krillgar krillgar - 3 days ago 5
C# Question

Mapping a long data property to int in database

In all of the examples that I've seen use

int32
s as the IDs. That's not always practical in production environments. Several of our databases have identity IDs that are in the realm of
int64
s, 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

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:

Comments