 |
|||||||||
|
|||||||||
|
|
In case you've been stuck under a rock for the past couple of months, the ADO.NET Entity Framework is essentially the conglomeration of bits and pieces of disbanded groups within Microsoft, including a lot of the work that was going into the database access model for WinFS, a now-defunct technology that may never see the light of day. What it basically does is build on top of the work done for C# vNext (3.5??) which includes LINQ, Language Integrated Query. LINQ lets you write C# code that looks like this:
var myDataQuery = from Customer in theDatabase.Customers where Customer.FirstName == "Kevin";
How cool is that?!? The Entity Framework takes that one step further by adding a layer of abstraction on top of the relational database called an Entity Model. This Entity Model allows you to create a model that much more elegantly fits into the Object-Oriented Design world.. you can create entities that consist of columns retrieved from multiple tables, you can create entities that inherit from other entities, etc. So far I've been really impressed, but I have one major nitpick.
At the moment, You cannot use a foreign key column as a discriminator column for the purposes of determining entity inheritance. That may sound like a lot, so let's take a look at an example.
Say you have a database with two tables: VideoGames, and VideoGameTypes. It should be obvious that the next thing I'm going to say is in the VideoGames table there is a GameType column that has a foreign key relationship and constraint connecting it to the VideoGameTypes table, probably on the ID column. Also, there's a nullable column called WiimoteOptimized that indicates if a Wii game is optimized for Wii controllers or if its just an ordinary controller game. Because I know that entity inheritance works on discriminator columns, one would think that I should be able to define an entity type called VideoGames which includes all rows in the VideoGames table, and I should also be able to define an entity type called WiiGames, with a condition contained in the underlying mapping file that indicates that in order to qualify for membership in the WiiGames entity set, the entity must have a value of 7 in the column GameType.
This then enables a query like this:
var wiiGames = from GenericGame in theDatabase.VideoGames orderby
GenericGame.Title where GenericGame is WiiGame;
If you don't think that the query above is unbelievably cool, then...well, there's probably no hope for you :)
Here's the rub: This flat out won't work. What will happen is you'll compile , everything will be fantastic, and then you'll run it and get a runtime error that mentions something cryptic about having conflicting conditions on the GameType column. What's going on is that there's a NOT NULL condition placed on the GameType column by the foreign key constraint, and there's a "Must be 7" condition placed on the same column by the WiiGame entity set mapping definition. The problem is that these things conflict and you cannot place an entity inheritance condition on a column that is also a foreign key.
That bites. Hard.
The official solution, according to Atul Adya (MSFT) over at the MSDN forum for ADO.NET vNext, is to create a second column that mirrors the data in the foreign key column. I tried creating a second entity column that mapped to the same database column and I still get the same runtime error.
Correct me if I'm wrong, but at what point did we slip away from reality and decide that we should fix a limitation in the Entity Framework by violating normalization rules in my back-end database?!?!?! p.s. A similar limitation exists in Rails/ActiveRecord as well - so I am really interested in hearing a good fix for this.
If anybody else has a better workaround than the official solution from Microsoft, I would love to hear it, because personally I think the idea of creating two columns in the database : 1 for discrimination and 1 for foreign key establishment is ludicrous.
well, EVERYBODY would like to hear a good solution for this, probably
including even the ADO.NET developers
what about creating the discrimination column on the fly during the select
based off the FK - probably too internal for the EF.
Its a good thought.. but as far as I can tell, I don't have control over
the select that generates the model.. it works in the other direction. I
define the model, then I define the mapping, and the schema to which the
model maps...and through some "magic" , the mapping provider then creates
SQL statements for me.
