Mapping a long data property to int in database

Question

In all of the examples that I've seen use int32s as the IDs. That's not always practical in production environments. Several of our databases have identity IDs that are in the realm of int64s, so our practice is to always use long for our ID properties. However, SQL Server has higher max values for the int column type.

I'm doing our first proof of concept with Entity Framework version 6. With the long ID, it's unable to map the object to the database.

I'm using Fluent API for all of my mapping. Right now, it looks like this for the ID:

Property(s => s.ID).HasColumnName("spcID");

If I add a .HasColumnType("int") to the end of the above, it gives me the following error:

Schema specified is not valid. Errors: (7,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.Int64[Nullable=False,DefaultValue=]' of member 'ID' in type 'EFConnection.Space' is not compatible with 'SqlServer.int[Nullable=False,DefaultValue=,StoreGeneratedPattern=Identity]' of member 'spcID' in type 'CodeFirstDatabaseSchema.Space'.

How do you map the datatype for these to a long variable in .NET?

Edit

Right now, I have a simple Integration Test set up to make sure that I can connect:

[TestMethod]
public void TestMethod1() {
    using (var context = new Context()) {
        Assert.IsTrue(context.Spaces.Any());
        Assert.IsTrue(context.Spaces.First().IsActive);
    }
}

Without the .HasColumnType("int"), the first Assert passes, but I get an InvalidOperationException on the second:

The 'ID' property on 'Space' could not be set to a 'System.Int32' value. You must set this property to a non-null value of type 'System.Int64'.

Answer 1

C# and SQL data types are compatible:

If your table has a column of bigint use public long Id { get; set; } if your column is int use public int Id { get; set; }

SQL

bigint -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 int -2,147,483,648 to 2,147,483,647

C#

long -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 int -2,147,483,648 to 2,147,483,647

References:

• int, bigint, smallint, and tinyint (Transact-SQL)
• int (C# Reference)
• long (C# Reference)