Tuesday, 29 January 2008

In my previous project, we used the Composite Application Block with WPF to decouple our application. Lately I have been thinking alot about this, and found some good material on the subject that I wanted to share. But first, let me give a high-level overview of my previous approach. If you are in need to first learn about MVP, check out this dnrTV video where Jean Paul Boodhoo talks about Model View Presenter.

First off, keep in mind that CAB was not written for WPF and has to be hacked to actually be usable.
Secondly, Acropolis is dead and is being turned into guidance material in the future (think end of 2008!).

Cab exposes workitems, controllers and views. We approached the workitem as a simple configurator, which meant it just grabbed the view it wanted and showed it. That was all the responsibility a workitem got, even in wizard style screens.
The controller was our main class, implementing all the screen logic. It was responsible for retrieving and saving data through services and did not communicate directly with the view. The only communication was through the model, that we dubbed as state.
The view was bound to this state class, which could indeed be seen as a viewmodel-model (see Dan Creviers posts on this subject here). We kept the view as dumb as possible and only let it decide on view specific things, never implementing logic. This means, that the controller could decide when a particular state was prudent (for instance, 'this item is on sale'), but the view gets to decide how to present that item to the world ('wow, I'm going to turn the background to yellow').
The codebehind of the view was only a translation layer between the WPF specific calls (events defined in Xaml) and calls to the controller.

This worked great for us, although in hindsight, we did find that the whole solution was a bit over-the-top. We stuck with it though, and I think it worked out well enough. I feel we could have done without the workitems (in our approach atleast).

Since I'm on sabbatical now, I've been reflecting somewhat more on this issue and found a series of incredible posts by Jeremy Miller on how to build your own CAB. Basically, he points out that it's not that hard to implement MVP in your application by building the pieces you need yourself. The upside would be that CAB is a big monster (I completely agree here),not worthy to be used in your kick-ass application (my own interpretation ;-) ) and building the components you need yourself would buy you a much more lightweight system custom fitted to your problem domain.

While reading all of his posts, I kept feeling that most of what is needed for building a more loosely coupled system is already provided by WPF. The resources system is a possible way to inject dependencies and retrieve them. The routed events and usage of commands is also great. Then just now I've found a post which proves that I am right (and the rest of the world is wrong ;-) ) written by Josh Smith himself, a notable wpf blogger. He shows how he actually uses the resources system to inject classes and even mocks.

My main interest at this moment is to decide how to build big systems with big teams and offer tangible guidance on that subject. One of the most important aspects of that is to Keep It Simple, Stupid! I saw that my team was strugling a bit with bending their minds around the whole MVC concept. This might be attributed to lacking explanations by myself, but at the very least, it was also caused by the feeling it brought-in more complexity then it took away. Certainly the less experienced of the team did not feel the de-coupling was worth it. That is why Jeremy's stand on this felt so 'right' for me. I do think that a simpler solution then CAB would be great.

However, Joshes solution does feel a bit contrived to me. A view would indeed be able to find it's presenter(s), but how does the presenter find it's services? Will they also be defined within the resources? Could be. I find some piece of inversion of control is a more straight forward way to configure your components than 'misusing' the resources functionality. Because that's basically what is being done here.
Keep in mind though, that it is mighty handy to be able to use the injected components within the userinterface without having to do weird things. Say you have an 'edit' button and a controller that was created to manage these things. Your view will want to bind the 'enabled' property to that controller. If it was injected somehow behind the scenes, you will have to find a way to bring it into scope for the button.
We solved this, but it's not pretty!

Another solution being worked on is a WPF specific MVC platform by Rob Eisenberg: Caliburn. His solution does tackle one important issue I have with current systems, which is the communication between the view and the controller. He introduces 'ActionMessages' which can be used in xaml like so:

  1.       <Button Content="="    
  2.               Margin="3">    
  3.          <Engine:Views.AddMessageSource>    
  4.             <Engine:ActionMessage Trigger="Click"    
  5.                                   Action="Divide"    
  6.                                   Return="{Binding ElementName=result, Path=Text}">    
  7.                <Engine:Parameter Value="{Binding ElementName=leftSide, Path=Text}" />    
  8.                <Engine:Parameter Value="{Binding ElementName=rightSide, Path=Text}" />    
  9.             </Engine:ActionMessage>    
  10.          </Engine:Views.AddMessageSource>    
  11.       </Button> 

When the button is clicked, the method 'Divide' will be called on your controller, with two parameters. It's return value will be bound to the result textbox.

Talk about a great idea!! He has used this framework for great succes and I can see why. Incredibly powerful.

However, it does bring in yet another mechanism unfamiliar to your teammembers. Probably worth it though, I'm looking forward to delving into the project and using it myself.
I don't like the introduction of yet another class of things that can go wrong at runtime though: the Action method (here: Divide) should exist on your presenter. If it's not, I'm sure you will only get an error at runtime. As if the binding syntax wasn't enough ;-)

