Living in the Tech Avalanche Generation

Home > Design Patterns, Entity Framework, LINQ To SQL, ORM > Entity Framework Repository, Specifications and Fetching Strategies.

Entity Framework Repository, Specifications and Fetching Strategies.

January 13th, 2009 Simon Segal Leave a comment Go to comments

Update: 4th March 2010

Many people are interested in learning and understanding about the connection between the Repository, Specifications and Fetching Strategies, indeed this is one of the most popular posts on this blog. Therefore if you fall into this category and you are interested in working with the Entity Framework in a very ‘explicit’ way, I suggest you also look at this series of posts listed below once you are finished here.

Now on with the rest of this post.

Not too long ago I posted a library that included Repository, Specification and Fetching strategy patterns that were designed to work with LINQ To SQL. If you have been reading this blog of late you will know that I have been moving my ORM focus away from LINQ To SQL and fixing it more on the Entity Framework; in so doing I decided to refactor the entire library so that it supported both OR/M products. Most recently I also made it clear that I felt the idea of Fetching Strategies was (for now) a waste of time (in the Entity Framework) but I have (as promised) reworked the earlier code libraries I posted for LINQ To SQL. The libraries contain a base / generic Repository and base Specification that can be utilised in building more specific Repositories (ala DDD) and the specifications are useful in building dynamic queries and testing for entity equality. I have also decided that another amendment should be made as to my original intent and therefore not pursue a single library to support both Microsoft ORM products and thus leave them as separate. You may however notice that the two Repositories look similar from the outside in.

Testing for equality

/// <summary>
/// Tests the entity equality matching
/// via a specification.
/// </summary>
[Test()]
public void TestExpressions()
{
    Order o1 = new Order();
    o1.OrderID = 1;
    o1.Customer = new Customer();
    o1.Customer.Country = “Australia”;

    Specification<Order> firstSpec =
        new Specification<Order>(o => o.OrderID == 1);

    Specification<Order> secondSpec =
        new Specification<Order>(o => o.Customer.Country == “Australia”);

    var orSpec = firstSpec | secondSpec;

    Console.WriteLine(“The Order o1 matches? {0}”, orSpec.Matches(o1));

    Assert.IsTrue(orSpec.Matches(o1));
}

This test demonstrates the ability of Specifications to query in memory entities for equality based on lambda expressions. I have talked about this before in the posts on the LINQ To SQL version so for more discussion about Specifications I suggest you look here.

The Specification interface

public interface ISpecification<T>
{
    Expression<Func<T, bool>> EvalPredicate { get; }
    Func<T, bool> EvalFunc { get; }
    bool Matches(T entity);
}

As stated the Specification interface carries a method signature designed to test equality, it also contains properties that return generic expressions and Functions that can be passed as predicate styled arguments to extension methods of IQueryable<T> providers such as LINQ To SQL and LINQ To Entities.

Specifications (no Repository yet!)

Our next test demonstrates a simpler single specification however this time it’s applied as a predicate to the .Where extension method and for good measure we throw in some eager loading so we can sum our order details and check the customers country with out delayed round trips.

[Test()]
public void SimpleSpecificationExample()
{
    Specification<Order> order_spec =
        new Specification<Order>(o => o.Order_Details.All
            (od => od.Product is DiscontinuedProduct));

    using (var ctx = new NorthwindEntities())
    {
        ObjectQuery<Order> orders =
            (ObjectQuery<Order>)
            from o in ctx.Orders
                .Include(“Order_Details”)
                .Include(“Customer”)
            .Where(order_spec.EvalPredicate)
            select o;

        Console.WriteLine(orders.ToTraceString());

        foreach (var order in orders)
        {
            var sumup =
                order.Order_Details.Sum
                (od => od.Quantity * od.UnitPrice);
            Console.WriteLine(“The sum of orders for order ID: “ +
                “{0} is {1} and Country is {2}”,
                order.OrderID.ToString(), sumup.ToString(),
                order.Customer.Country);
        }

        //test if all the products retrieved are indeed discontinued
        var discontinued = orders.Count(o => o.Order_Details.Count
            (od => od.Product as DiscontinuedProduct == null) > 1);

        Assert.AreEqual(discontinued, 0);
    }
}

