Why using Entity Framework?

In this post, we will use a sample and we will study its implementation with DataReader / LINQ To SQL / Entity Framework.

In this sample, we will use the following database :

image_thumb1

The method I want to code has to return the best 10 members (order by amount spent) with Points, CompanyName, ContactName properties and the Amount.

For this, I use a MemberInfo class:

public class MemberInfo
{
   
public string CustomerId { get; set
; }
   
public string CompanyName { get; set
; }
   
public string ContactName { get; set
; }
   
public int Points { get; set
; }
   
public double Amount { get; set; }
}
 

DataReader


This is the first implementation using DataReader.

public static List<MemberInfo> GetMembers()
{
    List<MemberInfo> value = new List<MemberInfo>();
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("SELECT TOP 10 MI.CustomerID, MI.CompanyName, MI.ContactName, MI.Points, COALESCE(MI.Amount,0) AS Amount FROM ( " +
            "SELECT C.CustomerID, C.CompanyName, C.ContactName, M.Points, ( SELECT SUM(OD.UnitPrice * OD.Quantity * (1 - OD.Discount)) FROM Orders O " +
            "INNER JOIN OrderDetails OD ON OD.OrderID = O.OrderID WHERE O.CustomerID = C.CustomerID) AS Amount FROM Customers C " +
            "INNER JOIN Members M on M.CustomerID = C.CustomerID ) MI ORDER BY MI.Amount DESC", connection))
        {
            using (SqlDataReader dataReader = command.ExecuteReader())
            {
                while (dataReader.Read())
                    value.Add(new MemberInfo { CustomerId = dataReader.GetString(0), CompanyName = dataReader.GetString(1),
                        ContactName = dataReader.IsDBNull(2) ? null : dataReader.GetString(2), Points = dataReader.GetInt32(3), Amount = dataReader.GetDouble(4) });
            }
        }
    } return value;
}



 


First this code is boring to write because we have to manage ourselves Connection, Command and DataReader.  



Moreover, the query in string is not very good: hard to read, with some potential repo that you will see only during execution.



Finally,our code works only with SQL Server.



LINQ To SQL




We will now code the same query with LINQ To SQL.



image_thumb4



With LINQ To SQL, the code is the following:



public static List<MemberInfo> GetMembers()
{
    
using (NorthwindDataContext context = new NorthwindDataContext
())
     {
        
return (from m in
context.Members
                
let
c = m.Customer
                
let amount = (double?)c.Orders.SelectMany(o => o.OrderDetails).Sum(od => (double)od.Quantity * (double
)od.UnitPrice * (1 - od.Discount))
                
orderby amount descending                  select new MemberInfo { CustomerId = c.CustomerID, CompanyName = c.CompanyName, ContactName = c.ContactName, Points = m.Points, Amount = amount ??
0 }).Take(10)
                .ToList();
     }
}


 


Look at previous points:



This code is less boreingto write than with DataReader: we don’t have to manage Connection, Command and DataReader.



Moreover, using a LINQ query (LINQ To SQL here), we have intellisense and navigation properties which improve our productivity. We don’t need to know C# (or VB .NET) and T-SQL, C# or VB.Net is enough. Moreover, the syntax color improve the query readability. Finally, the query is compiled which remove repo risks visible only during execution. If we make a repo, our code won’t compile.



However, our code still only works with SQL Server.



Entity Framework




Contrary to LINQ To SQL, Entity Framework has a real mapping model which allows us to have a entities conception different than the DB conception. This is very important. Indeed, entity conception is an object conception (for C# or VB.NET for example) whe, the DB conception is a relational one. These two conceptions constraints are not the same (a very easy sample is many to many relationships which does not exist in a relational conception forcing to use a third table). So best conceptions for entities and DB can be relatively different which sometimes impose a complex mapping between both. With Entity Framework the mapping complexity is managed by the Framework.



In our sample, we only work on Members so it is not good to have Member and Customer entity types.



So, we will use the following Entity Data Model:



image_thumb6



This allows us to have CustomerID, CompanyName, ContactName and Points properties directly on the same entity type: Member.



So this also simplifies LINQ query (LINQ To Entities for Entity Framework) :



public static List<MemberInfo> GetMembers()
{
    
using (NorthwindEntities context = new NorthwindEntities
())
     {
        
return (from m in
context.Members
                
let amount = (double?)m.Orders.SelectMany(o => o.OrderDetails).Sum(od => (double)od.Quantity * (double
)od.UnitPrice * (1 - od.Discount))
                
orderby amount descending
                 select new MemberInfo { CustomerId = m.CustomerID, CompanyName = m.CompanyName, ContactName = m.ContactName, Points = m.Points, Amount = amount ??
0 }).Take(10)


                .ToList();
     }
}


 


Moreover, Entity Framework is not limited to SQL Server. So we fix all issues we have with first version (using DataReader)



Conlusion











LINQ allows us:



·         to improve our productivity and the query readability



·         to abstract from SQL  using our query directly on entities











Entity Framework allows us:



·         To abstract from DB provider. It is possible to change from an Oracle DB To a SQL Server DB without changing anything in our code!



·         To abstract from DB structure for entity types conception in order to define the best conception for our application without weigh considering relational DB constraints.





Note that 3 T-SQL queries have the same execution plan.

 





If you want one more argument, I would add that that Entity Framework really is, in my opinion, THE data access techno in Microsoft strategy. You can look at WCF RIA Services or WCF Data Services to convince yourself. These technos can be used without Entity Framework but the productivity gain when we couple them with Entity Framework is really huge.



Hope that helps

This entry was posted in 7671, 7674, 7676, 9104. Bookmark the permalink.

One Response to Why using Entity Framework?

  1. rmar says:

    “This code is less boreingto write”… quote of the day.. thank you

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>