All in all, it seems there is lots of movement in the Microsoft community. People have been thinking about how to build userinterfaces for a long time, and I'm happy to see that this is also the case in the .Net world and very happy to see movement in the WPF camp on this front.

More on this soon!

Tuesday, 29 January 2008 00:19:38 (Romance Standard Time, UTC+01:00)  #    Comments [15]  |  Trackback
 Wednesday, 23 January 2008

David Chapell is writing tons of whitepapers lately, and his last one seems like a whitepaper I could've used on more than one occasion ;-)

Go print it and give it to your manager.

Wednesday, 23 January 2008 19:54:27 (Romance Standard Time, UTC+01:00)  #    Comments [7]  |  Trackback
 Saturday, 05 January 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 ;-(

 

 

Saturday, 05 January 2008 23:15:36 (Romance Standard Time, UTC+01:00)  #    Comments [2]  |  Trackback

We have seen two types of inheritance modeling, at this point I'm interested in modeling an association. This actually turned out to be harder then I had expected, even though the documentation on this subject is good and plentyfull ;-)

However, since you are still reading, I will create a very simple example in the employee class, such that an employee needs to report to another employee.
Remember that employee inherits from person. I was happy to see that I was indeed able to create the relationship!

I've added a new column in the employee table, named: ReportsToID and a foreignkey relation that specifies the primary key base to be Employee/EmployeeID and the foreign key base to be Employee/ReportsToID. In database lingo this simple means that ReportsToID can be filled with an exact EmployeeID and that this relationship is verified (for instance, when deleting an employee, but still having other employees which report to the deleted employee).

 Let's look at the SSDL that I created to match this new foreignkey relationship:

In the EntityContainer section:
          <AssociationSet Name="FK_Employee_Manager"
                          Association="EntityFrameworkTestModel1.Store.FK_Employee_Manager">
            <End Role="Manager" EntitySet="Employee" />
            <End Role="Members" EntitySet="Employee" />
          </AssociationSet>
And the mentioned AssociationSet:
        <Association Name="FK_Employee_Manager">
          <End Role="Manager" Type="EntityFrameworkTestModel1.Store.Employee" Multiplicity="0..1" />
          <End Role="Members" Type="EntityFrameworkTestModel1.Store.Employee" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Manager">
              <PropertyRef Name="EmployeeID" />
            </Principal>
            <Dependent Role="Members">
              <PropertyRef Name="ReportsToID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>

As you can see, I named the roles: Manager and Members.
The association can be interpreted as follows: An employee (a Member) can have zero or one Manager. A Manager can have zero to infinite Members.

Let's take a look at the C-side of life.
In the EntityContainerSection:
          <AssociationSet Name="TeamMemberToTeamLeader"
                          Association="EntityFrameworkTestModel1.TeamMemberToTeamLeader">
            <End Role="TeamMembers" EntitySet="Person" />
            <End Role="TeamLeader" EntitySet="Person" />
          </AssociationSet>
And the Association is defined as:
        <Association Name="TeamMemberToTeamLeader">
          <End Type="EntityFrameworkTestModel1.Employee" Role="TeamLeader" Multiplicity="0..1" />
          <End Type="EntityFrameworkTestModel1.Employee" Role="TeamMembers" Multiplicity="*" />
        </Association>

I like to use real desciptive names, but this should be easy enough to follow.

Also, I added navigationProperties to my model, so I can easily follow an association:
        <EntityType Name="Employee" BaseType="EntityFrameworkTestModel1.Person">
          <NavigationProperty Name="TeamLeader" Relationship="EntityFrameworkTestModel1.TeamMemberToTeamLeader" FromRole="TeamMembers" ToRole="TeamLeader" />
          <NavigationProperty Name="TeamMembers" Relationship="EntityFrameworkTestModel1.TeamMemberToTeamLeader" FromRole="TeamLeader" ToRole="TeamMembers" />
        </EntityType>

Remember that an association is an independent entity, it only defines a relation that can be traversed. We need Navigationproperties to actually use the association. This way it can be shared between models.

The actual mapping between the store and the conceptual model is interesting:
          <AssociationSetMapping Name="TeamMemberToTeamLeader"
                                 TypeName="EntityFrameworkTestModel1.TeamMemberToTeamLeader"
                                 StoreEntitySet="Employee" >
            <EndProperty Name="TeamMembers">
              <ScalarProperty Name="PersonID" ColumnName="EmployeeID" />
            </EndProperty>
            <EndProperty Name="TeamLeader">
              <ScalarProperty Name="PersonID" ColumnName="ReportsToID" />
            </EndProperty>
            <Condition ColumnName="ReportsToID" IsNull="false" />
          </AssociationSetMapping>

Here you can see that I am mapping the ScalarProperty PersonID instead of EmployeeID; remember we are using inheritance here.

The condition is needed to solve some errors I was having. I think it can be read as: the association only works if there is a reportsID value set.

Some test code:

Employee e = new Employee();
e.Firstname = "Ian";
e.Lastname = "Mort";
context.AddToPerson(e);

Einstein smartNerd = new Einstein();
smartNerd.Firstname = "Albert";
smartNerd.Lastname = "Einstein";
smartNerd.Language = "C# 3.5";
smartNerd.TeamLeader = e;
context.AddToPerson(smartNerd);

Elvis elvis = new Elvis();
elvis.Firstname = "Elvis";
elvis.Lastname = "Presley";
elvis.Language = "C# 2.0";
elvis.TeamLeader = e;
context.AddToPerson(elvis);

At this point I can check that e.TeamMembers has a count of 2 and both employees have a teammember property! So, everything working as expected.

Reading back this post, I can see it was actually pretty simple. However, the syntax seems to be overly complex and a small mistake leads to weird errors!

 

Saturday, 05 January 2008 15:09:31 (Romance Standard Time, UTC+01:00)  #    Comments [1]  |  Trackback
 Friday, 04 January 2008

Last post showed a very simple database setup with a Person, a Customer and an Employee-table. They were mapped to different classes in what is called a Table per Type mapping.

A different kind of mapping strategy is the Table Per Hierarchy mapping, where all the types in your hierarchy are mapped to the same table. In many cases your dba will not understand why there should be different tables for information that can clearly be represented within the same table! For instance, different types of employees (developer, manager, architects) will certainly re-use the employee table, where very often a column is added to differentiate between these types.

Far be it from me to disagree with such a database-schema. It makes perfect sense and is a clear example of the Object relational impedance mismatch. The domain-model needs to break free of these kinds of reasoning and needs to define it's own hierarchy.
It can do so by leveraging the column that was added to diffentiate between types. In OR-Mapping, it is called a discriminator column, as the system can use it to discriminate between different the class-type it should use.

I added a number of fun classes to the model, that represent developers: Einstein, Elvis and Mort. In this company, each inherits properties from the other, in a possibly unexpected way ;-)
There is also the Business analist and a Tester.