The TSQL that the Entity Framework produces here is as expected (I guess it’s not the most readable thing I have seen (output in VS.Net), so I have changed it around to make it more readable at least for me). Notice how the specification has played it’s part in building the correlated sub-query towards the end of the TSQL script, with all the conditionals to work out which products have been discontinued.

SELECT
[Project2].[EmployeeID] AS [EmployeeID],
[Project2].[Freight] AS [Freight],
[Project2].[OrderDate] AS [OrderDate],
[Project2].[OrderID] AS [OrderID],
[Project2].[RequiredDate] AS [RequiredDate],
[Project2].[ShipAddress] AS [ShipAddress],
[Project2].[ShipCity] AS [ShipCity],
[Project2].[ShipCountry] AS [ShipCountry],
[Project2].[ShipName] AS [ShipName],
[Project2].[ShippedDate] AS [ShippedDate],
[Project2].[ShipPostalCode] AS [ShipPostalCode],
[Project2].[ShipRegion] AS [ShipRegion],
[Project2].[ShipVia] AS [ShipVia],
[Project2].[C1] AS [C1],
[Project2].[CustomerID] AS [CustomerID],
[Project2].[CompanyName] AS [CompanyName],
[Project2].[ContactName] AS [ContactName],
[Project2].[ContactTitle] AS [ContactTitle],
[Project2].[Address] AS [Address],
[Project2].[City] AS [City],
[Project2].[Region] AS [Region],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[Country] AS [Country],
[Project2].[Phone] AS [Phone],
[Project2].[Fax] AS [Fax],
[Project2].[CustType] AS [CustType],
[Project2].[C3] AS [C2],
[Project2].[C2] AS [C3],
[Project2].[OrderID1] AS [OrderID1],
[Project2].[ProductID] AS [ProductID],
[Project2].[UnitPrice] AS [UnitPrice],
[Project2].[Quantity] AS [Quantity],
[Project2].[Discount] AS [Discount]
FROM ( SELECT
    [Extent1].[EmployeeID] AS [EmployeeID],
    [Extent1].[Freight] AS [Freight],
    [Extent1].[OrderDate] AS [OrderDate],
    [Extent1].[OrderID] AS [OrderID],
    [Extent1].[RequiredDate] AS [RequiredDate],
    [Extent1].[ShipAddress] AS [ShipAddress],
    [Extent1].[ShipCity] AS [ShipCity],
    [Extent1].[ShipCountry] AS [ShipCountry],
    [Extent1].[ShipName] AS [ShipName],
    [Extent1].[ShippedDate] AS [ShippedDate],
    [Extent1].[ShipPostalCode] AS [ShipPostalCode],
    [Extent1].[ShipRegion] AS [ShipRegion],
    [Extent1].[ShipVia] AS [ShipVia],
    [Extent2].[Address] AS [Address],
    [Extent2].[City] AS [City],
    [Extent2].[CompanyName] AS [CompanyName],
    [Extent2].[ContactName] AS [ContactName],
    [Extent2].[ContactTitle] AS [ContactTitle],
    [Extent2].[Country] AS [Country],
    [Extent2].[CustomerID] AS [CustomerID],
    [Extent2].[CustType] AS [CustType],
    [Extent2].[Fax] AS [Fax],
    [Extent2].[Phone] AS [Phone],
    [Extent2].[PostalCode] AS [PostalCode],
    [Extent2].[Region] AS [Region],
    1 AS [C1],
    [Extent3].[Discount] AS [Discount],
    [Extent3].[OrderID] AS [OrderID1],
    [Extent3].[ProductID] AS [ProductID],
    [Extent3].[Quantity] AS [Quantity],
    [Extent3].[UnitPrice] AS [UnitPrice],
    CASE WHEN ([Extent3].[Discount] IS NULL)
        THEN CAST(NULL AS int) ELSE 1 END AS [C2],
    CASE WHEN ([Extent3].[Discount] IS NULL)
        THEN CAST(NULL AS int) ELSE 1 END AS [C3]
    FROM   [dbo].[Orders] AS [Extent1]
        LEFT OUTER JOIN
            [dbo].[Customers] AS [Extent2]
        ON
            [Extent1].[CustomerID] = [Extent2].[CustomerID]
        LEFT OUTER JOIN
            [dbo].[Order Details] AS [Extent3]
        ON
            [Extent1].[OrderID] = [Extent3].[OrderID]
    WHERE  NOT EXISTS
        (SELECT
            cast(1 as bit) AS [C1]
        FROM
            [dbo].[Order Details] AS [Extent4]
        LEFT OUTER JOIN
            [dbo].[Products] AS [Extent5]
        ON
            [Extent4].[ProductID] = [Extent5].[ProductID]
        WHERE
            ([Extent1].[OrderID] = [Extent4].[OrderID])
        AND
            (( NOT    (CASE WHEN ([Extent5].[Discontinued] IS NULL)
                    THEN CAST(NULL AS varchar(1))
                    ELSE CASE WHEN ([Extent5].[Discontinued] = 0)
                    THEN ‘4X’ ELSE ‘4X0X’ END END LIKE ‘4X0X%’))
               OR    (CASE WHEN (CASE WHEN ([Extent5].[Discontinued] IS NULL)
                    THEN CAST(NULL AS varchar(1))
                    ELSE CASE WHEN ([Extent5].[Discontinued] = 0)
                    THEN ‘4X’ ELSE ‘4X0X’ END END LIKE ‘4X0X%’)
                    THEN cast(1 as bit) WHEN ( NOT
                    (CASE WHEN ([Extent5].[Discontinued] IS NULL)
                    THEN CAST(NULL AS varchar(1))
                    ELSE CASE WHEN ([Extent5].[Discontinued] = 0)
                    THEN ‘4X’ ELSE ‘4X0X’ END END LIKE ‘4X0X%’))
                    THEN cast(0 as bit) END IS NULL))
    )
)  AS [Project2]
ORDER BY
    [Project2].[OrderID] ASC,
    [Project2].[CustomerID] ASC,
    [Project2].[C3] ASC

