Category Archives: Entity Framework4

EntityFramework RefreshAll loaded entities from Database

This samples will talk about Entity Framework 4 (ObjectContext). I’ll show in a next post how to get this done with EF5 DbContext. It should be much easier.

Update: here it is, finally: https://christianarg.wordpress.com/2015/07/15/entityframework-dbcontext-refreshall-loaded-entities-from-database/

Update2: based on feedback on stackoverflow I’ve found a bug in the code. I was actually trying to refresh a just added entity, which will result in an exception. So I’ve removed EntityState.Added from the GetObjectStateEntries parameters.

With the default behaviour, once we get an entity from the Database, if it is changed in background, and even if you query the entity again, you will see the old values.

The easy way to solve this is two use very short lived context, and “reload” the entity in another context. If that is not posible (ex: you are using the context in a PerRequest basis, or Ioc Container Controlled) you have some options:

Refresh all loaded entities from Database

This code is simple and it can be very helpful, but you must be aware that it will reload ABSOLUTELLY ALL THE OBJECTS THAT YOU HAVE ALREADY QUERIED. If you queried many entites, it will have a negative performance impact

public void RefreshAll()
{
     // Get all objects in statemanager with entityKey
     // (context.Refresh will throw an exception otherwise)
     var refreshableObjects = (from entry in context.ObjectStateManager.GetObjectStateEntries(
                                                 EntityState.Deleted
                                               | EntityState.Modified
                                               | EntityState.Unchanged)
                                      where entry.EntityKey != null
                                      select entry.Entity);

     context.Refresh(RefreshMode.StoreWins, refreshableObjects);
}

Pros:

  • Very easy to use, once you have the code above :)

Cons:

  • Potentially, it could execute A LOT of queries in order to refresh a context that “used” many queries

Refresh specific entities

Let’s assume we have a Blog application for the example.


// First we need to Add the objects to our refresh list
var objectsToRefresh = new List<System.Object>();
objectsToRefresh.Add(blogPost);
objectsToRefresh.Add(blogPost.User);

foreach (var comment in blogPost.Comments)
{
    objectsToRefresh.Add(comment);
    objectsToRefresh.Add(comment.User);
 // etc
}
// Here it ended your application custom Code. Now you have to:

// Clean nulls and repeateds (context.Refresh will throw an exception otherwise)
var noNullsAndRepeateds = objectsToRefresh.Where(o => o != null).Distinct().ToList();

// Get only the entities that are being tracked by ObjectStateManager and have entityKey
// (context.Refresh will throw an exception otherwise)
var finalList = (from e in entityContext.ObjectStateManager.GetObjectStateEntries(
                                                 EntityState.Deleted
                                               | EntityState.Modified
                                               | EntityState.Unchanged)		

		where e.EntityKey != null &&
                noNullsAndRepeateds.Contains(e.Entity)
		select e.Entity).ToList();

entityContext.Refresh(RefreshMode.StoreWins, finalList);

Pros

  • Granular queries
  • Easy to use, once you have the code above :)

Cons

  • The problem is if you have an aggregate or object complex graph.You need to “craft” code to refresh each aggregate.
  • You have to manually “inform” which objects to refresh

Use MergeOptions
There are many resources on the web on how to do this


var query = context.BlogPosts;
query.MergeOption = MergeOption.OverwriteChanges;
var blogPost = query.SingleOrDefault(b=> b.Id == 1);

Pros

  • A good and recommended aproach.
  • Granular queries

Cons

  • Again, if you have an aggregate or object complex graph. You’ve probably had query it using multiple queries (ex: because when tunning and profiling you got better results). If this is the case you would need to specify the MergeOption for each query. That could be hard work
  • Manually refresh only affected objects
  • The way of specifying it in EF4 is a bit counter-intuitive

Entity Framework. View failed Sql sentence with actual parameters

The Problem

So, you are executing some Entity Framework code that seems ok and you are getting constaint exception when calling Save()

Then you think. “I’m going to watch whats going on. I’ll check EXACTLY the Sql sentence that’s being executed and find the Bug”. And you are going to desperate.

Intellitrace will show you the Sql sentence. But not the parameters

And there is an IQueryable.ToTraceString() but you have to modify your code AND it’s intented yo use with Queries, and you want to see a modifying sentence.

The solution: SqlProfiler

  1. Open SqlProfiler
  2. New Trace
  3. Choose Standart
  4. Event Selection
  5. Uncheck Audit, Existing Connection (and Stored procedure if you want)
  6. Check Show all Filters
  7. In TSQL Check ONLY:
  8. SQL:BatchStarting
  9. SQL:StmtStarting

sqlprofiler2

Why Starting and not any other event? Because if the sentence is failing (for example: a unique constraint) the “starting event” is the only one that records a trace, it will never complete.

Extra: when you have a query

In case you are executing a Query and you don’t have Sql Profiler, you can check this solution posted in stack overflow. I haven’t tried it myself but it looks fine. You have to modify your code, though

Entity Framework 4, execute a writing stored procedure inside a TransactionScope

Suppose you have a stored procedure that performs multiple insert, update or delete. You may do this in for many reasons, probably for performance, to avoid reading a large object graph with no need. In my case I needed to delete a large object graph. So I’ve writed my powerful stored procedure and created a function import. Here’s a really simplified example:


using(var tx = new TransactionScope())
{
  using(var ctx = new EntityContext())
  {
      ctx.FunctionImportWithDeleteStoredProcedure(parameter);
  }
  tx.Complete();
}

Seems ok isn’t it? Well you will get a TransactionAbortedException: “The transaction operation cannot be performed because there are pending requests working on this transaction.”

Why? Function import in EF are thought to be used to perform operations and retrieve values. It currently lacks the functionality of no return type (Yes there is the “None” return type option in the function import form, I encourage you to try it.). My Stored procedure was prepared for this, I return the rows affected.

Anyway, what can we do? We must evaluate the result, even if we don’t need it:


using(var tx = new TransactionScope())
{
  using(var ctx = new EntityContext())
  {
     // Evaluating the result does the magic!!
     ctx.FunctionImportWithDeleteStoredProcedure(parameter)
                              .SingleOrDefault(); 
  }
  tx.Complete();
}

Thats it. A bit tricky yes, but it works. I realised afterwards that in my case probably the right solution would be to map the store procedure to my entity remove operation, to remove the entity and the entire related object Graph. See Map Modification Functions to Stored Procedures
Many thanks to Danny Simmons for his answers.