All these types have properties that are mapped to the same table. However, as your dba has come to understand, the different types of employees do differ a little and in time this has led to the introduction of a few extra columns in the employee table: 'ProgrammingLanguage' and 'TestMethodology'. Too bad these have to be nullable, because they don't make sense in all cases!
Also, the functionname column of the previous post was changed to functiontype (int) and will act as the discriminator.

Our database now looks like this:

EF_TPH_DBDiagram..jpg

Nothing fancy here.

To create the conceptual model, I could use the designer. Again, if I update from the database, the model becomes useless, but editing the SSDL was easy enough in this case.
Adding the new entities in the model designer was also easy:

  • There should not be a scalar property 'FunctionType' in the model. The discriminator works behind the scenes, and that is a good thing! As a user of the domain model, you should not need to know about it.
  • Only when you delete that property, can it be used as a 'condition', which basically is the condition that EF uses to see which class-type it should instantiate.
  • To map the condition, you need to map the various types to the employee table, even though they are already mapped through their parent 'Employee'!! No properties need to be mapped though.
  • When adding the condition, a value can be set. When you set the value, the designer does make an error in the xml which you will have to fix yourself!! So, when I wanted the type 'Einstein' to have the condition 'FunctionType = 1', I let the designer create it, and went to the line the build error indicates as being wrong. There I changed some weird value like '_x3301' to just '1'.

