Share via

Entity Framework: Disconnected Entities

  • Article


 

Introduction

When access data in a conventional Windows Forms solution using TableAdapter, DataAdapter or a connection and command objects form a managed data provider, a connection opens; the project interacts with database tables. When the operation is completed, the connection is closed or with on read operations data is contained in a data container such as a DataTable or a list by iterating data using a DataReader. This article will explore what differences there are between conventional methods and Entity Framework.

Disconnected Entities

A DbContext instance will automatically track entities returned from the database. Changes made to these entities are detected when SaveChanges or SaveChangesAsync is called and the database will be updated as needed. However, sometimes entities are queried using one context instance and then saved using a different instance. 

What this means in a Windows Forms project is a “using statement” is used to read data into a container perhaps in Form Load or Shown event and once the event is done the DbContext closes the connection and will be disposed. Attempting to save changes in something like a button click event to another DbContext without first adding the entity to the new context means there is no tracking of the disconnected entity. The proper methods are to use the following for adding a new entity to the new DbContext.

public void  AddProduct1() 
{
    var product = new  Product()
    {
        ProductName = "Headphones",
        CategoryID = 1,
        UnitPrice = 17.99M
    };
 
    using (var context = new NorthWindContext())
    {
        context.Entry(product).State = EntityState.Added;
    }
}

Alternate to setting the state is using the Add method; in this case, the state will be added.

public void  AddProduct3()
{
    var product = new  Product()
    {
        ProductName = "Headphones",
        CategoryID = 1,
        UnitPrice = 17.99M
    };
 
    using (var context = new NorthWindContext())
    {
        context.Products.Add(product);
    }
}

Another method to add a disconnected entity is by using the Attach method where its state is unchanged.

public void  AddProduct2()
{
    var product = new  Product()
    {
        ProductName = "Headphones",
        CategoryID = 1,
        UnitPrice = 17.99M
    };
 
    using (var context = new NorthWindContext())
    {
        context.Products.Attach(product);
    }
}

Similarly, for a modified entity the state.

public void  SaveProduct(Product product, bool discontinued)
{
    using (var context = new NorthWindContext())
    {
        product.Discontinued = discontinued;
        context.Entry(product).State = EntityState.Modified;
        context.SaveChanges();
    }
}

For a delete operation EntityState will be Deleted. The Entry method attaches an entire entity graph to a context with the specified state to a parent entity.

public void  DeleteProduct(Product product)
{
    using (var context = new NorthWindContext())
    {
        context.Entry(product).State = EntityState.Deleted;
        context.SaveChanges();
    }
}

In some cases, a developer may like to have one method for add and modified. In the following code sample product is a disconnected entity so the context is un-aware of the entity state. If the identifier, ProductId is 0 then mark it as a new record, otherwise there is a current primary key which means this is an update so mark the entity as modified.

public void  SaveProduct(Product product, bool discontinued)
{
    using (var context = new NorthWindContext())
    {
        product.Discontinued = discontinued;
        context.Entry(product).State = EntityState.Modified;
        context.SaveChanges();
    }
}

Deferred execution

In an Entity Framework query that returns a sequence of values, the query variable itself never holds the query results and only stores the query commands. Execution of the query is deferred until the query variable is iterated over in a foreach or For Each loop. This is known as deferred execution; that is, query execution occurs some time after the query is constructed.

Consider the following where a foreach, for next or .ToList has been called against the query and is called in either a button click, form load event or from a button click event to a data class.

public IQueryable GetProductsIQueryable1()
{
    using (var context = new NorthWindContext())
    {
        return from product in context.Products select product;
    }
}

In this case, the above code is called from a button click event.

var ops = new  DataOperations();
List<List<Product>> results = ops.GetProductsIQueryable1()
  .Cast<List<Product>>()
  .ToList();

The following runtime error is raised.

The operation cannot be completed because the DbContext has been disposed.

This is because after executing the query above it’s gone as the DbContext has been disposed. While both of the following methods will work.

