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?

Flagstone

Have you tried a test: point the same model at a couple of test databases with such differences? Remember the data might make a difference (eg. decimal values with no fractional part may work while those with fractional parts fail). – Virescent

@Richard: Not yet, that's what I plan to do if no one can provide an authoritative answer. If I test it and it works, I still do not know if it is "supposed to work that way" or if it "works coincidentally because of some implementation details of EF". – Flagstone

It is true that "it seems to work" result won't be definite (but you won't get that here either). However an "it doesn't work" result would be. Thus you would be able to ask the real question: how can I adapt/use EF in these circumstances (given it is unlikely all conceivable mappings would be automatically supported you need that question whatever the "official" position is). – Virescent

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

Disillusionize

Answer 2

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

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.

Harlen

Answer 3

This is a very late answer to the question, but we have just had exactly the same issue as the OP - a client with some fields defined as int, whilst another client had those fields defined as bigint. Apologies if this solution exists somewhere else - I looked but didn't find it.

This may not be the prettiest solution but it works well where the problem isn't widespread (which would justify separate database contexts). In our case it's a single table that has manifested the problem, and changing the types in the database is a less preferred option (risk, deployment constraints, client dependencies).

First create client specific entities for the same table:

[Table("DbTableName")]
public class EntityForClientA
{
    public long Id { get; set; }
    public long GroupId { get; set; }
    //Alongside fields that don't differ
}

[Table("DbTableName")]
public class EntityForClientB
{
    public int Id { get; set; }
    public int GroupId { get; set; }
    //Alongside fields that don't differ
}

Then in the datasource:

    public DbSet<EntityForClientA> EntitiesA { get; set; }
    public DbSet<EntityForClientB> EntitiesB { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        if (isClientA) modelBuilder.Ignore<EntityForClientB>();
        else modelBuilder.Ignore<EntityForClientA>();
        base.OnModelCreating(modelBuilder);
    }

From there we create business models which access the correct entity according to the client being served.

Glycerol