The model now looks like this:

EF_TPH_ConceptualModel..jpg

Using the following test code:

....

Einstein smartNerd = new Einstein();
smartNerd.Firstname = "Albert";
smartNerd.Lastname = "Einstein";
smartNerd.Language = "C# 3.5";
context.AddToPerson(smartNerd);

Elvis elvis = new Elvis();
elvis.Firstname = "Elvis";
elvis.Lastname = "Presley";
elvis.Language = "C# 2.0";
context.AddToPerson(elvis);

Mort mort = new Mort();
mort.Firstname = "Mort";
mort.Lastname = "Builder";
mort.Language = "VB.Net";
context.AddToPerson(mort);

BusinessAnalist George = new BusinessAnalist();
George.Firstname = "George";
George.Lastname = "Clooney";
context.AddToPerson(George);

Tester tester = new Tester();
tester.Firstname = "UnitTests";
tester.Lastname = "Rock";
tester.Methodology = "TMap";
context.AddToPerson(tester);

....

I got the following expected outcome:

Ian, Mort is a Employee
Albert, Einstein is a Einstein
Ruurd, Boeke is a Person
Silvia, Banana is a Customer
George, Clooney is a BusinessAnalist
UnitTests, Rock is a Tester
Elvis, Presley is a Elvis
Mort, Builder is a Mort

The employee table was filled like this:

55 0 NULL NULL
56 1 C# 3.5 NULL
59 4 NULL NULL
60 5 NULL TMap
61 2 C# 2.0 NULL
62 3 VB.Net NULL

So, that's all there is to it.
Hopefully the data-team will fix the mapping bug in the next ctp!

Next up, I will mix it up just a bit.

Friday, 04 January 2008 01:17:45 (Romance Standard Time, UTC+01:00)  #    Comments [7]  |  Trackback
 Wednesday, 02 January 2008

I'm going to jump right into the Entity Framework here, with a simple how-to. If you want a gentle introduction into this technology, you can probably read some future posts, but you will be far better off reading the ADO.Net teamblog or better still, read all of the entries of Danny Simmons.

An OR-mapper needs to be flexible in mapping your domain classes, so you can describe your domain as truthfully as possible. The entity framework positions itself to be more than an OR-Mapper: it sees itself as a translation technology between models. One of those models is defined by your database, the other by your domain (your conceptual model). It's interesting to see what types of inheritance are possible. In this post I will give a sample of the Table Per Type (TPT) inheritance, where you define an inheritance in your conceptual model and use different tables to persist the data.

The way to implement this is fairly straightforward and documented, however, I certainly had some problems getting it to work. As did Scott Allen, so I thought I would create a small walk through for this scenario.

EF_TBT_DBDiagram.jpg

