Entity Framework: the productivity way

Matthieu MEZIL — Tue, 20 Oct 2009 13:39:12 GMT

One of the best points with Entity Framework is the developer productivity gain. If you associate EF with the T4 template code generation, this gain explodes. Imagine that we want a WCF service which exposes some data. For each entity type, you probably want a Get method which returns the entities, perhaps another Get which takes the entity id as parameter and which returns the entity with its relationships, a Add, a Update, perhaps a Delete.

EF allows an important productivity gain for the entities development and their use. However, in our case, to code to write is almost the same for each entity type. It means that it’s time to use the T4 template.

In all T4 samples I studied, this great template is used only for the entity generation. We will try here to go ahead.

WIth the T4 template, you will write your meta-code which will generate the WCF service! And what is very cool is the fact that you will be able to use your template in another project. I try to compare here the productivity between a code using ADO .NET 2.0 and a code using the Entity Framework. My first observation is: I forgot that it’s so boring and so long to use the classic ADO.

With EF and T4, the only thing I don’t generate is the relation list to load per entity type (I could use a fixed depth with T4 but I preferred to have a business logic for the relationships loading). With ADO .NET 2.0, I have to write all the code!

Moreover, “classic” ADO implies a string query and we can imagine to have some writing mistakes in our SQL queries and so it implies to write some unit tests which take time.

One of the first point we can see is the code reading. It’s so much easier with EF!

For the GetOrder(orderId) method, my ADO 2.0 code is the following:

private const string SELECT_ORDERS = "OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM ORDERS";
private const string SELECT_ORDERDETAILS = "OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details]";
private const string SELECT_CUSTOMERS = "C.CustomerID, CompanyName, ContactName, ContactTitle, [Address], City, Region, PostalCode, Country, Phone, Fax, Since, Points, CardNumber, CAST((CASE M.CustomerID when NULL then 0 else 1 END) AS bit) AS IsMember FROM Customers AS C LEFT OUTER JOIN Members AS M ON C.CustomerID = M.CustomerID";
 
public Order GetOrder(int orderID)
{
    return ReadEntity<Order>(string.Format("SELECT TOP 1 {0} WHERE OrderID = @OrderID; \n SELECT {1} WHERE OrderID = @OrderID;", SELECT_ORDERS, SELECT_ORDERDETAILS), new[] { new SqlParameter("OrderID", orderID) }, reader => GetOrder(reader), (o, reader, connection) =>
    {
        o.OrderDetails = new List<OrderDetail>();
        reader.NextResult();
        while (reader.Read())
        {
            var orderDetail = GetOrderDetail(reader);
            orderDetail.Order = o;
            o.OrderDetails.Add(orderDetail);
        }
        if (o.CustomerID != null)
            o.Customer = ReadEntity<Customer>(string.Format("SELECT TOP 1 {0} WHERE C.CustomerID = @CustomerID", SELECT_CUSTOMERS), new[] { new SqlParameter("CustomerID", o.CustomerID) }, subReader => GetCustomer(subReader), connection);
        var pq = o.OrderDetails.Select(od => od.ProductID.ToString());
        if (pq.Any())
        {
            var products = ReadEntities<Product>(string.Format("SELECT {0} WHERE ProductID IN ({1})", SELECT_PRODUCTS, pq.Skip(1).Any() ? pq.Aggregate((p1, p2) => p1.Contains(string.Format(" {0} ", p2)) ? p1 : string.Concat(" ", p1, " , ", p2)) : pq.First()), subReader => GetProduct(subReader), connection);
            foreach (var p in products)
                p.OrderDetails = o.OrderDetails.Where(od => od.ProductID == p.ProductID).Select(od => { od.Product = p; return od; }).ToList();
        }
    });
}
 
private T ReadEntity<T>(string commandText, SqlParameter[] parameters, Func<SqlDataReader, T> getEntityFromReader, Action<T, SqlDataReader, SqlConnection> moreAction = null) where T : class
{
    SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindEntities"].ToString());
    connection.Open();
    try
    {
        return ReadEntity(commandText, parameters, getEntityFromReader, connection, moreAction);
    }
    finally
    {
        connection.Close();
    }
}
 
