SQL Server 2008 Data Types and Entity Framework 4

Saturday, February 13 2010 - Data Access

Because I’ve had a lot of conversations about spatial data types lately, I thought I would create a SQL Server table that contained one of every type then bring it into an entity data model using the wizard to see what happens. This is in EF4.

Here are screenshots of the table and the entity showing their property types.

everydatatypetable

This way you can see what is and isn’t supported. The wizard excluded four fields it still doesn’t recognize at all.

  • geography
  • geometry
  • hierarchyid
  • sql_variant

If you focus on the entity, you can compare the property names to the property types to see how different data types are treated. E.g. the xml property is a String and the real property is a Single.

The fields that use Max become properties whose Max Length property =”Max”

Tagged as: ,

#1 Michael K. Campbell on 2.13.2010 at 3:08 PM

I hate to say it, but I can't take EF or LINQ to SQL seriously from MS until they add support in for Geography types.

I GET that doing so will be quite hard - but seriously... that data is expressed/stored somewhere within the bowels of SQL Server as binary data or something - which is then 'morphed' into a CLR object.

Why MS can't marshal that data back and forth between THEIR products is mind boggling.

And if it sounds like I have a chip on my shoulder, I DO. Because I designed an entire system with 25+ entites and wired it up for use via LINQ to SQL (actually via PLINQO - which makes L2S viable)... and had everything running JUST fine until I tried to query a table/entity with a Geography data type in it - and LINQ just plain BUSTED.

Options? Well... either remove the Geography type (lame) or don't use any ORM from MS. (Or remove the Geography object from your entities - but that's pretty lame too.)

And... ultimately, it just bugs me that the SQL Server team pimps Geography data types (which are FAN-FREAKING-TASTIC) and Microsoft is sooooo busy pushing EF all over us... yet they just pretend that there's something wrong with you if you want to use both.

Major Fail. (And here's hoping someone at MS reads your blog and see's me going all 'AngryPets' in here ;) )

Great post though Julie - and WAY to put all these different data types to the test.

#2 Vijay Santhanam on 2.15.2010 at 7:38 AM

The big issue for me is writing custom mappings for data types isn't supported.

There's a few times I'd like System.Uri mapped to a DB string (cos you know, it's a uri in my domain model) and you just can't do that in EF.

Unless there's a way I don't know about yet

#3 Julie on 2.15.2010 at 8:02 AM

Hi Vijay

See if there's anything in this post that might help you...the idea of using a view. thedatafarm.com/.../yes-you-can-rea

#4 Craig Stuntz on 2.18.2010 at 8:58 AM

This is really helpful; thanks. Seems to be missing FILESTREAM, though.