As you can see, my database (SqlExpress) has 3 tables. The 'Person' table, a customer table and an employee table. There are foreign key relations between them, with the Person table being the Primary/Unique Key Base Table and the Customer and Employee tables being the Foreign Key Base Tables. It is important to note that the PersonID column is set up to automatically generate ID's, and the CustomerID and EmployeeID columns are not: we want to create a situation where an object like Customer is persisted to these two tables (customer and person) and the Person table is the one supplying a new ID. The Customer table will just use that PersonID as it's own CustomerID.

That concludes the database schema, next up is the conceptual model, ugh, I mean domain model:

EF_TBT_ConceptualModel.jpg

This is how I want to my conceptual model to look like.  

When you have just generated the model from the database, the foreign key relations are visible. Remove them! Then, also remove the CustomerID and EmployeeID properties that were generated. Add the two inheritance links.
Since Customer already maps to Person (through the inheritance) you do not have to map these again. You do have to add a mapping to the customer table. The key point here is that the CustomerID column needs to be mapped to the PersonID property. Same goes for Employee.

Now, this is all there is to it. Before we dive into the XML generated, let me just point this out: when you update from the database again, the designer will fail. This is a major problem, where the synchronization just isn't good enough. It will encounter problems with the foreign key associations that it has recreated. I hope this gets fixed in the following CTP.

Now, although deceptively easy through the designer, I have a gut-feeling that it's going to be best to actually understand all the XML mapping that is generated. Just like the WPF and WF designers immediately made me turn to learning Xaml and Xoml, I feel that this designer is going to force me to learn CSDL and CS mapping pretty quickly.

When opening the edmx file with the XML editor, quickly use ctrl-E-F to format everything. The designer creates very long lines.
The SSDL content is unimportant, since it represents the database and should not be tinkered with here.
The CSDL is as follows:

    <edmx:ConceptualModels>
      <Schema Namespace="EntityFrameworkTestModel1" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="EntityFrameworkTestEntities4">
          <EntitySet Name="Person" EntityType="EntityFrameworkTestModel1.Person" />
        </EntityContainer>
        <EntityType Name="Customer" BaseType="EntityFrameworkTestModel1.Person">
          <Property Name="CustomerDiscount" Type="Int32" Nullable="false" />
        </EntityType>
        <EntityType Name="Employee" BaseType="EntityFrameworkTestModel1.Person">
          <Property Name="FunctionName" Type="String" Nullable="false" MaxLength="50" />
        </EntityType>
        <EntityType Name="Person">
          <Key>
            <PropertyRef Name="PersonID" />
          </Key>
          <Property Name="PersonID" Type="Int32" Nullable="false" />
          <Property Name="Firstname" Type="String" Nullable="false" MaxLength="50" />
          <Property Name="Lastname" Type="String" Nullable="false" MaxLength="50" />
        </EntityType>
      </Schema>
    </edmx:ConceptualModels>

Note a container with only one set: the Person set. Then the three types are defined. Only the Person type has a key identified, the Customer and the Employee do not!

The C-S mapping then, glues this model to the SSDL:

      <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
        <EntityContainerMapping StorageEntityContainer="dbo" CdmEntityContainer="EntityFrameworkTestEntities4">
          <EntitySetMapping Name="Person">
            <EntityTypeMapping TypeName="IsTypeOf(EntityFrameworkTestModel1.Person)">
              <MappingFragment StoreEntitySet="Person">
                <ScalarProperty Name="PersonID" ColumnName="PersonID" />
                <ScalarProperty Name="Firstname" ColumnName="Firstname" />
                <ScalarProperty Name="Lastname" ColumnName="Lastname" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(EntityFrameworkTestModel1.Customer)">
              <MappingFragment StoreEntitySet="Customer">
                <ScalarProperty Name="PersonID" ColumnName="CustomerID" />
                <ScalarProperty Name="CustomerDiscount" ColumnName="CustomerDiscount" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(EntityFrameworkTestModel1.Employee)">
              <MappingFragment StoreEntitySet="Employee">
                <ScalarProperty Name="PersonID" ColumnName="EmployeeID" />
                <ScalarProperty Name="FunctionName" ColumnName="FunctionName" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>

Here we see the PersonID being mapped to either the CustomerID column or the EmployeeID column. Interesting!