private T ReadEntity<T>(string commandText, SqlParameter[] parameters, Func<SqlDataReader, T> getEntityFromReader, SqlConnection connection, Action<T, SqlDataReader, SqlConnection> moreAction = null) where T : class
{
    SqlCommand command = connection.CreateCommand();
    command.CommandText = commandText;
    command.Parameters.AddRange(parameters);
    SqlDataReader reader = command.ExecuteReader();
    try
    {
        if (reader.Read())
        {
            var value = getEntityFromReader(reader);
            if (moreAction != null)
                moreAction(value, reader, connection);
            return value;
        }
        return null;
    }
    finally
    {
        reader.Close();
    }
}
 
private List<T> ReadEntities<T>(string commandText, Func<SqlDataReader, T> getEntityFromReader, SqlConnection connection) where T : class
{
    var value = new List<T>();
    SqlCommand command = connection.CreateCommand();
    command.CommandText = commandText;
    SqlDataReader reader = command.ExecuteReader();
    try
    {
        while (reader.Read())
            value.Add(getEntityFromReader(reader));
        return value;
    }
    finally
    {
        reader.Close();
    }
}
 
private Order GetOrder(SqlDataReader reader)
{
    return new Order { OrderID = reader.GetInt32(0), CustomerID = reader.IsDBNull(1) ? null : reader.GetString(1), EmployeeID = reader.IsDBNull(2) ? null : (int?)reader.GetInt32(2), OrderDate = reader.IsDBNull(3) ? null : (DateTime?)reader.GetDateTime(3), RequiredDate = reader.IsDBNull(4) ? null : (DateTime?)reader.GetDateTime(4), ShippedDate = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5), ShipVia = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6), Freight = reader.IsDBNull(7) ? null : (decimal?)reader.GetDecimal(7), ShipName = reader.IsDBNull(8) ? null : reader.GetString(8), ShipAddress = reader.IsDBNull(9) ? null : reader.GetString(9), ShipCity = reader.IsDBNull(10) ? null : reader.GetString(10), ShipRegion = reader.IsDBNull(11) ? null : reader.GetString(11), ShipPostalCode = reader.IsDBNull(12) ? null : reader.GetString(12), ShipCountry = reader.IsDBNull(13) ? null : reader.GetString(13) };
}
 
private OrderDetail GetOrderDetail(SqlDataReader reader)
{
    return new OrderDetail { OrderID = reader.GetInt32(0), ProductID = reader.GetInt32(1), UnitPrice = reader.GetDecimal(2), Quantity = reader.GetInt16(3), Discount = reader.GetFloat(4) };
}
 
private Customer GetCustomer(SqlDataReader reader)
{
    if (reader.GetBoolean(14))
        return GetMember(reader);
    return GetCustomer<Customer>(reader);
}
 
private T GetCustomer<T>(SqlDataReader reader) where T : Customer, new()
{
    return new T { CustomerID = reader.GetString(0), CompanyName = reader.GetString(1), ContactName = reader.IsDBNull(2) ? null : reader.GetString(2), ContactTitle = reader.IsDBNull(3) ? null : reader.GetString(3), Address = reader.IsDBNull(4) ? null : reader.GetString(4), City = reader.IsDBNull(5) ? null : reader.GetString(5), Region = reader.IsDBNull(6) ? null : reader.GetString(6), PostalCode = reader.IsDBNull(7) ? null : reader.GetString(7), Country = reader.IsDBNull(8) ? null : reader.GetString(8), Phone = reader.IsDBNull(9) ? null : reader.GetString(9), Fax = reader.IsDBNull(10) ? null : reader.GetString(10) };
}
 
private Member GetMember(SqlDataReader reader)
{
    var member = GetCustomer<Member>(reader);
    member.Since = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11);
    member.Points = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);
    member.CardNumber = reader.IsDBNull(13) ? null : reader.GetString(13);
    return member;
}
 
private Product GetProduct(SqlDataReader dataReader)
{
    return new Product { ProductID = dataReader.GetInt32(0), ProductName = dataReader.GetString(1), SupplierID = dataReader.IsDBNull(2) ? null : (int?)dataReader.GetInt32(2), CategoryID = dataReader.IsDBNull(3) ? null : (int?)dataReader.GetInt32(3), QuantityPerUnit = dataReader.IsDBNull(4) ? null : dataReader.GetString(4), UnitPrice = dataReader.IsDBNull(5) ? null : (decimal?)dataReader.GetDecimal(5), UnitsInStock = dataReader.IsDBNull(6) ? null : (short?)dataReader.GetInt16(6), UnitsOnOrder = dataReader.IsDBNull(7) ? null : (short?)dataReader.GetInt16(7), ReorderLevel = dataReader.IsDBNull(8) ? null : (short?)dataReader.GetInt16(8), Discontinued = dataReader.GetBoolean(9) };
}

