Tip 45 – How to swap EF metadata at runtime.


Background

By default the Entity Framework embeds its metadata inside your assembly as a resource.

It also puts a connection string in the App or Web Config that references those resources something like this:

<add name=”BloggingEntities” connectionString=”metadata=res://*/Blogging.csdl|res://*/Blogging.ssdl|res://*/Blogging.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=TipsDatabase;Integrated Security=True;MultipleActiveResultSets=True&quot;” providerName=”System.Data.EntityClient” />

This makes it incredibly easy to get started:

using (BloggingEntities ctx = new BloggingEntities())
{

NOTE: the use of res://* to tell the EF to look inside the assemblies resources for the various bits of metadata.

Problem

But embedding the metadata as a resource also means it is essentially immutable.

What if you need to change it at runtime?

There are lots of reasons why you might want to change the metadata at runtime, but probably the most likely is that in production you have to deal with a DBA who has a different set of database design ideas.

Typically this means you have to make some database changes which require some storage model (SSDL) and mapping (MSL) changes.

Doing so is perfectly okay, indeed it is one of the big benefits of using the Entity Framework, so long as the conceptual model or CSDL — what the developer programs against — remains unchanged.

Solution

So how do you plug in a different MSL and SSDL at runtime?

Here are the steps involved:

Step 1:
Get hold of the ‘metadata artifacts’:

  1. Right click on the designer canvas and click Properties:

    RightClick
  2. Set ‘Metadata Artifact Processing’ to ‘Copy to Output Directory’:

    CanvasProperties 
  3. Build and look in the bin\debug (or bin\release) directory:

    bin_debug

Step 2:
Now you have the CSDL/MSL/SSDL as files, it is easy to replace one or more (generally the SSDL and MSL) with a version that matches your environment. So you can have one set for Development and another for Production.

Then all you need to do is modify your connection string to point to the right set of files, something like this:

var connStr =
@”metadata=.\Blogging.csdl|.\Production.ssdl|.\Production.msl;
    provider=System.Data.SqlClient;
    provider connection string=””
           Data Source=.\SQLEXPRESS;
           Initial Catalog=TipsDatabase;
           Integrated Security=True;
           MultipleActiveResultSets=True
   “””;

using (BloggingEntities ctx = new BloggingEntities(connStr))
{

Notice that now the connection string uses .\ rather than res://* which tells the EF to look in the same directory as the application for the metadata.

That’s all there is to it.

NOTE: EF connection strings can be a little tricky, what with nested provider connection strings, metadata and providers, thankfully though there is an EntityConnectionStringBuilder class to help out.

Comments (3)

  1. Anon says:

    Hi Alex,

    A related issue I’m having is changing the database at runtime.

    I think the root cause is the in the SSDL entitysets are being created with the Schema attribute

    ie

    <EntitySet Name="task" EntityType="hardModel.Store.task" store:Type="Tables" Schema="harder" />

    The result is that the database name is appearing the the final slq being executed, the database name being the design time database.

    Is there a workaround to prevent this from happening? I do not mind being limits to just one database.

  2. Alex D James says:

    @Anon (?)

    the Schema is not the database name, it is the schema, usually it is something like ‘dbo’.

    Both your production and development databases can have the same schema.

    Alex

  3. Ann says:

    Clarifying my question..

    The production and test are on the same database system one is called  hardtest and the other hardprod. i.e  databases or schemas in the same database system/machine

    My question was in regard to designing using hardtest but then at runtime changing to use hardprod. Assuming the both hardtest and hardprod are the same structure I should be able to do this without issue.

    The issue issue I’m having is the the SQL EF is producing contains the database/schema name of the design time database. ie the sql looks like "Select * from  hardtest.task" instead of  "Select * from hardprod.task"

    The reason I think this is happening is that EF is storing the schema name in the SSDL EntitySet as stated above.

    If I manual edit the SSDL by deleting the Schema attribute in all the EntitySets them I get generic SQL that works ie SQL without any Schema defined "Select * from task"

    The problem with doing this is that when I try to update the EF model again it adds back all the EF entities (because I remove the Schema attribute from the SSDL it thinks everything is new and adds it to the model for a 2nd time)

    The solution you proposed in your blog post is about changing the SSDL and other files if the database design is different. But what is they are the same and I just want the generic SQL to not include the schemas.

    One thing is note is that changing the connection string does make EF hit the correct database/schema.. but the SQL being executed at this time contains the design time database name or schema.

Skip to main content