I have a mail from Tim on the EF team that I've had for a little while. I'd asked a question about mapping and he'd given me an answer saying something like "Oh, you could always do X, Y, Z" and I'd left this mail in my inbox for a while because I've been distracted and also because I didn't really understand how to do X, Y and Z but I didn't really want to say :-)

Today, I think I understood a bit more of it and the result is twofold.

The first of those folds is that I'm thinking that I'd really like to spend maybe a week in a darkened room doing nothing but Entity Framework but I doubt that I'll get the chance as (strangely) in my job I'm not really encouraged to learn technology but that's another story and not one that I'll bore you with here.

The second of those folds is that I really think that the Entity Framework still has a whole lot of documentation and explanation that's going to be needed if people are really going to get it - there's a lot in there and it's not immediately obvious how to use it and (frankly) right now I find that sitting in an XML editor with an EDMX file, IntelliSense, the docs and a whole tonne of curiosity is proving to be far more rewarding than sitting in the designer. Don't get me wrong, the designer's fine and I'm sure it'll get better but, right now, it seems to limit choices making you think that you can only do a few things when you can actually do a lot more.

An aside...if you're putting together some kind of data-oriented conference feel free to include many sessions on Entity Framework. I feel that you need at least;

1. Overview of Entity Framework and LINQ to Entities.
2. One or possibly two sessions just to explain what SSDL, CSDL and MSL can actually do for you. It needs to be deep stuff with lots of examples.
3. Session explaining EntitySQL and what additional constructs it has (I wrote about some of those once and gathered up the links here).
4. Session explaining LINQ to Entities.
5. Possibly a session explaining how to use the designer, how to use the command line tools and what the top 10 error messages mean :-)

Sure, it's not every conference that's going to be able to include all that stuff but someone, somewhere needs to do it :-)

Anyway, what I hadn't appreciated previously that Tim had alluded to in his mail was that I can have a piece of SSDL that looks like this (deliberately kept simple);

      <Schema Namespace="NorthwindModel.Store"
              Alias="Self"
              ProviderManifestToken="09.00.3054"
              xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="dbo">
          <EntitySet Name="Customers" EntityType="NorthwindModel.Store.Customers" />
        </EntityContainer>
        <EntityType Name="Customers">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID" Type="nchar" Nullable="false" MaxLength="5" />
          <Property Name="CompanyName" Type="nvarchar" Nullable="false" MaxLength="40" />
          <Property Name="ContactName" Type="nvarchar" MaxLength="30" />
          <Property Name="ContactTitle" Type="nvarchar" MaxLength="30" />
          <Property Name="Address" Type="nvarchar" MaxLength="60" />
          <Property Name="City" Type="nvarchar" MaxLength="15" />
          <Property Name="Region" Type="nvarchar" MaxLength="15" />
          <Property Name="PostalCode" Type="nvarchar" MaxLength="10" />
          <Property Name="Country" Type="nvarchar" MaxLength="15" />
          <Property Name="Phone" Type="nvarchar" MaxLength="24" />
          <Property Name="Fax" Type="nvarchar" MaxLength="24" />
        </EntityType>
        <Function Name="InsUkCustomer"
                  IsComposable="false">
          <CommandText>insert customers(CustomerId,CompanyName) values(@Id, @Company)</CommandText>
          <Parameter Name="Id"
                     Mode="In"
                     Type="nvarchar"/>
          <Parameter Name="Company"
                     Mode="In"
                     Type="nvarchar" />
        </Function>
        <Function Name="DelUkCustomer"
                  IsComposable="false">
          <CommandText>
            delete customers where customerid = @id
          </CommandText>
          <Parameter Name="Id"
                     Mode="In"
                     Type="nvarchar"/>            
        </Function>
        <Function Name="UpdUkCustomer"
                  IsComposable="false">
          <CommandText>
            /* TODO */
          </CommandText>
        </Function>
      </Schema>

