EF Core to SqlServer types

[JasinCole]

What's the harm in marking my table column in EF core as an int if the sql server type is bigint which is actually a long in C#?

As you are probably aware by now, I am working on a database that I did not design. But I do not understand the need of the database designers to use bigint for the column when the maximum value can only be 99999. Makes no sense, but I can not change it. I do recognize that this column is similar to a couple hundred more tables with the same column where the number can be much larger than 99999.

I haven't tried to even see if EF Core will allow me to change it to int without throwing a fit. Does EF Core have any sort of automatic conversion from C# types to Sql Server types when types do not match?

 

[jmcilhinney]

It's a matter of practical vs theoretical. In theory, someone could enter a value into the database that will be too large for your EF model. If that never happens then, from a practical standpoint, there's no issue.

In my office, we use bigint for all PKs, regardless of how many records the table is likely to have. If there are never enough records to require that size, the actual space wasted is minimal anyway and if we always do the same thing then we can never mistakenly do the wrong thing and use a type that is too small. There's literally no point using a smaller data type in your EF model so you ought to just follow the database.

 

[cjard]

What's the harm

What's the purpose?

 

[JasinCole]

What's the purpose?

I was thinking about the memory overhead when I asked the question. I realize that memory isn't much of an issue any longer as far as space, but potentially other inefficiences.

 

[jmcilhinney]

You're looking at 4 bytes per value so you'd have to have a lot of instances of that type in memory for that to make any practical difference.

32-bit numbers are slightly more efficient to work with but, again, you'd have to be working with huge data sets for that difference to be significant.

 

[Skydiver]

Depends on how fast 64-bit index keys are compared to 32-bit index keys. In memory, it shouldn't matter as much given modern machines, but on disk it still matters: you can fit less 64-bit index keys within a given cluster/page vs 32-bit index keys. So it depends on how often the database needs to read in pages.

 

[jmcilhinney]

Depends on how fast 64-bit index keys are compared to 32-bit index keys. In memory, it shouldn't matter as much given modern machines, but on disk it still matters: you can fit less 64-bit index keys within a given cluster/page vs 32-bit index keys. So it depends on how often the database needs to read in pages.

I don't think that's an issue in this case though, because it seems that the database is using 64-bit values regardless, with the question being whether using 32-bit numbers in the corresponding EF model would be worthwhile or problematic.

 

[Skydiver]

Very true.

 

[cjard]

I was thinking about the memory overhead when I asked the question. I realize that memory isn't much of an issue any longer as far as space, but potentially other inefficiences.

You've already spent orders of magnitude longer worrying about it than you could ever save in CPU time switching to int