2

My question deals specifically with the mapping of numeric types between SQL Server 2008 and the .NET 3.5 Entity Framework.

I work for a corporation that has fairly stringent rules when it comes to designing databases. Said rules are enforced by a DBA who is neurotic about tables not being any larger in size than they need to be. For instance, he insists that where appropriate we keep numeric columns to tinyint and smallint.

The EF maps numeric types like such (SQL Server type on left, .NET type on right):
tinyint -> Byte
smallint -> Int16
int -> Int
bigint -> Int64

My concern arises due to the fact that I've done some reading recently and found that the .NET runtime is optimized to work with Int32. This question on Stackoverflow really gets into the guts of it, should anyone want to do a read-up on it.

My question is this: since the EF maps smallint to Int16, should I just get over the optimization issue and use the Int16 member variables in code, or is there some other solution that would allow me to work with the Int32 type in code and still be able to use smallint types in SQL Server? I can think of a solution or two on my own, but they all seem like overkill in the name of "optimization".

Community
  • 1
  • 1
Jagd
  • 7,169
  • 22
  • 74
  • 107
  • 1
    I'm not sure about this but I guess you could just change the type of the property in your model to in32 and as long as you dont send a value larger than int16 max value everything should be fine. – ryudice Feb 02 '11 at 23:45
  • @ryudice - Unfortunately, that doesn't work... or at least not with LINQ to Entities (it might work with LINQ to SQL??). I tried it and it got mad at me. – Jagd Feb 03 '11 at 16:13

1 Answers1

1

If this were LINQ to SQL instead of LINQ to Entities, you could change the type on your mapping, the only issue will be if you end up with a value in there that's bigger than the smallint column can handle (but then that's a whole 'nother issue anyway, I suppose).

But the LINQ to Entities validator doesn't allow that "mismatch."

bdukes
  • 152,002
  • 23
  • 148
  • 175
  • If I go into the ORM (the EDMX) and manually change the type of an Int16 to Int32, and then recompile, I get an error that basically says the relationship types aren't mapped properly. – Jagd Feb 03 '11 at 16:15
  • Ah, looks like that's just possible for LINQ to SQL. Sorry – bdukes Feb 03 '11 at 16:51