public List<Product> GetProductsAsList() 
{
    using (var context = new NorthWindContext())
    {
        context.Configuration.LazyLoadingEnabled = false;
        return (from product in context.Products select product).ToList();
    }
}
 
 
public IQueryable<Product> GetProductsIQueryable2() 
{
    return from product in _context.Products select product;
}

In the last example the DbContext has been declared as a protected class level variable and in the button click event the caller used .ToList.

private void  GetProductsIQueryableWithoutExceptionButton_Click(object sender, EventArgs e)
{
    var ops = new  DeferredExecution();
 
    List<Product> results = ops.GetProductsIQueryable2()
        .ToList();
}

Note that when using code has displayed in the last example there are penalties, data is cached for the lifetime that the DbContext is there until disposed so only use this method when it makes sense. In a small to moderate size Windows Form application this method is fine but not for a web based application, in a web application create a DbContext for each operation. 

Immediate Query Execution

In contrast to the deferred execution of queries that produce a sequence of values, queries that return a singleton value are executed immediately. Some examples of singleton queries are Average, Count, FirstOrDefault, and Max. These execute immediately because the query must produce a sequence to calculate the singleton result. You can also force immediate execution.

Here is a mindset change, in conventional Windows Forms application when a count is needed against data a developer might want to know how many records there are in a table and will populate a DataTable to find out. Granted this is not the proper method to ask for a count, instead, a SELECT COUNT would be used yet some don’t comprehend that data is being returned and in larger applications can slow things down. Something can happen in Entity Framework. The trick is to not return data yet there seems to be no controlling the query so in these cases we use AsNoTracking.

public int  CountProducts()
{
    using (var context = new NorthWindContext())
    {
        return context.Products.AsNoTracking().Count();
    }
}

On the web a great many developers squawk over poor performance with counting records in Entity Framework and in many cases in for loops over large amount of data. If this is being done without any need to change data the following will improve performance but will go unnoticed on single counts.

public int  CountProducts() 
{
    using (var context = new NorthWindContext())
    {
        context.Configuration.AutoDetectChangesEnabled = false;
        return context.Products.AsNoTracking().Count();
    }
}

For more on AutoDetectChangesEnabled see the following Microsoft documentation.

Going deeper

Image a case where the task is to get discounts for orders by a specific customer and where the current discount is a certain value change it as we like this customer for all their business. 

In the following method orders table, joined to order details by order primary key OrderID, the inner where condition filters down to a specific customer, which has multiple detail records. Since discount is at order details level a language extension method gives us one order detail rather than many for a single order. This is followed by filtering to orders with a specific discount. All operations are done server side which can lead to slow execution.

public void  ApplyDiscount1(int  pCustomerIdentifier, float pCurrentDiscount, float pNewDiscount)
{
    using (var context = new NorthWindContext())
    {
        var orderDetailsResults = (from order in  context.Orders
            join orderDetail in  context.Order_Details on order.OrderID equals orderDetail.OrderID
            where order.CustomerIdentifier == pCustomerIdentifier
            select orderDetail)
            .DistinctBy(details => details.OrderID)
            .Where(details => details.Discount == pCurrentDiscount)
            .ToList();
 
        foreach (var item in orderDetailsResults)
        {
            item.Discount = pNewDiscount;
            context.Entry(item).State = EntityState.Modified;
        }
 
        Console.WriteLine(context.SaveChanges());
    }
}

This can be improved by performing only the where clause on a specific customer along with setting AutoDetechChangesEnabled to false and to prevent child entities to be loaded set LazyLoadingEnabled to false, include AsNoTracking.