In the next test we demonstrate a more complex compound Specification that is composed of AND and OR logic expressions with some precedence thrown in for good measure.

[Test()]
public void CombinedOrSpecExample()
{
    Specification<Order> country_customer_spec =
        new Specification<Order>
            (o => o.Customer.Country == “Germany” ||
                o.Customer.Country == “Usa”);

    Specification<Order> german_customer_spec =
        new Specification<Order>
            (o => o.Customer.Country == “Germany”);

    Specification<Order> us_customer_spec =
        new Specification<Order>
            (o => o.Customer.Country == “Usa”);

    Specification<Order> orderid_spec =
        new Specification<Order>(o => o.OrderID != 10279);

    Specification<Order> ords_with_discontinued_prods =
        new Specification<Order>(o => o.Order_Details.All
            (od => od.Product is DiscontinuedProduct));

    var orSpec = ords_with_discontinued_prods &
        orderid_spec & (german_customer_spec | us_customer_spec);

    using (var ctx = new NorthwindEntities())
    {
        //note the eager loading here
        //and still further down we have
        //to lazy load the same data
        //as eager loading has not worked
        //as expected.
        var discontinued_products_on_order =
            (ObjectQuery<Order_Detail>)
            from o in ctx.Orders
                .Include(“Order_Details”)
                .Include(“Customer”)
            .Where(orSpec.EvalPredicate)
            from od in ctx.OrderDetails
                .Include(“Product”)
            where od.OrderID == o.OrderID
            select od;

        Console.WriteLine(discontinued_products_on_order.ToTraceString());

        //demonstrates the current short comings IMO of the
        //entity framework with respect to the eager loading
        //and lazy loading. As you can see the includes are
        //NOT doing what you might expect as we still have
        //to load them lazy below.
        foreach (var ord_detail in discontinued_products_on_order)
        {
            if (!ord_detail.ProductReference.IsLoaded)
            {
                ord_detail.ProductReference.Load();
            }
            if (!ord_detail.OrderReference.IsLoaded)
            {
                ord_detail.OrderReference.Load();
            }
            if (!ord_detail.Order.CustomerReference.IsLoaded)
            {
                ord_detail.Order.CustomerReference.Load();
            }
            Console.WriteLine(“The OrderID: {0} the product name: {1} “ +
                “the customers location {2}”,
                ord_detail.OrderID, ord_detail.Product.ProductName,
                ord_detail.Order.Customer.Country);
        }

        Assert.IsTrue(discontinued_products_on_order
                      .Count(p => p.Order.Customer.Country != “Germany” &&
                                  p.Order.Customer.Country != “USA”) == 0);
    }
}

