I am considering to use EF to access legacy SQL Server databases installed on our customers' systems.
我正在考虑使用EF访问客户系统上安装的旧SQL Server数据库。
The problem is that the databases schemas are not 100% consistent: While they all have the same (relevant) tables and fields, some numeric fields might have different data types.
问题是数据库模式不是100%一致的:虽然它们都具有相同(相关)的表和字段,但某些数字字段可能具有不同的数据类型。
As far as I can see, the types are "compatible" from an application point of view: E.g. a field containing small numbers might be a smallint on customer A's database, but an int on customer B's database, or a field containing a price might be a decimal(10,2) on A's database but a float on B's database (yes, that means that B might suffer from floating-point problems -- it's a legacy database after all).
据我所知,从应用的角度来看,这些类型是“兼容的”:例如包含小数字的字段可能是客户A数据库中的一个小字段,但客户B数据库中的int或包含价格的字段可能是A数据库上的小数(10,2),但是B的数据库上有一个浮点数(是的,那个意味着B可能会遇到浮点问题 - 毕竟它是一个遗留数据库。
Since we are not the only ones accessing the databases, changing (and, thus, unifying) the schemas is not an option. Is Entity Framework able to cope with that (i.e., will it gracefully accept a double in the SQL Server table if the model definition claimed it's a decimal(10,2)) or will it crash horribly?
由于我们不是唯一访问数据库的人,因此更改(并因此统一)模式不是一种选择。实体框架是否能够应对(即,如果模型定义声称它是小数(10,2),它会优雅地接受SQL Server表中的双精度)还是会崩溃?
2 个解决方案
#1
3
If you look on * you will find many questions that ask how to map one datatype in the database to another:
如果您查看*,您会发现许多问题,询问如何将数据库中的一种数据类型映射到另一种数据类型:
Bool的简称
'Y'/'N'为真/假。
Convert from to string in database to boolean property Entity Framework 4.1
从数据库中的字符串转换为布尔属性实体框架4.1
时间(0)到DateTime
Nearly always the solution is to have 2 fields in the entity, with code to do the explicit conversion.
几乎总是解决方案是在实体中有2个字段,使用代码进行显式转换。
A small number of DataTypes can be mapped using the FluentAPI and you could use custom code first conventions:
可以使用FluentAPI映射少量DataType,您可以使用自定义代码优先约定:
datetime2到DateTime
Your smallint to int will fall into this category but I am pretty sure float to decimal(10, 2) will not.
你的smallint到int将属于这个类别,但我很确定float到十进制(10,2)不会。
Your problem is compounded by having multiple databases with different datatypes so I don't think EF by itself is going to work very well.
拥有多个具有不同数据类型的数据库使您的问题更加复杂,因此我认为EF本身并不能很好地工作。
I can think of two things you could try:
我可以想到你可以尝试的两件事:
Create Views in each of the databases that map datatypes consistently, then reverse engineer the Entity Framework from the Views. You will also probably have to map the CUD to stored procedures then modify the Create and Update sql for each database to convert the dataypes.
在每个映射数据类型的数据库中创建视图,然后从视图中反向设计实体框架。您可能还必须将CUD映射到存储过程,然后修改每个数据库的Create和Update sql以转换dataypes。
OR
Look at Dapper where you have greater control over the sql and do conversions there. There are links to hybrid implementations of the repository pattern at this answer EF + Dapper Hybrid Implementation
看看Dapper,你可以更好地控制sql并在那里进行转换。在此答案EF + Dapper Hybrid Implementation中,链接到存储库模式的混合实现
#2
0
Is Entity Framework able to cope with that (i.e., will it gracefully accept a double in the SQL Server table if the model definition claimed it's a decimal(10,2)) or will it crash horribly
实体框架是否能够应对这种情况(即,如果模型定义声称它是小数(10,2),它是否会优雅地接受SQL Server表中的双精度)或者它是否会崩溃
I think that will not cause an issue as entity framework make all the conversions needed before executing the query and as there is no implicit conversion from floating-point to decimal you should detect it at compile time and make the explicit conversion.
我认为这不会导致问题,因为实体框架会在执行查询之前进行所需的所有转换,并且由于没有从浮点到十进制的隐式转换,您应该在编译时检测它并进行显式转换。
#1
3
If you look on * you will find many questions that ask how to map one datatype in the database to another:
如果您查看*,您会发现许多问题,询问如何将数据库中的一种数据类型映射到另一种数据类型:
Bool的简称
'Y'/'N'为真/假。
Convert from to string in database to boolean property Entity Framework 4.1
从数据库中的字符串转换为布尔属性实体框架4.1
时间(0)到DateTime
Nearly always the solution is to have 2 fields in the entity, with code to do the explicit conversion.
几乎总是解决方案是在实体中有2个字段,使用代码进行显式转换。
A small number of DataTypes can be mapped using the FluentAPI and you could use custom code first conventions:
可以使用FluentAPI映射少量DataType,您可以使用自定义代码优先约定:
datetime2到DateTime
Your smallint to int will fall into this category but I am pretty sure float to decimal(10, 2) will not.
你的smallint到int将属于这个类别,但我很确定float到十进制(10,2)不会。
Your problem is compounded by having multiple databases with different datatypes so I don't think EF by itself is going to work very well.
拥有多个具有不同数据类型的数据库使您的问题更加复杂,因此我认为EF本身并不能很好地工作。
I can think of two things you could try:
我可以想到你可以尝试的两件事:
Create Views in each of the databases that map datatypes consistently, then reverse engineer the Entity Framework from the Views. You will also probably have to map the CUD to stored procedures then modify the Create and Update sql for each database to convert the dataypes.
在每个映射数据类型的数据库中创建视图,然后从视图中反向设计实体框架。您可能还必须将CUD映射到存储过程,然后修改每个数据库的Create和Update sql以转换dataypes。
OR
Look at Dapper where you have greater control over the sql and do conversions there. There are links to hybrid implementations of the repository pattern at this answer EF + Dapper Hybrid Implementation
看看Dapper,你可以更好地控制sql并在那里进行转换。在此答案EF + Dapper Hybrid Implementation中,链接到存储库模式的混合实现
#2
0
Is Entity Framework able to cope with that (i.e., will it gracefully accept a double in the SQL Server table if the model definition claimed it's a decimal(10,2)) or will it crash horribly
实体框架是否能够应对这种情况(即,如果模型定义声称它是小数(10,2),它是否会优雅地接受SQL Server表中的双精度)或者它是否会崩溃
I think that will not cause an issue as entity framework make all the conversions needed before executing the query and as there is no implicit conversion from floating-point to decimal you should detect it at compile time and make the explicit conversion.
我认为这不会导致问题,因为实体框架会在执行查询之前进行所需的所有转换,并且由于没有从浮点到十进制的隐式转换,您应该在编译时检测它并进行显式转换。