public void  ApplyDiscount2(int  pCustomerIdentifier, float pCurrentDiscount, float pNewDiscount)
{
    using (var context = new NorthWindContext())
    {
        context.Configuration.AutoDetectChangesEnabled = false;
        context.Configuration.LazyLoadingEnabled = false;
 
        var orderDetailsResults = (from order in  context.Orders
            join orderDetail in  context.Order_Details on order.OrderID equals orderDetail.OrderID
            where order.CustomerIdentifier == pCustomerIdentifier
            select orderDetail).AsNoTracking().ToList();
 
        orderDetailsResults = orderDetailsResults
            .DistinctBy(details => details.OrderID)
            .Where(details => details.Discount == pCurrentDiscount)
            .ToList();
 
        foreach (var item in orderDetailsResults) 
        {
            item.Discount = pNewDiscount;
            context.Entry(item).State = EntityState.Modified;
        }
 
        Console.WriteLine(context.SaveChanges());
    }
}

Note rather than one large query executed at once, the example above breaks down the one query into two. Therefore, the first query executes processing fewer records, which in turn means the second query is processing fewer records. In regards to updating the Discount since AsNoTracking is used we must tell the DbContext there are changes, otherwise nothing is saved.

Taking a side track, there are basically two trains of thought on getting distinct values, using a HashSet (which DistinctBy uses) or using GroupBy. The following demonstrates using GroupBy.

public void  DistinctExample()
{
    using (var context = new NorthWindContext())
    {
        context.Configuration.AutoDetectChangesEnabled = false;
        context.Configuration.LazyLoadingEnabled = false;
 
        var orderDetailsResults = (from order in  context.Orders
                join orderDetail in  context.Order_Details on 
                    order.OrderID equals orderDetail.OrderID
                select orderDetail)
            .AsNoTracking()
            .ToList();
 
        var finalResult = orderDetailsResults
            .GroupBy(od => od.OrderID)
            .Select(grouping => grouping.First())
            .ToList();
    }
}

To verify the following SELECT statement was used.

SELECT OrderID  FROM [Order Details] GROUP BY  OrderID ;

There is one major issue, we thought that only one record for each order details was needed but since each order details has the Discount this means each must be updated. This is done in the following case by removing DistinctBy extension method.

public void  ApplyDiscount3(int  pCustomerIdentifier, float pCurrentDiscount, float pNewDiscount)
{
    using (var context = new NorthWindContext())
    {
        context.Configuration.AutoDetectChangesEnabled = false;
        context.Configuration.LazyLoadingEnabled = false;
 
        var orderDetailsResults = (from order in  context.Orders
            join orderDetail in  context.Order_Details on order.OrderID equals orderDetail.OrderID
            where order.CustomerIdentifier == pCustomerIdentifier
            select orderDetail)
            .AsNoTracking()
            .ToList();
 
        orderDetailsResults = orderDetailsResults
            .Where(details => details.Discount == pCurrentDiscount)
            .ToList();
 
        foreach (var item in orderDetailsResults)
        {
            item.Discount = pNewDiscount;
            context.Entry(item).State = EntityState.Modified;
        }
 
        Console.WriteLine(context.SaveChanges());
    }
}

One last point, in the code samples above, when optimized and not returning more data than actually needed will improve performance. The code samples two additional fields not needed which will not causing any performance issues but if on the other hand there were a large amount of unnecessary columns then this is when a smaller container would be used to control how many fields are included.

Tracking changes

DbContext Configuration AutoDetectChangesEnabled has been used above which disabled automatic calls to DetectChanges were DetectChanges scans the tracked entity instances to detect any changes made to the instance data which may assist with performance when returning large sets of data that is a) for viewing only b) working from a disconnected DbContext.

AsNoTracking extension method returns a new query and the returned entities that are not be cached by the context (DbContext or Object Context). This means that the Entity Framework does not perform any additional processing or storage of the entities that are returned by the query. This means that updates will not function as there is not caching. This means the state must be specified before SaveChanges of the DbContext is called. Times to use in a Window Forms project, loading references tables that will not be updated, added or deleted or to display data in a DataGridView for viewing only or for a report engine.

Summary

This article discussed how working with DbContext various modes for working with data along with thoughts on writing queries against database table data with considerations for where a query is executed along with insight to execute these queries in regards to performance.  

Source code

https://github.com/karenpayneoregon/EntityFrameworkDisconnectedEntities

See also