This is just saying that we have an entity in the DB called Customers (it's from Northwind) and that we've got three functions. Except, they're not really functions. They're just pieces of SQL defined right here in the SSDL file with the <CommandText/> element. I hadn't appreciated that this was something that I could do - I figured that Functions meant SPs in the database but, clearly, that's not the case :-)

With that in place, I've defined in my conceptual model an entity set called UkCustomers which is ( as you'd expect ) just populated with customers from the UK. It looks like this;

<Schema Namespace="NorthwindModel"
              Alias="Self"
              xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="NorthwindEntities">
          <EntitySet Name="UkCustomers"
                     EntityType="NorthwindModel.Customers" />
        </EntityContainer>
        <EntityType Name="Customers">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID"
                    Type="String"
                    Nullable="false"
                    MaxLength="5"
                    FixedLength="true" />
          <Property Name="CompanyName"
                    Type="String"
                    Nullable="false"
                    MaxLength="40" />
          <Property Name="ContactName"
                    Type="String"
                    MaxLength="30" />
          <Property Name="ContactTitle"
                    Type="String"
                    MaxLength="30" />
          <Property Name="Address"
                    Type="String"
                    MaxLength="60" />
          <Property Name="City"
                    Type="String"
                    MaxLength="15" />
          <Property Name="Region"
                    Type="String"
                    MaxLength="15" />
          <Property Name="PostalCode"
                    Type="String"
                    MaxLength="10" />
          <Property Name="Country"
                    Type="String"
                    MaxLength="15" />
          <Property Name="Phone"
                    Type="String"
                    MaxLength="24" />
          <Property Name="Fax"
                    Type="String"
                    MaxLength="24" />
        </EntityType>
      </Schema>

So, nothing radical in there at all but what I find "fun" is in the MSL mapping between these two things which I did using a QueryView. Julie has a great post over here about QueryViews and DefiningQuery.

Here's my mapping;

      <Mapping Space="C-S"
               xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
        <EntityContainerMapping StorageEntityContainer="dbo"
                                CdmEntityContainer="NorthwindEntities">
          <EntitySetMapping Name="UkCustomers">
            <QueryView>
              using NorthwindModel;
              select value NorthwindModel.Customers(
              c.CustomerID, c.CompanyName, c.ContactName, c.ContactTitle,
              c.Address, c.City, c.Region, c.PostalCode, c.Country,
              c.Phone, c.Fax)
              from dbo.Customers as c
              where c.Country = 'UK'
            </QueryView>
            <EntityTypeMapping TypeName="NorthwindModel.Customers">
              <ModificationFunctionMapping>
                <InsertFunction FunctionName="NorthwindModel.Store.InsUkCustomer">
                  <ScalarProperty Name="CustomerID" ParameterName="Id" Version="Current"/>
                  <ScalarProperty Name="CompanyName" ParameterName="Company" Version="Current"/>
                </InsertFunction>
                <DeleteFunction FunctionName="NorthwindModel.Store.DelUkCustomer">
                  <ScalarProperty Name="CustomerID" ParameterName="Id" Version="Original"/>
                </DeleteFunction>
                <UpdateFunction FunctionName="NorthwindModel.Store.UpdUkCustomer"/>
              </ModificationFunctionMapping>
            </EntityTypeMapping>              
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>

I found this "interesting" :-) I can just define a query that brings back my UK customers and when it comes to doing the insert, update, delete on it I can just map those to the (incomplete) functions that I wrote inline at the store level.

Neat. Then I can go write some LINQ code;

      using (NorthwindEntities ent = new NorthwindEntities())
      {
        Customer c = new Customer()
        {
          CustomerID = "TESTX",
          CompanyName = "C1"
        };
        ent.AddToUkCustomers(c);
        ent.SaveChanges();
      }

I found this exciting enough that I'm finishing this post at 1am having been playing around with this stuff - how sad is that? :-)