dynamically change the model - Database First

Question

My DB is MS-SQL with EF 6.0 using Data-First approach. I'm syncing with hundreds of databases (tables scheme is almost the same on all of them), dynamically changing the connection string when required.

My issue is that some databases have a slightly different scheme than others. on all of them I have a table X which has a column Y and Y can be either a bit, or a byte.

EF generated a model class, based on the database which has column Y defined as byte. So when querying, it obviously throws an exception.

The 'Y' property on table 'X' could not be set to a System.Boolean value. you must set the value to System.Byte.

Is there a way, dynamically change the model, in Database-First approach, to resolve this issue ? or perhaps cast returned value, to a byte, before its assigned to the model ? to prevent the exception ?

Answer 1

There is a way to get this done in database-first. In short: create two sets of mapping and model files and choose one set in the config file.

Model files

When an EDMX is created, EF creates three files:

• The store model (*.ssdl).
• The class (or conceptual) model (*.csdl).
• The mapping between these two models (*.msl).

These file are embedded as resource files in the compiled assembly and normally you don't need to be aware of their existence. At runtime, EF will load the files from the assembly, directed by the resource paths in the config file's connection string, usually looking like...

metadata=res://*/...

It is possible to embed another set of resource files into the assembly and modify the connection string accordingly, but it takes a couple of steps to achieve this.

For brevity, I will refer to "mapping and model files" as "model files".

Adding two sets of model files

Step 1 - Create the first set

Creating the first set of files is nothing but creating an EDMX. I used a very simple database table:

CREATE TABLE [dbo].[Person](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [IsActive] [bit] NOT NULL,
    CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([Id] ASC))
ALTER TABLE [dbo].[Person] ADD  CONSTRAINT [DF_Person_IsActive]  DEFAULT ((1)) FOR [IsActive]

In a simple C# console application I created an EDMX off this table.

Step 2 - Add partial files

In my case only a Person class was created:

public partial class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

In EF, the property IsActive must be mapped to a bit database field, so it's not possible to simply map it to to byte (or tinyint) field, as you already found out. we have to add a second property to support the byte field:

partial class Person
{
    public byte IsActiveByte { get; set; }
}

The main challenge is how to get either of these two properties mapped to the one field in the database depending on its data type.

Step 3 - Copy and modify the second set

Now the model files of the first set are embedded in the assembly. We want to make them available as regular files in order to copy and modify them. This can be done by temporarily changing the setting "Metadata Artifact Processing" from its default (Embed in Output Assembly) to Copy to Output Directory. Now build the project and find the three files in the bin/Debug folder.

Restore the "Metadata Artifact Processing" setting to its default, move the files to the project's root directory and copy them into a second set. I ended up having these files, of which the "BitModel" ones are the originals:

BitModel.csdl
BitModel.msl
BitModel.ssdl
ByteModel.csdl
ByteModel.msl
ByteModel.ssdl

For the ByteModel files to support the Person.IsActiveByte property I made these changes (original line/edited line):

• csdl:

  <Property Name="IsActive" Type="Boolean" Nullable="false" />
  <Property Name="IsActiveByte" Type="Byte" Nullable="false" />
  

• ssdl:

  <Property Name="IsActive" Type="bit" Nullable="false" />
  <Property Name="IsActive" Type="tinyint" Nullable="false" />
  

• msl:

  <ScalarProperty Name="IsActive" ColumnName="IsActive" />
  <ScalarProperty Name="IsActiveByte" ColumnName="IsActive" />
  

The BitModel files can now be deleted.

Step 4 - Embed the second set as resource

The next step is to add the ByteModel files to the project and in their properties set "Build Action" to "Embedded Resource". Rebuild the project.

The files are embedded slightly differently than the way EF does it originally. Inspecting the .exe file in a disassembler shows that their resource names are <namespace>.<filename>, in my case: BitOrBye.ByteModel.csdl, etc.

Step 5 - Add a connection string

EF added a connection string to the project looking like...

<add name="DbBitContext" 
    connectionString="metadata=res://*/BitModel.csdl
                              |res://*/BitModel.ssdl
                              |res://*/BitModel.msl;
    provider=System.Data.SqlClient;
    provider connection string=&quot;data source=.\sql2016;initial catalog=DbBit;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;"
    providerName="System.Data.EntityClient" />

I copied this connection string and commented out the original one. In the copied connection string I modified the resource paths:

<add name="DbBitContext" 
    connectionString="metadata=res://*/BitOrByte.ByteModel.csdl
                              |res://*/BitOrByte.ByteModel.ssdl
                              |res://*/BitOrByte.ByteModel.msl;
    ... />

Now the assembly is ready to connect to a database in which the Person.IsActive field is a tinyint. The property Person.IsActive is not a mapped property any more, Person.IsActiveByte is.

Enter the previous connection string and the context maps to the bit field, so the connection string can now be used to determine which type of database is supported, "BitModel" or "ByteModel".

Limitations

In LINQ-to-Entities queries only mapped properties can be addressed. For example, a query like...

context.People.Where(p => p.Id > 10).Select(p => p.Name).ToList()

...is OK. But when the "BitModel" is active, a query like...

context.People.Where(p => p.IsActiveByte == 1).Select(p => p.Name).ToList()

...will throw the infamous The specified type member 'IsActiveByte' is not supported in LINQ to Entities exception.

Of course you already have this restriction. You probably want to add unmapped properties to your classes that channel the value of both the bye and bit properties to one property you'll use in the application code.

A possible way out is to use EntityFramework.DynamicFilters . This little gem enables you to define global filters in a context that can be turned on and off. Thus, two global filter can be defined...

modelBuilder.Filter("IsActiveBit", (Person p) => p.IsActive, true);
modelBuilder.Filter("IsActiveByte", (Person p) => p.IsActiveByte, 1);

...of which you will add one, depending on the type of database you connect to, which can be inferred from the connection string.