Entity Framework: the productivity way

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.

This entry was posted in 10369, 10511, 7671, 7674, 7675. Bookmark the permalink.

2 Responses to Entity Framework: the productivity way

  1. Steve says:

    I’ve been doing PHP development for a while, but I’m interested in doing Silverlight DEV using WCF. I understand and can use EF well, but I see the need to generate WCF services from an EF model class.

    How would I go about using your T4 template to consume an EF model to generate a WCF service?

    Thanks! I hope you can help me save some time too!

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>