Archive

Archive for the ‘Entity Framework’ Category

Tips to improve Entity Framework Performance

March 12, 2013 14 comments

LINQ to Entity is a great ORM for querying and managing database. It offers a lot of things, so it is mandatory to know about performance of it. These are right up to a certain point as LINQ comes with its own penalties. There are some tips and tricks that we should keep in mind while designing and query database using entity framework ORM. Here is a list of some tips that I would like to share with you.

  1. Avoid to put all the DB Objects into One Single Entity Model

    Entity Model specifies a single unit of work, not all our database. If we have many database objects that are not connected to one another or these(log tables, objects used by batch processes etc.) are not used at all. Hence these objects are consuming space in the memory and cause performance degrades. So try to make separate entity models of related database objects.

  2. Disable change tracking for entity if not needed

    Whenever you retrieve the data only for reading purpose, not for modification then there is no need of object tracking. So disable object tracking by using MergeOption as below:

    NorthwindDataContext context = new NorthwindDataContext();

    context.tblCities.MergeOption = MergeOption.NoTracking;

    This option allows us to turn off the object cache and unnecessary identity management of the objects.

  3. Use Pre-Generating Views to reduce response time for first request

    When the object of ObjectContext is created first time in the application, the entity framework creates a set of classes that is required to access the database. This set of classes is called view and if your data model is large then creating the view may delay the web application response to the first request for a page. We can reduce this response time by creating view at compile time by using T4 template or EdmGen.exe command-line tool.

  4. Avoid fetching all the fields if not required

    Avoid fetching not required fields from the database. Suppose I have table of Customer with 20 fields and I am interested only in three fields – CustomerID, Name, Address then fetch only these three fields instead of fetching all the fields of the Customer table.

    //Bad Practice

    var customer =

    (from cust in dataContext.Customers

    select cust).ToList();

    //Good Practice

    var customerLite =

    (from cust in dataContext.Customers

    select new {

    customer. CustomerID,

    customer.Name,

    customer.Address

    }). ToList ();

  5. Choose appropriate Collection for data manipulation

    In linq we have Var, IEnumerable, IQueryable, IList type collection for data manipulation. Each collection has its importance and performance impact on the query, so beware of using all these collection for data manipulation.

  6. Use Compiled Query wherever needed

    Make a query to compiled query if it is frequently used to fetch records from the database. This query is slow in first time but after that it boost the performance significantly. We use Compile method of CompiledQuery class for making compiled query.

    Suppose you required to retrieve customers details again and again based on city then make this query to compiled query like as

    // create the entity object

    NorthwindEntities mobjentity = new NorthwindEntities();

    //Simple Query

    IQueryable lstCus = from customer in mobjentity.tblCustomers

    where customer.City == “Delhi”

    select customer;

    //Compiled Query

    Func> compiledQuery

    = CompiledQuery.Compile>(

    (ctx, city) =>from customer in ctx.Customers

    where customer.City == city

    select customer);

    In above query we are passing the string parameter city for filtering the records.

  7. Retrieve only required number of records

    When we are binding data to grid or doing paging, retrieve only required no of records to improve performance. This can achieved by using Take,While and Skip methods.

    // create the entity object

    NorthwindEntities mobjentity = new NorthwindEntities();

    int pageSize=10,startingPageIndex=2;

    List lstCus = mobjentity.tblCustomers.Take(pageSize)

    .Skip(startingPageIndex * pageSize)

    .ToList();

  8. Avoid using Contains

    In LINQ, we use contains method for checking existence. It is converted to “WHERE IN” in SQL which cause performance degrades.

  9. Avoid using Views

    Views degrade the LINQ query performance costly. These are slow in performance and impact the performance greatly. So avoid using views in LINQ to Entities.

  10. Debug and Optimize LINQ Query

    If you want to debug and optimize your query then LINQ Pad is a great tool for this purpose. I am a big fan of LINQ Pad. It is very useful for query construction, debugging and optimization.

    IQueryable lstCus = from customer in mobjentity.tblCustomers

    where customer.City == “Delhi”

    select customer;

    lstCus.Dump();

    Dump method of LINQ Pad give the result of above query in the result window.


Entity Framework: The version of SQL Server in use does not support datatype ‘datetime2′

August 8, 2012 Leave a comment

I development against a 2008 copy of SQL Server, which normally isn’t a problem, until….. I pushed out my latest code to the production system, which runs SQL 2005, and started smoke testing the system. As soon as I tried to write a record, i got the data type exception. Its painfully in the innerexception.

