Does Entity Framework support differing data types in the model?

Question

I am considering to use EF to access legacy SQL Server databases installed on our customers' systems.

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.

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).

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?

Answer 1

If you look on StackOverflow you will find many questions that ask how to map one datatype in the database to another:

Short to Bool

'Y'/'N' to true/false.

Convert from to string in database to boolean property Entity Framework 4.1

time(0) to DateTime

Nearly always the solution is to have 2 fields in the entity, with code to do the explicit conversion.

A small number of DataTypes can be mapped using the FluentAPI and you could use custom code first conventions:

datetime2 to DateTime

Your smallint to int will fall into this category but I am pretty sure float to decimal(10, 2) will not.

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.

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.

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