CODEWITHSUNDEEP
c#asp.netsql-servervisual-studioentity-framework
DeveloperDan
DeveloperDan

Reputation: 4684

Why does the old value stay in this object after it is reset?

I've shown three programmers this problem and we're all stumped. I call a Sql Server stored procedure in a foreach loop and the result always is the same as the first call. Even if I hard code parameters (removing the loop) only the first result is assigned to all subsequent calls.

The stored procedure is called by an Entity Framework function import (EF4 database first using the designer). The calling code lives in a repository that is a class library. The repository is called by a separate Asp.net webforms project. The problem code looks like this:

IEnumerable<WorkOrder> orders = _context.GetWorkOrders(UserName, workOrder, customerCode).ToList();

OrderStatus lastStatus = new OrderStatus();

foreach (Order order in orders)
{
     lastStatus = _context.GetOrderStatus(order.OrderNumber).FirstOrDefault();
     order.LastOrderStatus = lastStatus.OrderStatus;
}

As you can see this is pretty basic stuff. Depending on the order numbers passed in I always get the result of the first order number in the loop. I've turned off Ajax (part of the Telerik controls I use) because that has caused baffling errors for me in the past. I really hope you can suggest a way to debug this problem! Thanks in advance.

EDIT: Daniel J.G.'s comment led me to this possible solution. Now I need to figure out how to apply Ladislav Mrnka's answer..."Try to call ExecuteFunction directly with MergeOption.OverwriteChanges."

Upvotes: 4

Views: 408

Answers (2)

DeveloperDan
DeveloperDan

Reputation: 4684

I'm back with a second answer to my own question. Be sure the Entity Key is truly a unique identifier for each Entity!

In my case, the OrderStock Entity was missing the OrderID (along with StockID) as the Entity Key. Typically the designer culls the primary key fields from the database but I have a unique situation (where my entity is based on a view). Since I left off OrderID from the Entity Key I saw duplicate rows for a single OrderStock Entity.

When I marked OrderID Entity Key = True the duplicate problem went away.

Upvotes: 1

DeveloperDan
DeveloperDan

Reputation: 4684

I'm answering my own question (since no one else has after a few days). The problem is caused by the Entity Framework database first designer. It generates code that caches the first stored procedure result causing the bad results in subsequent calls.

As I mentioned in the edit to my question the fix involves replacing the default MergeOption parameter used by ExecuteFunction. You need to use MergeOption.OverwriteChanges instead of the default (which I believe is MergeOption.PreserveChanges).

You could change that parameter in the generated code but your changes would be lost each time the designer is rebuilt. Instead I simply copied the generated code to my repository class, changed the MergeOption to OverwriteChanges, and stopped using the generated code. The end result looks like this:

IEnumerable<WorkOrder> orders = _context.GetWorkOrders(UserName, workOrder, customerCode).ToList();

OrderStatus lastStatus = new OrderStatus();

foreach (Order order in orders)
{

    ObjectParameter workOrderParameter;
    if (wo.WorkOrder != null)
    {
        workOrderParameter = new ObjectParameter("WorkOrder", order.WorkOrder);
    }
    else
    {
       workOrderParameter = new ObjectParameter("WorkOrder", typeof(global::System.String));
    }

    lastStatus = _context.ExecuteFunction<OrderStatus>("GetOrderStatus", MergeOption.OverwriteChanges, workOrderParameter).FirstOrDefault();

    if (status != null)
    {
        order.LastOrderStatus = status.OrderStatus;
    }
}

I also see that there is a way you can modify the T4 template to make the generated code use the correct MergeOption parameter. I haven't tried it though. If you're interested take a look here.

Upvotes: 2

Related Questions