Use int Id for a bigint column in EF6

Question

We're using Entity Framework 6 to build a data layer on top of a legacy Sql Server database that has Id columns of type bigint. We want to use int for our Id column types rather than long, and we're confident that the tables will never grow beyond the size limit of an int.

However, we're getting the following error:

The specified cast from a materialized 'System.Int64' type to the 'System.Int32' type is not valid.

How can we achieve what we want without modifying the db column type?

Solution 1

You should be able to specify the DataType of the column in your OnModelCreating method for each model affected):

modelBuilder.Entity<Department>()   
    .Property(p => p.Id)   
    .HasColumnType("bigint");

If every Id in every model is mapped to a bigint, then you could use a custom convention:

modelBuilder.Properties<int>()
   .Where(p => p.Name == "Id")
   .Configure(c => c.HasColumnType("bigint"));

Another technique would be to use an abstract base class for all the Models that have a bigint Id (and this example shows it using data annotation instead of fluent API:

public abstract class BaseModel
{
   [Column(TypeName="bigint")]
   public int Id { get; set; }
}

References:

Configuring the data type of a column

Custom conventions

Solution 2

What about something like this:

class DbEntity
{
    [Key]
    private Int64 Id { get; set; }

    [NotMapped]
    public int SmallerId { 
        get { return Convert.ToInt32(Id); }
    }
}

This strategy can be used for all sorts of mappings, like Yes/No to true/false, see this question

Solution 3

Why not use Int64 on your entity instead of trying to map to a data type that can't handle the maximum value in the database?