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?

Please, add some more info. When are you getting that mesasge? How does your model or code look like? — JotaBe, Aug 28 '15 at 08:50

Colin · Accepted Answer · 2015-08-28T15:30:58.833

14

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

edited Aug 28 '15 at 15:30

answered Aug 28 '15 at 09:56

Colin

22,328
17
103
197

2

Would it throw exception only in case of value exceeding int limits? – Artyom Aug 28 '15 at 10:03
1

The last one did the trick for us, just putting the column type attribute on the model property: `[Column(TypeName="bigint")]` – Savage Aug 28 '15 at 11:57

score 3 · Answer 2 · edited May 23 '17 at 12:00

3

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

edited May 23 '17 at 12:00

Community

1
1

answered Aug 28 '15 at 08:59

Matthew Sainsbury

1,470
3
18
42

So you're saying there is no way to map an int field directly to a bigint column? – Savage Aug 28 '15 at 09:21
I don't know if it would work, but you could try the modelBuilder.Entity().Property(x=>x.Id).HasColumnType("bigint"); method. however, the cast would still have to be done, I don't know if EF would really convert these values. – DevilSuichiro Aug 28 '15 at 09:34

score 2 · Answer 3 · answered Oct 16 '15 at 18:25

2

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?

answered Oct 16 '15 at 18:25

Thomas Johnson

400
2
10

Simple = better! _long_ will also work in place of Int64. – Ron Rebennack May 11 '18 at 17:32

Use int Id for a bigint column in EF6

3 Answers3

Linked