Notice in the LINQ query above we are actually projecting from the join with OrderDetails and so the includes hints on the Orders entity set is being ignored by the Entity Framework. Here’s the TSQL being produced:

SELECT
    1 AS [C1],
    [Extent3].[OrderID] AS [OrderID],
    [Extent3].[ProductID] AS [ProductID],
    [Extent3].[UnitPrice] AS [UnitPrice],
    [Extent3].[Quantity] AS [Quantity],
    [Extent3].[Discount] AS [Discount]
FROM
    [dbo].[Orders] AS [Extent1]
LEFT OUTER JOIN
    [dbo].[Customers] AS [Extent2]
ON
    [Extent1].[CustomerID] = [Extent2].[CustomerID]
INNER JOIN
    [dbo].[Order Details] AS [Extent3]
ON
    [Extent1].[OrderID] = [Extent3].[OrderID]
WHERE
    (
    NOT EXISTS(
        SELECT
            cast(1 as bit) AS [C1]
        FROM
            [dbo].[Order Details] AS [Extent4]
        LEFT OUTER JOIN
            [dbo].[Products] AS [Extent5]
        ON
            [Extent4].[ProductID] = [Extent5].[ProductID]
        WHERE
            ([Extent1].[OrderID] = [Extent4].[OrderID])
        AND
            ((    NOT (CASE WHEN ([Extent5].[Discontinued] IS NULL)
                    THEN CAST(NULL AS varchar(1))
                    ELSE CASE WHEN ([Extent5].[Discontinued] = 0)
                    THEN ‘4X’ ELSE ‘4X0X’ END END LIKE ‘4X0X%’))
                OR (CASE WHEN (CASE WHEN ([Extent5].[Discontinued] IS NULL)
                    THEN CAST(NULL AS varchar(1))
                    ELSE CASE WHEN ([Extent5].[Discontinued] = 0)
                    THEN ‘4X’ ELSE ‘4X0X’ END END LIKE ‘4X0X%’)
                    THEN cast(1 as bit) WHEN ( NOT
                    (CASE WHEN ([Extent5].[Discontinued] IS NULL)
                    THEN CAST(NULL AS varchar(1)) ELSE
                    CASE WHEN ([Extent5].[Discontinued] = 0)
                    THEN ‘4X’ ELSE ‘4X0X’ END END LIKE ‘4X0X%’))
                    THEN cast(0 as bit) END IS NULL))
))
AND
    (10279 <> [Extent1].[OrderID])
AND
    ((N‘Germany’ = [Extent2].[Country])
OR
    (N‘Usa’ = [Extent2].[Country]))