So, I think to myself, I am not doing anything amazing here. I am just trying to use the Entity Framework to write simple data. Well, apparently there is a tag in the store model saying what version of SQL you are targeting. Is there an easy property window for this? Nope. Open the XML.

If you change the 2008 out the server version you are targeting. 2005 or 2000 if your old, and voila. Up and running. I guess I need to upgrade the production servers to 2008. What a pain.

Right Click your EDMX file and open with XML Editor. Change ProviderManifestToken=2008 to ProviderManifestToken=2005

Then change the following:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
 <!-- EF Runtime content -->
 <edmx:Runtime>
 <!-- SSDL content -->
 <edmx:StorageModels>
 <Schema Namespace="MyDataModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005"
       xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"
       xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
 <EntityContainer Name="MyDBModelStoreContainer">

NOTE : If you still want to use the same ProviderManifestToken=2008 than you 
can also resolve this problem by simply handle the datetime property to Nullable
datatime (DateTime?)

Hope this would be helpful!
Categories: Entity Framework

Using Repository and Unit of Work patterns with Entity Framework 4.0

November 11, 2011 1 comment

In this post, I’d like to look at how we might be able to take our example a bit further and use some of the common patterns such asRepository and Unit Of work so that we can implement persistence specific concerns in our example.

Expanding on our Northwind based example a bit further, let’s say I am interested in supporting the following Customer entity oriented operations:

  • Query for a customer by ID
  • Searching for Customer by Name
  • Adding a new Customer to the database

I also want to be able to query for a product based on ID.

Finally, given a customer, I would like to be able to add an Order to the database

Before we get into the details, there are two things I’d like to get out of the way first:

  • There is more than one “correct” way to approach this problem. I’ll likely be making many simplifying assumptions as I go – the objective is to show a very high level sketch of how you might implement the two patterns to solve the problem at hand.
  • Normally, when using TDD, I’d start out with tests and use my tests to evolve my design incrementally. Since I’m not following TDD in this example, please bear with me if you see me doing things like defining an interface upfront, instead of letting tests and commonalities dictate the need for an interface, etc.

mplementing the Repository

Let’s start with the work we have to do on Customer entity and look at what a repository for dealing with Customer might look like:

public interface ICustomerRepository
{        
    Customer GetCustomerById(string id);
    IEnumerable<Customer> FindByName(string name);
    void AddCustomer(Customer customer);
}

This repository interface seems to meet all the requirements around Customer:

  • GetCustomerById should allow me to get a single customer entity by primary key
  • FindByName should allow me to search for customers
  • AddCustomer should allow me to add customer to the database

This sounds good to me for the moment. Defining an interface like this for your repository is a good idea, especially if you are interested in writing tests using mocks or fakes and it allows for better unit testing by keeping your database out of the equation entirely. There are blog posts coming in the future that cover testability, mocks and fakes, etc.

You might take this interface definition a bit further and define a common IRepository for dealing with concerns that are common for multiple repository types. This is fine thing to do if you see that it works for you. I don’t necessarily see the need yet for this particular example and so I’ll pass on it for now. It is entirely possible that this becomes important as you add more repositories and refactor.

Let’s take this repository and see how we might build an implementation of it that leverages Entity Framework to enable data access.

First of all, I need an ObjectContext that I can use to query for data. You might be tempted to handle ObjectContext instantiation as a part of the repository’s constructor – but it is a good idea to leave that concern out of the repository and deal with that elsewhere.

Here’s my constructor:

public CustomerRepository(NorthwindContext context)
{
    if (context == null)
        throw new ArgumentNullException("context");

   _context = context;
}

In the above snippet, NorthwindContext is my typed ObjectContext type.

Let’s now provide implementations for the methods required by our ICustomerRepository interface.

GetCustomerById is trivial to implement, thanks to LINQ. Using standard LINQ operators, we can implement GetCustomerById like this:

public Customer GetCustomerById(string id)
{
    return _context.Customers.Where(c => c.CustomerID == id).Single();
}

Similarly, FindByName could look like this. Once again, LINQ support makes this trivial to implement:

public IEnumerable<Customer> FindByName(string name)
{
    return _context.Customers.Where( c => c.ContactName.StartsWith(name)
                                   ).ToList();                        
}

