Entity framework with dynamic schema changes, using Database-First approach.

By | November 14, 2013

Scenario: I needed to use Entity Framework. However, it was a little bit complicated for few reasons:

  • Schema names were dynamic <–> that means we need to change our model on runtime
  • Connection string generation had to be dynamic, since we didn’t know database name, username / password.
  • It needed to be a seperate DLL that EVERYBODY could use. As you might know, EF likes to use “currently active App.config”, so storing it in there, is a big no no, unless you could somehow use FILE-MERGERER. Anyhow, I am not too big fan of app.config.

I took a quick look into Entity framework CODE-FIRST approach, and it can change easily mappings to tables. There is small problem though; EF will be useful only to me, if it can deal with Stored Procedures easily. Code-First DOES NOT support SP “directly”. If you reverse engineer database, it doesn’t know anything about stored procedures, and it can’t do much. However, you can always obtain DbConnection and execute SQL directly. It’s kind of pointless.

Database administrators make you use STORED PROCEDURES/VIEWS, so they can control internal database. It’s kind of what programmers do, e escape REPOSITORY behind SERVICE, so that it can change easily. If Code-First does not generate stored procedures, then there is not much point of using it. The very idea of CODE-FIRST is after all, CODE-FIRST..

What I needed: database first approach(generates stored procedures automatically). EntityConnection will be build on runtime through CSDL/MSL/SSDL files, dynamically replacing schemas, as needed. Here is a little caveat; it can’t access anything that is auto-generated. It’s not that complicated problem though, since you can always change how ENTITY FRAMEWORK T4 scripts generate the underlying auto-generated files.

First of all, we need to make sure that the generated DbContext will be internal, so nobody outside of DLL can’t use it. So, without thinking too hard, find out your *.Context.tt, line: ‘Accessibility.ForType(container)’ and replace it with “internal” & generate DbContext again.

You also want to expose constructor for generated DbContext, so you can intialize it from EntityConnection. You can either change the T4 script, or add partial class, as such:

internal partial class LINKEntities
	{
		/// <summary>
		/// Initializes a new instance of the <see cref="LINKAQDEntities"/> class.
		/// </summary>
		/// <param name="entityConnection">The entity connection.</param>
		public LINKEntities(EntityConnection entityConnection) : 
                         base(entityConnection, false)
		{

		}
	}

Now, we need to create our dynamic DbContext class, which will wrap Generated DbContext. The main reason I do is because it’s never too good idea to expose generated DbContext as public for others. For obvious reasons; no binary compatibility & naming sucks. As soon you regenerate context from EDMX, everything breaks, including anything that references it outside. Usually you can use some kind of DTO solution for this.