Shawn Wildermuth recently had something to say about how calls to .Include() act more as ‘hints’ rather than a ‘guarantee’ in their behaviour when the Entity Framework employs eager loading. One thing this exercise is teaching me is that it’s an absolute non negotiable to ever assume the TSQL behaviours of the Entity Framework or my LINQ queries and constantly be checking what it’s producing at the database. This helping me retain my TSQL and SQL Profiler skills.

Finally the Repository

I don’t really want to include all the code as text here in the post, so it’s here for download, but here is a small example of what’s in there:

public class EntitiesRepository<E, C> where E : EntityObject
                                      where C : ObjectContext
{
    private readonly C _ctx;

    public C Session
    {
        get { return _ctx; }
    }

    public EntitiesRepository(C session)
    {
        _ctx = session;
    }

    public void Save()
    {
        _ctx.SaveChanges();
    }

    /// <summary>
    /// A generic method to return ALL the entities
    /// of type TEntity. This overload will use the
    /// the parameter entitySetName in the resolution of mapping
    /// between the CSDL and SSDL. This method is useful
    /// for Models that DO have their pluralized names
    /// changed by the developer. For example if Customers
    /// table from Northwind produces an Entity that by
    /// default is named Customers but has it’s name changed
    /// to Customer this method call would fail. In Version
    /// 2.0 this pluralizing behaviour will change and this
    /// method overload should be used only if Developers
    /// change EntitySet names from the default name generated.
    /// </summary>
    /// <param name=”entitySetName”>
    /// The EntitySet name of the entity in the model.
    /// </param>
    /// <typeparam name=”TEntity”>
    /// The Entity to load from the database.
    /// </typeparam>
    /// <returns>Returns a set of TEntity.</returns>
    public ObjectQuery<E> All(string entitySetName)
    {
        return (ObjectQuery<E>)_ctx.CreateQuery<E>(“[" + entitySetName + "]“);
    }

    /// <summary>
    /// A generic method to return ALL the entities
    /// of type TEntity. This overload will use the
    /// typeof(TEntity).Name in the resolution of mapping
    /// between the CSDL and SSDL. This method is useful
    /// for Models that DO NOT have their pluralized names
    /// changed by the developer. For example if Customers
    /// table from Northwind produces an Entity that by
    /// default is EntitySet name is NOT changed.
    /// </summary>
    /// <typeparam name=”TEntity”>
    /// The Entity to load from the database.
    /// </typeparam>
    /// <returns>Returns a set of TEntity.</returns>
    public ObjectQuery<E> All()
    {
        return (ObjectQuery<E>)_ctx.CreateQuery<E>(“[" + typeof(E).Name + "]“);
    }

    /// <summary>
    /// A generic method to return ALL the entities
    /// of type TEntity. This overload will use the
    /// the parameter entitySetName in the resolution of mapping
    /// between the CSDL and SSDL. This method is useful
    /// for Models that DO have their pluralized names
    /// changed by the developer. For example if Customers
    /// table from Northwind produces an Entity that by
    /// default is named Customers but has it’s name changed
    /// to Customer this method call would fail. In Version
    /// 2.0 this pluralizing behaviour will change and this
    /// method overload should be used only if Developers
    /// change EntitySet names from the default name generated.
    /// </summary>
    /// <param name=”entitySetName”>
    /// The EntitySet name of the entity in the model.
    /// </param>
    /// <typeparam name=”TEntity”>
    /// The Entity to load from the database.
    /// </typeparam>
    /// <returns>Returns a set of TEntity.</returns>
    public ObjectQuery<E> All(string entitySetName, ISpecification<E> where)
    {
        return (ObjectQuery<E>)
            _ctx.CreateQuery<E>(“[" + entitySetName + "]“)
            .Where(where.EvalPredicate);
    }

    /// <summary>
    /// A generic method to return ALL the entities
    /// of type TEntity. This overload will use the
    /// typeof(TEntity).Name in the resolution of mapping
    /// between the CSDL and SSDL. This method is useful
    /// for Models that DO NOT have their pluralized names
    /// changed by the developer. For example if Customers
    /// table from Northwind produces an Entity that by
    /// default is EntitySet name is NOT changed.
    /// </summary>
    /// <typeparam name=”TEntity”>
    /// The Entity to load from the database.
    /// </typeparam>
    /// <returns>Returns a set of TEntity.</returns>
    public ObjectQuery<E> All(ISpecification<E> where)
    {
        return (ObjectQuery<E>)
            _ctx.CreateQuery<E>(“[" + typeof(E).Name + "]“)
            .Where(where.EvalPredicate);
    }
}

