Moving from DevExpress to EDM

Using Microsoft Entity Framework seems to be a good idea. Really, this technology allows us to work with data model, which is equal to database model; to synchronize it with SQL Server; to execute queries using LINQ to Entities... And you don't need to use DevExpress, NHibernate or whatever anymore, just use Microsoft tools (Visual Studio + Entity Framework + SQL Server)!

But let's try to create a model. It will be generated automatically, but only in one direction - "to create a model from a database schema". So first you must have a database! OK, I've got it. But my database was generated by DevExpress: one table per class, using foreign keys for creating a hierarchy of classes-inheritors. Total amount of tables in a database is 230. Yes, it is too many, the diagram which was generated by SQL Server Management Studio is:

 

Picture 1 – Database diagram in MS SQL Server Management Studio

And now I need to generate an objects model of this schema. It seems to be easily - just use Entity Data Model (EDM) editor, which is a part of MS Visual Studio 2008. After specifying server and database, selecting tables model is created. The diagram is: 

Picture 2 – Generated EDM

I don't want to show how it works, how you can execute queries, that it is useful and so on :-) My goal now is to compile this model after generating. Just successfully compile without errors. But first I have to make some changes according to my object model. I need to have a hierarchy of types, but after generating I've got a set of classes and classes-inheritors have one to many relationship with base classes. So I have to set attribute "Base type" of each inheritor to base type value, but after this I also have to delete manually all primary key fields (named OID in my case) of inheritors (because base type already contains the same field and error

Error: A member named OID cannot be defined in class Milestone. It is defined in ancestor class SceneObject.

occurs). And I have to remove association for each relationship manually (picture 3). For more than 200 classes it was taking too much time. I guess these steps should be done automatically :-(

 

Picture 3 – All conflicts with mapping in associations have to be solved manually

The problem with setting of base type attribute may be successfully solved by using advanced version of EDM editor (EDMGen2, see this link). But I haven’t tried it yet, I used only the routine tools of VS 2008.

If you have many to many relationships in you schema, you must keep some specific features of EDM generation in your mind. Many useful facts I got from this post.  But not all.

So, if you have database, which is generated by DevExpress, many to many relationships look like:

Picture 4 – Many to many relationship, generated by DevExpress

But for successful generation of EDM you need a connecting table, which contains compound key (picture 5).

Picture 5 – Many to many relationship, which is required for successful EDM generation

In this case (picture 5) you have got additional class (named StateResource) for connecting table Statestates_Resourceresources. But you don’t need this class, delete them, add direct association between State and Resource classes, set mappings and get the following error after trying to compile:

Error: Problem in Mapping Fragment starting at line 7699: At least one of the key properties of AssociationSet StateResource mus

t be mapped to all the key properties (Statestates_Resourceresources.resources, 

Statestates_Resourceresources.states) of table Statestates_Resourceresources.

But you have mapped all fields already! (picture 6)

Picture 6 – All field are mapped for association

XML code for this mapping is OK too:

<AssociationSetMapping Name="StateResource" TypeName="Model.StateResource" StoreEntitySet="Statestates_Resourceresources">

            <EndProperty Name="Resource">

              <ScalarProperty Name="OID" ColumnName="resources" />EndProperty>

            <EndProperty Name="State">

              <ScalarProperty Name="OID" ColumnName="states" />EndProperty>

AssociationSetMapping>

So, what is wrong? As it happens connecting table MUST NOT contain any other fields with the exception of key fields (picture 7).

Picture 7 – Correct many to many relationship

If you have this relationship (picture 7), EDM will be generated correctly without any additional classes, but if you have that relationship (picture 5), model will be generated with additional class and you will not be able to delete its.

OK, let’s finish with relationships and try to add a new class to our model. There is only one way to do it – add a new table (CheckGate in our case) to database and update your model than (because you can’t update database schema using your model, you can only update data in the database). So let’s create a new table, connect it with other (CheckGate with Shopfloor). After updating you get a new class CheckGate with correct association and mapping, everything is OK (by the way, you can’t update only 1 table from the database, you have to update the whole tables in your database, I guess it is the disadvantage). But let’s delete relationship (between tables CheckGate and Shopfloor) in database and than update our model again. Association wasn’t deleted! You have got the following error:

Error: Association End “Shpfloor” is not mapped.

Picture 8 – Error after update

You have to delete this association manually.

Conclusions: the possibility to have an up-to-date object model of database, to execute query directly to tables via objects is very important, useful and convenient, but there are still too many bottlenecks in the process of generation and modifying of the model: synchronizing only in one direction, problems with updating, hang and crashes of the VS 2008 (picture 9) during modifying a huge model… Sometimes you have to spend too much time to implement very simple features. So I hope the next version of EDM will be more powerful and reliable.

Picture 9 – Crash of VS 2008 while modifying a huge model