Crazy!

With EF, this is simply the following:

//Generated file
partial class NorthwindService
{
    private static Func<NorthwindEntities, System.Int32, Order> GetOrderCQ = CompiledQuery.Compile<NorthwindEntities, System.Int32, Order>((context, OrderID) => context.Orders.OfType<Order>().FirstOrDefault(e => e.OrderID == OrderID));
 
    private static Func<NorthwindEntities, System.Int32, Order> GetOrderWithIncludeCQ { get; set; }
 
    public Order GetOrder(System.Int32 OrderID)
    {
        using (var context = new NorthwindEntities())
        {
            if (GetOrderWithIncludeCQ != null)
                return GetOrderWithIncludeCQ(context, OrderID);
            return GetOrderCQ(context, OrderID);
        }
    }
}
 
//My partial part
partial class NorthwindService
{
    static NorthwindService()
    {
        GetOrderWithIncludeCQ = (context, orderID) => context.Orders.Include("OrderDetails.Product").Include("Customer").FirstOrDefault(o => o.OrderID == orderID);
    }
}

Moreover, as explained previously, with EF I write only one line of code:

GetOrderWithIncludeCQ = (context, orderID) => context.Orders.Include("OrderDetails.Product").Include("Customer").FirstOrDefault(o => o.OrderID == orderID);

What a productivity gain!

This is the dev time function of the number of entities:

Nb entités / temps de dev	1	2	3	4	5	6	7	8	9	10	11	12	13	14	100	500	1000
EF	3	3	4	4	4	4	5	5	5	5	6	6	6	6	28	128	253
ADO .NET 2.0	35	65	95	125	155	185	215	245	275	305	335	365	395	425	3005	15005	30005

So for 1000 entities, with ADO 2.0, it takes more than 66 days (7.5 h per day) against only 4 hours for Entity Framework. Ok I’m not honnest. If we have 1000 entities, it’s better to split our model into some models. SO we will say 2 days for EF against 2.5 months for ADO 2.0. Great isn’t it?

Moreover, the EF code is easier to read, there is not a lot of reason to keep on using ADO .NET 2.0.

I just want to tell one last point: it’s very important to have a training on EF (for this you can contact me (matthieu.mezil at live.fr)). This is very important because even if EF seems very easy to use, there are important concepts you have to know:

not to lose a lot of time
to get the result you wished (particularly with the non persisted entities)
not to have bad performance

You can download the tt I wrote here.

French ALT .NET Demo

Matthieu MEZIL — Tue, 28 Apr 2009 21:13:00 GMT

Last week, I spoke at an EF session for the french ALT .NET community.

During its course, I made a WCF demo with EF POCO and a sort of LINQ provider in the client part.

This provider is very simplistic (made in less than 15 minutes), isn't thread safe but is interesting for the POC and because if my provider doesn't support a LINQ method, it will use LINQ To Object method.

I just want to come back on this provider, which is very different than normal LINQ providers, and particularly on this code:

public static class CategoryClient
{
    public static MyQueryable<Category> Categories
    {
        get
        {
            ClientLINQ.AllEntities = true;
            return GetCategories().ToMyQueryable();
        }
    }
 
    private static IEnumerable<Category> GetCategories()
    {
        List<Category> value;
        if (ClientLINQ.AllEntities)
            value = Context.Instance.CategoryContract.GetAllCategories();
        else
            value = Context.Instance.CategoryContract.GetCategories(ClientLINQ.IncludeValues, ClientLINQ.WhereValue, ClientLINQ.OrderByValue, ClientLINQ.SkipValue, ClientLINQ.TakeValue);
        foreach (var category in value)
            yield return category;
    }
}

How does it work? The set of AllEntities to true initializes IncludeValues, WhereValues, etc

IncludeValues = null;
WhereValue = null;
OrderByValue = null;
SkipValue = 0;
TakeValue = 0;

How can AllEntities be false in GetCategories method whereas the instruction which follows AllEntities = true is the GetCategories call and this method starts with a test on AllEntities ?

In fact, you should see that this method uses yield return syntax. So, it will be run only when Categories properties will be iterated. This means that the different LINQ methods are called before. That's why it's works.

Matthieu MEZIL : WCF

Entity Framework: the productivity way

EF with WCF recent demos

French ALT .NET Demo