What strategy to follow while matching sql server datatypes in C# Code First approach while using Entity Framework

Question

While matching SQL Server datatypes with C# Code First Approach in Entity Framework, what are specific points that we should keep in mind so that our C# datatype should match exactly our SQL Server datatype?

Specifically, while we have to match SQL Server datatype to C# datatype, let's say for particular datatype hierarchyid, which exists in SQL Server but not in C#, how can we make it possible?

Answer 1

There's some datatype that's not supported by EF.

geography
geometry
hierarchyid
sql_variant

http://thedatafarm.com/data-access/sql-server-2008-data-types-and-entity-framework-4/

I never use any of that type, but when using dapper I also come to this similar problem(processing image datatype), the easiest way to deal with this limitation is just to go back to old OLE DB.

Other than that you just use this as a reference,

Answer 2

EF should handle the mapping of most datatypes. If you're using something that isn't handled you should be able to customize the mapping. Easiest would be to exclude mapping for unknown types, and save your object in a flattened way either in or related to the entity. Downside is that querying could be more work and you need to provide something to convert your object when reading and writing, but you're doing that maybe already when converting to viewmodels.

Answer 3

EF isolates your code from concrete database. Each database has it's own features but EF's API is not so powerfull to express them all. Actually, from the client's side EF is a simple object storage.

So, if you want to manage such database features like specific datatypes, security checks, triggers, views... EF can't help with it. I haven't seen anything that can help you. You need to introduce a dependency to the sql server in your system.