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 ?
5 Answers
Answers 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="data source=.\sql2016;initial catalog=DbBit;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" 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.
Answers 2
I just wanna give you some options to consider :
OPTION 1 :
Adjust the schema in the database's side, if possible, because it will only give you a headache in the long run.
OPTION 2 :
Or switch to code-first to make room for some tweaks. Furthermore, if you rely on EDMX, this feature has been dropped in EF Core, anyway.
OPTION 3 :
Give that table a special treatment, like, exclude it from your main Context, and make a new context just to take care of it.
Most likely, other than that, as far as I know, it will never work.
Sorry I can only give you some ideas.
Answers 3
In my opinion this is not a db-first attempt. It's a sort of "code-first-wtih-generated-classes-from-db" attempt.
- Generate master-schema
- Generate code form master
- Connect to different dbs with generated code
What you did wrong is using the wrong master-schema. You tried to access with the wrong datatypes. Write a schama (or code directly) with compatible datatypes. Easiest way is to use string-only-properties and map them later.
Example-Class
[Table("dbo.G")] public class G { public string Id { get; set; } [Column("CI_BlockWithID")] public string CiBlockWithIdStr { get; set; } public int CiBlockWithId { get { return Convert.ToInt32(this.CiBlockWithIdStr); } set { this.CiBlockWithIdStr = value.ToString(); } } } The example shows a code-first snippet to explain the mechanics. You need code with compatible datatypes - string in this example.
The question is how do you get this code?
a. Code-First (which you don't want to do)
b. Force the code-generator to chosse other datatypes (generate from master schema).
Please be aware that strong types are better than using string. This should only show you the mechanic. If you're reading a TinyInt and a Byte, you could try to use Int32 as master-type and so on (Depending on db-provider). In MySQL we use very much strings to read DateTimes and Enums.
Answers 4
Could you add a view to all the databases that does the conversion from bit to byte within its defining sql? Then include the view instead of the table. You may also have to use stored procedures to do updates and inserts. Effectively you'd be using the view to make the databases appear identical to a single
DbContext.Could you have multiple
DbContexts that inherit from a base context, then dynamically change the context when required, instead of changing connection strings? I use a Unit of Work / Repository pattern with dependency injection. My Unit of Work is dependent on aDbContext:public class UnitOfWork { private readonly DbContext context; public UnitOfWork(DbContext context) { this.context = context; } }
And I define what should be injected into the unit of work during application startup. If you used this pattern you would inject the correct DbContext at the point that you are currently switching connection strings.
Answers 5
You can create a partial class for your entity and add a property which can handle any situation:
public partial class X { public int TrueY { //add verification logic here } } 
0 comments:
Post a Comment