The Repository does contain equivalent public methods for “First”, “Last” and “Unique”. The Unique generic methods are worth discussing as there is no actual implementation of .Single<T> for LINQ To Entities and if called will throw an exception. The recommended way of dealing with this is to use the .First() extension method, however I decided to incorporate a .Unique method in the repository so I have abstracted away a call to .First() and thrown a custom exception when their are duplicates in the query produced by calling Unique (which is the same behaviour as calling .Single<T> in LINQ To SQL). The following code and test demonstrates the one of four overloads of .Unique and it’s usage :

public E Unique(string entitySetName, ISpecification<E> where)
{
    if (_ctx.CreateQuery<E>(“[" + entitySetName + "]“).
        Where<E>(where.EvalPredicate).Count() > 1)
    {
        throw new NotUniqueEntityException();
    }
    return (E)
        _ctx.CreateQuery<E>(“[" + entitySetName + "]“)
        .Where(where.EvalPredicate).First();
}

The TEST:

[Test()]
[ExpectedException(typeof(NotUniqueEntityException))]
public void FailTestForUnique()
{
    //new up the repository
    EntitiesRepository<Customer, NorthwindEntities> ctx =
        new EntitiesRepository<Customer,
        NorthwindEntities>(new NorthwindEntities());

    //new up a specification
    Specification<Customer> cust_spec =
        new Specification<Customer>(c => c.Country == “Germany”);

    var unique_german_cust = ctx.Unique(“Customers”, cust_spec);

    Assert.IsNotNull(unique_german_cust);

    Console.WriteLine(“The Unique customer ID is : {0}”,
                        unique_german_cust.CustomerID);
}

The Custom Exception :

public class NotUniqueEntityException : Exception
{
    public NotUniqueEntityException() :
        base(“The entity query produced more than “ +
                “a single result and is not unique”) { }
}

Please remember that the code for download is not fully tested nor complete for production it’s provided here as an example.

Categories: Design Patterns, Entity Framework, LINQ To SQL, ORM Tags: Design Patterns, Entity Framework, LINQ To SQL, ORM

Comments (4) Trackbacks (3) Leave a comment Trackback

Marc Brooks

January 17th, 2009 at 14:40 | #1

Reply | Quote

Neato! Won’t your implementation of Unique actually make two round-trips to the database?

[Reply]
Simon Segal

January 17th, 2009 at 16:32 | #2

Reply | Quote

Yes unfortunately it will. I guess the question is what is going to cost more the query for all of records matching the specification followed by a count in memory or the round trip? The outcome of the query would determine that. Perhaps some E-SQL would be a better option.

[Reply]
Steve

June 5th, 2009 at 00:44 | #3

Reply | Quote

I’m having problems with your sample code - it doesn’t appear to have this code in it - I see Iron Ruby stuff in it ?

Is it the right link ?

Thanks!

[Reply]
Simon Segal

June 5th, 2009 at 19:51 | #4

Reply | Quote

Wow your quite right Steve. Must have been a mix up when I went to drop a later version that came after this post. I have put it in the subversion repo here

Simon

[Reply]