October 2009 - Posts

Generate a WCF service from an edmx with T4 v2

I recently published a T4 template to generate a WCF service. I updated it to be able to load its relationships from an entity.

When the template is written (what I’ve already done), it generates the WCF service for us (you just have to set the end point in the config file).

As I explained in my previous post, the great point with it is the fact that the code writing time is not dependant of the number of entities on the model. Moreover,  the template is generally (it’s the case here) not dependant of the model so we can use it with another model and more generally with another project.

dynamic and indexer

Remember one year ago, I wrote a post on how to use reflection with dynamic keyword. At this moment, I sent an email to MS to add an indexer in dynamic. I don’t know if my email changed any thing but I am very happy to see that from .NET 4.0 Beta 2, we are now able to use it.

So in my sample, instead of

result = dynamicSpBase.get_Item(name);

I can now directly write this:

result = dynamicSpBase[name];

Great! Smile

Posted by Matthieu MEZIL | with no comments
Filed under: ,

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.

EF: Include with where clause

Several guys ask me if it’s possible to add a condition in the Include method and the answer is no.

However you can do an equivalent like this:

from cWithP in

    (from c in context.Categories

     select new

     {

         Category = c,

         Products = from p in c.Products

                    where p.UnitPrice > 20

                    select p

     }).AsEnumerable()

select cWithP.Category;

or the condensed equivalent:

context.Categories.Select(c => new { Category = c, Products = c.Products.Where(p => p.UnitPrice > 20) }).AsEnumerable().Select(cp => cp.Category);