Messing around with ADO.NET Entity Framework Beta 3

This post is part of a small series on .NET ORM tools. You can find the rest of them here.

While I was checking out a number of .NET ORM tools, I thought I’d work through the exercise using the ORM-related bits from the ADO.NET Entity Framework Beta 3 and the Entity Designer CTP2.

Scene refresher

I have a table of suppliers, and a table of states (or provinces, territories, prefectures etc.). Both suppliers and states have names, which are stored as strings/varchars, and IDs, which are stored as Guids/uniqueidentifiers. Each supplier can service many states. So we have a simple many-to-many relationship between the two main entities. It looks a bit like this:

I am using Aussie states for my tests, so I have populated the State table with the following names: NSW, VIC, QLD, TAS, SA, WA, ACT, NT.

Setting up Entity Framework

I created a new C# class library project, then added an ADO.NET Entity Data Model file (.edmx) as a project item, which I named WorkshopModel.edmx. Adding the project item runs a wizard that lets you choose the connection, and then select which database items to include in the model (including tables, views and SPs):

Once the wizard completes the model is opened in the designer. Note the recognition of the many-to-many relationship between suppliers and states. I edited to default association name from Supplier_StatesServiced to StatesServiced to make it a bit easier to read.

The designer provides access to loads of properties for the model, so it is possible that some of the code I am going to write further on could be simplified by tweaking the model. At this stage it feels very much like a WYSIWYG way of generating an NHibernate-style mapping file and accompanying entity classes. The designer generates a WorkshopData.Designer.cs file from my WorkshopData.edmx, which includes definitions of the Entity Framework-based classes.

Populating the database

As per my previous posts, I’ll use a test fixture to run the remainder of the code. Here is the method used to create a supplier and map the states it services:

private static void createSupplier(String name, String[] statesServiced) {
  using (WorkshopEntities entityModel = new WorkshopEntities()) {
    Supplier supplier = Supplier.CreateSupplier(Guid.NewGuid(), name);
    foreach (String stateName in statesServiced) {
      supplier.State.Add(entityModel.State.Where(s => s.Name == stateName).First());
    }
    entityModel.SaveChanges();
  } 
}

The WorkshopEntities class gives us access to the relevant Entity Framework bits (it derives from System.Data.Objects.ObjectContext). The designer has generated a static Supplier.CreateSupplier(...) method, but I couldn’t find a nice way of adding all the relevant states at once. I would have loved to do something like this:

List<State> states = (from state in entityModel.State
            where statesServiced.Contains(state.Name)
            select state).ToList();
states.ForEach(state => supplier.State.Add(state));

But this gave me an exception:

EntityFrameworkWorkshop.EntityFrameworkFixture.DatabaseSetup : System.NotSupportedException : The ‘Boolean Contains[String](System.Collections.Generic.IEnumerable`1[System.String], System.String)’ method is not recognized by LINQ to Entities, and cannot be translated into a store expression.

The Entity Framework (as of this release) cannot map the Contains method to Entity SQL. The similar LINQ to SQL query works fine. The difference is that the LINQ to SQL provider is mapping direct to TSQL, whereas the LINQ to Entities provider is mapping to Entity SQL (I think, please correct me if I’m wrong), which needs to work over a variety of sources (not just relational databases). I’m assuming it’s tougher to implement something that works over all sources than simply targeting TSQL, but maybe this is just because we aren’t in RTM yet.

I had a similar problem when I wanted to write entityModel.State.Where(s => s.Name == stateName).Single(), where I was told that it wouldn’t map Single(), but that I might like to try First() instead. Which I did, and it all worked fine. There are a few more comments about this kind of thing here, as well as a list of supported and unsupported methods on MSDN.

Still, at least we have a nice recognition of the relationship between suppliers and states, which was a big drawback with the LINQ to SQL example. We’ll then run the code below to populate the database with our test data:

createSupplier("Dave^2 Quality Tea", new string[] { "NSW", "VIC" });
createSupplier("ORMs'R'Us", new string[] { "NSW" });
createSupplier("Lousy Example", new string[] { "TAS", "VIC" });
createSupplier("Bridge Sellers", new string[] { "QLD" });

Querying the data

I’m going to favour using LINQ to Entities for querying in these examples, rather than Entity SQL. Similarly to NHibernate, which has HQL (Hibernate Query Language), Entity Framework can use a SQL-like language to query the domain objects. This gives both tools a lot of query power, at the cost of embedding logic in strings that aren’t checked by the compiler and that generally won’t work with refactoring tools. I used the ICriteria approach for strongly-typed queries in the NHibernate example, so I’ll use LINQ to Entities for this one.

I’ll quickly run through the first two simple queries I’ve been using in the previous posts, first getting all suppliers and then suppliers that have an "s" in their name:

[Test]
public void Should_be_able_to_get_all_suppliers() {
  using (WorkshopEntities entityModel = new WorkshopEntities()) {
    var suppliers = from supplier in entityModel.Supplier select supplier;
    Assert.That(suppliers.Count(), Is.EqualTo(4));
  }
}
[Test]
public void Should_be_able_to_get_all_suppliers_with_s_in_their_name() {
  using (WorkshopEntities entityModel = new WorkshopEntities())   {
    var suppliers = from supplier in entityModel.Supplier
            where supplier.Name.ToLower().Contains("s")
            select supplier;
    Assert.That(suppliers.Count(), Is.EqualTo(3));
  }
}

And here’s the code to get data over the supplier-state relationship, retrieving all suppliers that service NSW:

[Test]
public void Should_be_able_get_all_suppliers_that_service_NSW() {
  using (WorkshopEntities entityModel = new WorkshopEntities())   {
    var suppliers = from supplier in entityModel.Supplier
            where supplier.State.Any(state => state.Name == "NSW")
            select supplier;
    Assert.That(suppliers.Count(), Is.EqualTo(2));
  }
}

This is much nicer than the LINQ to SQL version, which required explicitly joining over the relationship:

var suppliers = from supplier in db.Suppliers
        join servicedState in db.Supplier_StatesServiceds 
          on supplier.SupplierId 
          equals servicedState.SupplierId
        where servicedState.State.Name == "NSW"
        select supplier;

Vague semblance of a conclusion

We’ve only really looked at the ORM side of the Entity Framework in this example, and it definitely seems very capable in this regard (at least for this simple example), but as noted by David Laribee, ORM is a very small part of the Entity Framework. It’s main potential is in querying across a model that incorporates different data sources and services (say, different databases, XML sources etc), which is definitely an exciting aim.

The only real issue I had using the tool were the unmapped methods, but this was easy to work around. I think the real test of the tool will be how much you can get into the Entity Data Model and how easy it is to work with. Earlier releases faced some criticisms regarding persistance ignorance and the fact you can’t use POCOs for entities, but I believe some of the issues are being addressed (thanks to Tom for the link) by the ADO.NET team based on the feedback they have received.

Comments