Matthieu MEZIL

EDM can save your hair :-)

I am doing a training this week and one of my student told me that his DB can be used with EF. Indeed, his base isn't great for EF:

• a table Bases with 4 columns:
  • Id, int, Identity (PK)
  • Key, int, not null
  • Value, int, not null
  • Text, nvarchar

He explained me that Value is an int between 1 and 9 which can't be changed and Key is Id * 10 + Value

• a table Inheriteds with 3 columns:
  • Id, int, Identity (PK)
  • Key, int, not null
  • Text2, nvarchar

His Key column is the same as the Bases one and so, in fact, he has a relationship 1 -> 0..1 between Bases and Inheriteds.

• a table Descriptions with 4 columns:
  • Id, int, Identity (PK)
  • Key, int, not null
  • Language, nchar
  • Text, nvarchar

His Key column is the same as the Bases one and he has a relationship 1 -> * between Bases and Descriptions, as if Descriptions PK was Key and Language.

In CSDL part, he wanted to have:

• an asbtract entity type Base (mapped on Bases)
• an entity type Entity which inherits Base and which is mapped on Inheriteds
• an entity Description
• an 1 -> * association between Base and Description

Is it possible to do this scenario with EF? Barack Obama knows the answer: yes we can!

So how to do this?

• First, in the designer, after importing tables and renaming generated entity types, you delete the Id property of each.
• Then, you set Base.Key, Description.Key and Description.Language as EntityKey.
• You set Base entity key abstract
• You delete Entity.Key
• You add inheritance between Entity and Base and you fix the Entity mapping (ie: you map Key column on Base.Key)
• You add association between Base and Description

Of course, at this point, your model isn't correct.

To fix it, you have to change the SSDL and to simulate a DB as you want (without Id and with Key as PK).

As Key is computed, you change his StoreGeneratedPattern to Computed.

Then you have to manage the CUD operations of Base entity type because you have to compute the KeyValue.

Base is abstract so you have to define CUD operations for its inherited entity types (Entity here).

Now, I have to map Entity entity type with my SSDL functions:

And that's all. I can now have a good object conception for my entities without concerning myselft with the DB constraints.

TPC can be strange

Imagine the following tables:

• Table1
  • Column1 Type1 (PK)
  • Column2 Type2
• Table2
  • Column1 Type1 (PK)
  • Column2 Type2
  • Column3 Type3

Now, imagine that we want to do the following mapping scenario:

• EntityType1
  • Prop1
  • Prop2
• EntityType2 : EntityType1
  • Prop3

To do this, we make TPC to map EntityType2 on Table2.

Then context.EntityTypes1 generates a LEFT OUTER JOIN SQL request.

Now what about context.EntityTypes1.OfType<EntityType2>()?

In this case, as all the EntityType2 properties are mapped on Table2 columns, the query is only on Table2 without including INNER JOIN with Table1.

So what is the problem?

The problem is if you have some records in Table2 without associated record in Table1 (I mean with the same keys). Indeed, in this case, context.EntityTypes1.OfType<EntityType2>() will return them and context.EntityTypes1 won't.

EDM Designer

I didn’t blog for a (too) long time which is strange for me.

I received some emails asking if I was ok and I found in this a similitude between Scott Guthrie and myself. Lol

So what did I do during my nights this last month? No I don’t sleep more and no I wasn’t in a 6 weeks’ vacation. 

In fact, I'm beginning a very funny project: doing an EDM designer! And I thought that it was time I did my first WPF project.

If you look at the code, you will see a VB project. Yes Julie, you read well.  This project is used to write an edmx file from my classes and, instead of using serialization, I choose to play with VB9 XML Literal. For all other projects, I use C# of course.

The first version is only a CSDL read-only designer.

I think it makes two interesting points compared to the current MS designer:

• The ability to show only some entity types and not all
• The ability to show Complex Type

What do I want to add in the following versions?

• The ability to add or remove csdl type / property / relation / inheritance (fixing these bugs of course) and to save it into an edmx file
• The ability to change csdl type / property properties like the name, the visibility, etc.
• The ability to show and change the mapping (including TPC which isn’t supported by current MS designer)
• The ability to (re)generate csdl entity types from ssdl entity types. (In current MS designer, it generates it when you add the table / view from the wizard but if you delete your csdl entity type, you have to regenerate it yourself or to delete your ssdl entity type and to use the wizard to add it again. Moreover, for SSDL View, you have nothing to generate the CSDL entity type)
• The ability to add SSDL Views or Functions
• Integrate it in VS
• The ability to save your designer

You can download it here.

As I said previously, I am a WPF beginner so if you have time to audit it and if you have some remarks, advices, criticism, post a comment please.