In code, this allows us to do the following:

EntityFrameworkTestEntities4 context = new EntityFrameworkTestEntities4();


// inserting some
Person p = new Person();
p.Firstname = "Ruurd";
p.Lastname = "Boeke";
context.AddToPerson(p);

Customer c = new Customer();
c.Firstname = "Silvia";
c.Lastname = "Banana";
c.CustomerDiscount = 10;
context.AddToPerson(c);

Employee e = new Employee();
e.Firstname = "Ian";
e.Lastname = "Mort";
e.FunctionName = "Developer";
context.AddToPerson(e);

context.SaveChanges();

IQueryable<Person> persons =
from person in context.Person
select person;

foreach (Person person in persons)
{
Console.WriteLine("{0}, {1} is a {2}",
person.Firstname, person.Lastname, person.GetType().Name);
}

Console.ReadLine();

Did you notice that the context was first saved, before the Linq query was executed? It's easy to forget, but the query is executed on the database, not on a Union of objects in the database and in the context.

The output is as expected:

Ruurd, Boeke is a Person
Silvia, Banana is a Customer
Ian, Mort is a Employee

What I really like about this mapping strategy, is the fact that no discriminator column was needed. The system knows that an employee is an employee because of the existence of the record in the employee table. Let's quickly look at the generated query

SELECT
CASE WHEN (( NOT (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL))) AND ( NOT (([UnionAll1].[C3] = 1) AND ([UnionAll1].[C3] IS NOT NULL)))) THEN '0X' WHEN (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL)) THEN '0X0X' ELSE '0X1X' END AS [C1],
[Extent1].[PersonID] AS [PersonID],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname],
CASE WHEN (( NOT (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL))) AND ( NOT (([UnionAll1].[C3] = 1) AND ([UnionAll1].[C3] IS NOT NULL)))) THEN CAST(NULL AS int) WHEN (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL)) THEN [UnionAll1].[CustomerDiscount] END AS [C2],
CASE WHEN (( NOT (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL))) AND ( NOT (([UnionAll1].[C3] = 1) AND ([UnionAll1].[C3] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(50)) WHEN (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL)) THEN CAST(NULL AS nvarchar(50)) ELSE CAST( [UnionAll1].[C1] AS nvarchar(50)) END AS [C3]
FROM [dbo].[Person] AS [Extent1]
LEFT OUTER JOIN (SELECT
    [Extent2].[CustomerID] AS [CustomerID],
    [Extent2].[CustomerDiscount] AS [CustomerDiscount],
    CAST(NULL AS nvarchar(max)) AS [C1],
    cast(1 as bit) AS [C2],
    cast(0 as bit) AS [C3]
    FROM [dbo].[Customer] AS [Extent2]
UNION ALL
    SELECT
    [Extent3].[EmployeeID] AS [EmployeeID],
    CAST(NULL AS int) AS [C1],
    [Extent3].[FunctionName] AS [FunctionName],
    cast(0 as bit) AS [C2],
    cast(1 as bit) AS [C3]
    FROM [dbo].[Employee] AS [Extent3]) AS [UnionAll1] ON [Extent1].[PersonID] = [UnionAll1].[CustomerID]

A left outer join was done with a union of customer and employee. Also note the CASE statements in the main select statement.
I leave it up to you to decide if such SQL statements are okay in your environment. I see no problem with them but I can see them growing pretty fast.

Let's take a look at what happens if you only want your customers. The following Linq expression can be used:

IQueryable<Customer> customers =
from customer in context.Person.OfType<Customer>()
select customer;

When executed, the following SQL is executed:

SELECT
'0X0X' AS [C1],
[Extent1].[CustomerID] AS [CustomerID],
[Extent2].[Firstname] AS [Firstname],
[Extent2].[Lastname] AS [Lastname],
[Extent1].[CustomerDiscount] AS [CustomerDiscount]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[PersonID]

All in all very straightforward.

Wednesday, 02 January 2008 18:56:31 (Romance Standard Time, UTC+01:00)  #    Comments [11]  |  Trackback