Note that I chose to expose the results as IEnumerable<T> – you might choose to expose this as an IQueryable<T> instead. There are implications to doing this – in this case, I am not interested in exposing additional IQueryable based query composition over what I return from my repository.

And finally, let’s see how we might implement AddCustomer:

public void AddCustomer(Customer customer)
{
    _context.Customers.AddObject(customer);
}

You may be tempted to also implement the save functionality as a part of the AddCustomer method. While that may work for this simple example, it is generally a bad idea – this is exactly where the Unit of Work comes in and we’ll see in a bit how we can use the this pattern to allow us to implement and coordinate Save behavior.

Here’s the complete implementation of CustomerRepository that uses Entity Framework for handling persistence:

public class CustomerRepository : ICustomerRepository
{
    private NorthwindContext _context;

    public CustomerRepository(NorthwindContext context)
    {
        if (context == null)
            throw new ArgumentNullException("context");

         _context = context;
    }

    public Customer GetCustomerById(string id)
    {
        return _context.Customers.Where(c => c.CustomerID == id).Single();
    }

    public IEnumerable<Customer> FindByName(string name)
    {
        return _context.Customers.Where(c => c.ContactName.StartsWith(name))
            .AsEnumerable<Customer>();                        
    }

    public void AddCustomer(Customercustomer)
    {
        _context.Customers.AddObject(customer);
    }
}
Here’s how we might use the repository from client code:
CustomerRepository repository = new CustomerRepository(context);
Customer c = new Customer( ... );
repository.AddCustomer(c);
context.SaveChanges();

For dealing with my Product and Order related requirements, I could define the following interfaces (and build implementations much like CustomerRepository). I’ll leave the details out of this post for brevity.

public interface IProductRepository
{
    Product GetProductById(int id);
}

public interface IOrderRepository
{
    void AddOrder(Order order);
}

Implementing Unit of Work Pattern using ObjectContext

You may have noticed this already; even though we didn’t implement any specific pattern to explicitly allow us to group related operations into a unit of work, we are already getting Unit of Work functionality for free with NorthwindContext (our typed ObjectContext).

The idea is that I can use the Unit of Work to group a set of related operations – the Unit of Work keeps track of the changes that I am interested in until I am ready to save them to the database. Eventually, when I am ready to save, I can do that.

I can define an interface like this to define a “Unit of Work”:

 

public interface IUnitOfWork
{        
    void Save();
}

Note that with a Unit of Work, you might also choose to implement Undo / Rollback functionality. When using Entity Framework, the recommended approach to undo is to discard your context with the changes you are interested in undoing.

I already mentioned that our typed ObjectContext (NorthwindContext) supports the Unit of Work pattern for the most part. In order to make things a bit more explicit based on the contract I just defined, I can change my NorthwindContext class to implement the IUnitOfWork interface:

public class NorthwindContext : ObjectContext, IUnitOfWork
{   
    public void Save()
    {
        SaveChanges();
    }

. . .

I have to make a small adjustment to our repository implementation after this change:

public class CustomerRepository : ICustomerRepository
{
    private NorthwindContext _context;

    public CustomerRepository(IUnitOfWork unitOfWork)
    {
        if (unitOfWork == null)
            throw new ArgumentNullException("unitOfWork");

        _context = unitOfWork as NorthwindContext;
    }

    public Customer GetCustomerById(string id)
    {
        return _context.Customers.Where(c => c.CustomerID == id).Single();
    }

    public IEnumerable<Customer> FindByName(string name)
    {
        return _context.Customers.Where(c => c.ContactName.StartsWith(name))
            .AsEnumerable<Customer>();
    }

    public void AddCustomer(Customer customer)
    {
        _context.Customers.AddObject(customer);
    }
}
That’s it – we now have our IUnitOfWork friendly repository, and you can use the IUnitOfWork based context to even coordinate work across multiple repositories. Here’s an example of adding an order to the database that requires the work of multiple repositories for querying data, and ultimately saving rows back to the database:
IUnitOfWork unitOfWork = new NorthwindContext();

CustomerRepository customerRepository = new CustomerRepository(unitOfWork);
Customer customer = customerRepository.GetCustomerById("ALFKI");

ProductRepository productRepository = new ProductRepository(unitOfWork);
Product product = productRepository.GetById(1);

OrderRepository orderRepository = new OrderRepository(unitOfWork);

Order order = new Order(customer); 
order.AddNewOrderDetail(product, 1);
            
orderRepository.AddOrder(order);

unitOfWork.Save();

 

Categories: Entity Framework