Now, add DatabaseUtils to your code:

	internal static class DatabaseUtils
	{
		/// <summary>
		/// Builds the connection string for Entity framework.
		/// </summary>
		/// <returns></returns>
		public static EntityConnection BuildConnection(BuildConnectionParams buildConnectionParams)
		{
			var sqlBuilder = new SqlConnectionStringBuilder
				{
					DataSource = buildConnectionParams.ServerName,
					InitialCatalog = buildConnectionParams.DatabaseName,
					IntegratedSecurity = true
				};

			var providerString = sqlBuilder.ToString();
			var entityBuilder = new EntityConnectionStringBuilder
			{
				Provider = buildConnectionParams.ProviderName,
				ProviderConnectionString = providerString,
				Metadata = string.Format(@"res://*/{0}.csdl|
							res://*/{0}.ssdl|
							res://*/{0}.msl", buildConnectionParams.ModelName)
			};

			return CreateConnection(buildConnectionParams.SchemaName, entityBuilder, buildConnectionParams.ModelName);
		}

		/// <summary>
		/// Creates the EntityConnection, based on new schema & existing connectionString
		/// </summary>
		/// <param name="schemaName">Name of the schema.</param>
		/// <param name="connectionBuilder"></param>
		/// <param name="modelName">Name of the model.</param>
		/// <returns></returns>
		private static EntityConnection CreateConnection(string schemaName, EntityConnectionStringBuilder connectionBuilder, string modelName)
		{
			Func<string, Stream> generateStream =
				extension => Assembly.GetExecutingAssembly().GetManifestResourceStream(string.Concat(modelName, extension));

			Action<IEnumerable<Stream>> disposeCollection = streams =>
			{
				if (streams == null)
					return;

				foreach (var stream in streams.Where(stream => stream != null))
					stream.Dispose();
			};

			var conceptualReader = generateStream(".csdl");
			var mappingReader = generateStream(".msl");
			var storageReader = generateStream(".ssdl");

			if (conceptualReader == null || mappingReader == null || storageReader == null)
			{
				disposeCollection(new[] { conceptualReader, mappingReader, storageReader });
				return null;
			}

			var storageXml = XElement.Load(storageReader);

			foreach (var entitySet in storageXml.Descendants())
			{
				var schemaAttribute = entitySet.Attributes("Schema").FirstOrDefault();
				if (schemaAttribute != null)
					schemaAttribute.SetValue(schemaName);
			}

			storageXml.CreateReader();

			var workspace = new MetadataWorkspace();

			var storageCollection = new StoreItemCollection(new[] { storageXml.CreateReader() });
			var conceptualCollection = new EdmItemCollection(new[] { XmlReader.Create(conceptualReader) });
			var mappingCollection = new StorageMappingItemCollection(conceptualCollection, 
																	storageCollection,
																	new[] { XmlReader.Create(mappingReader) });

			workspace.RegisterItemCollection(conceptualCollection);
			workspace.RegisterItemCollection(storageCollection);
			workspace.RegisterItemCollection(mappingCollection);

			var connection = DbProviderFactories.GetFactory(connectionBuilder.Provider).CreateConnection();
			if (connection == null)
			{
				disposeCollection(new[] { conceptualReader, mappingReader, storageReader });
				return null;
			}

			connection.ConnectionString = connectionBuilder.ProviderConnectionString;
			return new EntityConnection(workspace, connection);
		}
	}

And BuildConnectionParams(I actually like to pull everything into structure and make user initialize each Property field seperately. This way it’s a lot harder to call method incorrectly)

	/// <summary>
	/// Those params are used for SQLCE.
	/// </summary>
	internal struct BuildConnectionParams
	{
		public string ProviderName
		{
			get; 
			set;
		}

		public string ServerName
		{
			get; 
			set;
		}

		public string DatabaseName
		{
			get; 
			set;
		}

		public string ModelName
		{
			get;
			set;
		}

		public string SchemaName
		{
			get;
			set;
		}
	}

Not gonna even lie, some of the code is not written by me, I’ve done some heavy refactoring though. Plus I’ve changed it a little bit, because it didn’t work earlier.
You can find the discussion here: changing-schema-name-on-runtime-entity-framework

Final step is to create new DynamicContextWrapper, and initialize underlying DbContext & EXPOSE what is needed. It’s quite simple:

public class DynamicContextWrapper : IDisposable
	{
		private readonly string AQDModel = "AQDModel"; // EDMX MODEL!

		private LINKEntities _entities;

		/// <summary>
		/// Initializes a new instance of the <see cref="DynamicAQDContext"/> class.
		/// </summary>
		public DynamicCOntextWrapper()
		{
			var entityConnection = DatabaseUtils.BuildConnection(new BuildConnectionParams
			{
				ProviderName = "System.Data.SqlClient",
				ServerName = "localhost\\YOURSERVER",
				DatabaseName = "YOUR_DATABASE",
				ModelName = AQDModel,
				SchemaName = "ASR_001"
			});

			if(entityConnection == null)
				throw new Exception("Can't create EntityConnection");

			_entities = new LINKEntities(entityConnection);
		}

		/// <summary>
		/// TummyTEST!
		/// </summary>
		public void Test()
		{
			var ha = _entities.LNK_GET_LIST_DATA().ToList();
		}

		/// <summary>
		/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
		/// </summary>
		public void Dispose()
		{
			if (_entities == null) 
				return;

			_entities.Dispose();
			_entities = null;
		}
	}

And that’s how you do it. Note that now can everything be changed on runtime. Schema/Username/ and so on. The schema name is based on per EDMX. If you have different schemas on a single EDMX, it’s only adding more complexity. It’s not hard to actually change it. You need to modify CreateConnection() => storageXml.Descendants() and replace concrete schemas. To keep things simple, I suggest to use seperate schema per EDMX.

2 thoughts on “Entity framework with dynamic schema changes, using Database-First approach.

  1. Suresh

    Hello, I could see the above pattern read (.csdl, ssdl, msl) files only for already available model. We can able create new table at runtime and refresh the whole model using above pattern? Please kindly help me.

    Reply
  2. ertichriseelmaa Post author

    .csdl, ssdl, msl files are generated by Entity framework scripts. You can check out how these files are generated from the database by viewing EF T4 scripts. It’s possible to generate models & DLLS on runtime from a table. It’s tricky, but can be done.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *