Saturday, January 05, 2008

Let's mix things up!
One of the strengths of the Entity Framework is it's mapping mechanism, which uses views to represent the data needed for an entity. As they call it 'some clever magic' allows the EF to fold and unfold data into views, solving the problem that most database systems (including oracle) do not solve for you: updating and inserting into a view that consists of more than 2 tables.
This allows us to map more information when needed.

In our previous examples, an employee table was defined in the database and our conceptual model used the Table Per Hierarchy mapping strategy to map developers, testers and business analists to that table. We now find that we have quite a bit of information that we need to store about the developers (such as their skill-set) that is of no use to the other types of employees. We could very well add a bunch of nullable columns to the employee table. However, one can only sustain that much filth for a certain time period ;-) Going down that path will soon get out of hand. It's better to create a new table that will hold the information we seek. (Or would you rather create a universal table???)

Thus a new table 'DevelopmentSkills' is added and a primary key DeveloperID is created, along with a few boolean columns. A foreign key relation is created between the Employee table and the new table. Nothing fancy:

EF_Combining_DBDiagram.jpg

I've learned by now that it's best to create the SSDL by hand. So I add the new table to the SSDL:

        <EntityType Name="DevelopmentSkills">
          <Key>
            <PropertyRef Name="DeveloperID" />
          </Key>
          <Property Name="DeveloperID" Type="int" Nullable="false" />
          <Property Name="WPF" Type="bit" Nullable="false" Default="false" />
          <Property Name="WCF" Type="bit" Nullable="false" Default="false" />
          <Property Name="WF" Type="bit" Nullable="false" Default="false" />
          <Property Name="EF" Type="bit" Nullable="false" Default="false" />
        </EntityType>
And the more important association:
        <Association Name="FK_DevelopmentSkills_Employee">
          <End Role="Employee" Type="EntityFrameworkTestModel1.Store.Employee" Multiplicity="1" />
          <End Role="DevelopmentSkills" Type="EntityFrameworkTestModel1.Store.DevelopmentSkills" Multiplicity="0..1" />
          <ReferentialConstraint>
            <Principal Role="Employee">
              <PropertyRef Name="EmployeeID" />
            </Principal>
            <Dependent Role="DevelopmentSkills">
              <PropertyRef Name="DeveloperID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>

This association represents the foreignkey in the database and should be pretty clear by now.
Don't forget to define the sets in the entitycontainer.

Then I was able to use the designer to first create extra properties in the 'einstein' class, our junior developer ;-)
In the mapping designer, choose to map to DevelopmentSkills. Set the DeveloperID column to the PersonID property and map the remaining properties.

Pretty neat, we did not have to map another class, but instead, just extended our current class.

That means that this code is now possible:

Einstein smartNerd = new Einstein()
{
Firstname = "Albert",
Lastname = "Einstein",
Language = "C# 3.5",
WCF = true, WPF = true, WF = true, EF = true,
TeamLeader = e,
};
context.AddToPerson(smartNerd);

Elvis elvis = new Elvis()
{
Firstname = "Elvis",
Lastname = "Presley",
Language = "C# 2.0",
WPF = true, EF = true,
TeamLeader = e,
};
context.AddToPerson(elvis);

One thing to note though: the current designer makes a mistake when mapping the extra table to the Einstein class. It keeps adding new entitytypemappings in the CSDL, with the new mapping fragment (to developmentskills table) instead of combining the fragments in the already defined entitytypemapping. This leads to an error keeping the designer from showing any info. I had to hand edit a few times ;-(

 

 

Wednesday, March 05, 2008 6:07:55 PM (Romance Standard Time, UTC+01:00)
Could I bother you for a small demo project that demonstrates linking 1 entity to 2 tables.

I just dont get this, and could do with just 1 working example.

Many thanks
Monday, March 10, 2008 1:58:25 PM (Romance Standard Time, UTC+01:00)
Hi Sacha,

I do think there are a few demo projects out there that do this. Check the SDK, i'm sure i've seen it.
If you still can not find it, reply again. ;-)

Ruurd
Ruurd
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):