dynamically change the model - Database First

.net entity-framework entity-framework-6

Popular Answer

It is possible to do this in database-first. Create two sets of mapping and model files, then choose one set in the configuration file, to put it briefly.

file models

Three files are produced by EF when an EDMX is created:

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

You often don't need to be aware of the existence of these files since they are incorporated as resource files in the generated assembly. The resource paths in the connection string of the config file will guide EF to load the files from the assembly during runtime, often looking like...

metadata=res://*/...

Although it requires a few steps, it is feasible to include a different set of resource files in the assembly and adjust the connection string appropriately.

I'll abbreviate "mapping and model files" to "model files" for simplicity.

Two sets of model files are added.

Create the first set in step one.

The initial set of files can only be created by generating an EDMX. I made use of a rather basic 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]

I made an EDMX off of this table in a simple C# terminal application.

Step 2: Add incomplete files

In my situation, aPerson a class was developed:

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

The property in EFIsActive be mapped to abit It is not feasible to simply translate it to a database column becausebyte (or tinyint (as you well know) field. To accommodate the byte field, a second property must be added:

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

The key difficulty is figuring out how to transfer either of these two features, depending on the data type, to the same database field.

Step 3: Replicate and change the second set.

The first set's model files are currently integrated into the assembly. They should be accessible like ordinary files so that others may copy and edit them. The "Metadata Artifact Processing" option may be temporarily changed from its default value of "Embed in Output Assembly" to "To Output Directory Copy." The three files may now be found in the bin/Debug folder after building the app.

Set the "Metadata Artifact Processing" preference back to its default value, transfer the files into a second set, then move the files to the project's root directory. I eventually obtained these files, the "BitModel" ones being the originals:

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

To support the ByteModel files,Person.IsActiveByte I amended the following text (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" />
    

Now it's possible to remove the BitModel files.

Embed the second set as a resource in step four.

The ByteModel files must now be added to the project and have their "Build Action" property set to "Embedded Resource." Rebuild the undertaking.

Compared to how EF initially performs it, the files are embedded somewhat differently. The.exe file's disassembler inspection reveals that their resource names are<namespace>.<filename> in my situation:BitOrBye.ByteModel.csdl , etc.

Add a connection string in step five.

The project now has a connection string created by EF that looks 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 duplicated this connection string and left the old one uncommented. The resource paths in the copied connection string were updated as follows:

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

The group is now prepared to connect to a database where thePerson.IsActive is a fieldtinyint . The buildingPerson.IsActive is no longer a mapped property,Person.IsActiveByte is.

the context maps to the previous connection string when entered.bit column, therefore it is now possible to identify the available database type—"BitModel" or "ByteModel"—using the connection string.

Limitations

LINQ-to-Entities queries can only target mapped properties. For instance, a search like...

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

...is OK. But a query like... won't work while the "BitModel" is activated.

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

the dreaded The 'IsActiveByte' type member is not supported by LINQ to Entities. exception will be thrown.

Of course, you are already subject to this limitation. Your classes should usually include unmapped properties that combine the values of the bye and bit properties into a single property that can be used in the application code.

Using EntityFramework.DynamicFilters could be a way out. With the help of this little gem, you can create global filters in a context that may be activated and deactivated. As a result, two global filters may be established.

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

Depending on the kind of database you connect to—which can be determined from the connection string—you will add one to that list.

5
3/8/2018 1:00:21 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow