LINQ has trouble updating entities from model binding

I have an edit form with ExtJs but ExtJs is not relevant for this problem. On submit, I bind the form to a model.

public ActionResult EditUserHour([Bind(Include = "Id, Duration, HourCategoryId, Explanation, InvoiceTypeId, StartDate, StartTime, TicketId")] UserHour userHour)
{
    if (ModelState.IsValid)
    {
        try
        {
            _service.Update(userHour);
        }
        catch (RuleException ex)
        {
            ex.CopyToModelState(ModelState, string.Empty);
        }

    }
    if (ModelState.IsValid)
        return Json(new { success = true, redirect = Url.Action(ListAction) });
    else
        return Json(new { success = false, errors = ModelState.ToDictionary() });
}

Look at the _service.Update(userHour); line. Between the controller and the repository is a service layer so I don't call the repository directly. I consider this a better design than mixing data access, validation and business logic in a repository.

Currently, the - not working - method in _service is:

开发者_开发问答public void Update(UserHour userHour)
{
    CRMDataContext c = new CRMDataContext();
    c.Refresh(RefreshMode.KeepCurrentValues, userHour);
    c.SubmitChanges();
}

I have tried everything from all c.Attach(...) calls possible and refresh calls, but I get various exceptions like attaching an object which has a key which already exists.

One of the possible solutions I have encountered is retrieving the original entity from datacontext and simply set all the properties, but that's far from a neat solution. A second option is to use FormCollection to map to the original entity, but I avoid using FormCollection and prefer model binding due to security reasons. Besides, I cannot imagine model binding is incompatible with update.

Is there a possibility of creating a userHour in the controller with model binding, giving it the identity of the userHour it is actually updating and store it in the database? It has bugged me all day long by now.

Any help is greatly appreciated!

If you're using a view model or a presentation model, then you're going to have to deal with all of that ugly left/right assignment code. There's just no good way around it. That's the nature of DTO's. They add a layer of abstraction, and a lot of times they're helpful, but then you have to deal with the consequences.

However, you example seems to indicate that you have a one-to-one relationship between your database and your view. We can exploit that with Attach(). Here's how to do it.

• Make sure that your table has a timestamp column. Without the timestamp column, L2S doesn't do a very good job of tracking updates.

Example:

• In your view, make sure that you have both the ID and timestamp as hidden properties. You'll need these both of these when you reattach your object.

Example:

@Html.HiddenFor(x => x.Id)
@Html.HiddenFor(x => x.Timestamp)

• In your controller or service, attach will now work.

Example:

public void Update(UserHour userHour)
{
  var ctx = new CRMDataContext();
  ctx.UserHours.Attach(userHour, true);
  ctx.SubmitChanges();
}

As you said, the only way is to fetch the original object and update its properties.

You could implement a generic extension to Data.Linq.Table which accepts your model bound object an retrieves the original item from the DataContext by its primary key. After this you iterate thru all properties with refrection and set the new values to the retrieved entity. You may give an array of properties you wish to update in case not all properties were bound thru model binding and so set to default.

I know Refelction is not a good solution, but it helps sometimes.

public static class TableExtensions
{
    public static void UpdateOnSubmit<T>(this Table<T> table, T changes) where T : class
    {
        UpdateOnSubmit(table, changes, null);
    }

    public static void UpdateOnSubmit<T>(this Table<T> table, T changes, string[] properties) where T : class
    {
        var item = table.FirstOrDefault(GetPkExpression(table, changes));
        if (item != null)
        {
            UpdateItem<T>(ref item, changes, properties);
        }
    }

    private static void UpdateItem<T>(ref T original, T changes, string[] properties) where T : class
    {
        Type OriginalType = typeof(T);

        if (properties == null)
        {
            PropertyInfo[] Info = OriginalType.GetProperties();

            foreach (PropertyInfo PI in Info)
            {
                if (IsUpdateableColumn(PI))
                {
                    PI.SetValue(original, PI.GetValue(changes, null), null);
                }
            }
        }
        else
        {
            foreach (string propName in properties)
            {
                PropertyInfo PI = OriginalType.GetProperty(propName);

                if (PI != null && IsUpdateableColumn(PI))
                {
                    PI.SetValue(original, PI.GetValue(changes, null), null);
                }
            }
        }
    }

    private static bool IsUpdateableColumn(PropertyInfo pi)
    {
        object[] attributes = pi.GetCustomAttributes(typeof(ColumnAttribute), true);

        if (attributes.Length == 0)
            return false;

        foreach (ColumnAttribute attr in attributes)
        {
            if (attr.IsDbGenerated)
                return false;
        }

        return true;
    }

    private static Expression<Func<T, bool>> GetPkExpression<T>(Table<T> table, T value) where T : class
    {
        var mapping = table.Context.Mapping.GetTable(typeof(T));
        var pk = mapping.RowType.DataMembers.FirstOrDefault(d => d.IsPrimaryKey);

        if (pk == null)
        {
            throw new Exception(string.Format("Table {0} does not contain a Primary Key field", mapping.TableName));
        }

        var pkValue = typeof(T).GetProperty(pk.Name).GetValue(value, null);

        var param = Expression.Parameter(typeof(T), "e");
        return Expression.Lambda<Func<T, bool>>(Expression.Equal(Expression.Property(param, pk.Name), Expression.Constant(pkValue)), new ParameterExpression[] { param });
    }
}

After using this extension your Update function should look like:

public void Update(UserHour userHour)
{
    CRMDataContext c = new CRMDataContext();
    c.UserHour.UpdateOnSubmit(userHour, new string[] { "Id", "Duration", "HourCategoryId", "Explanation", "InvoiceTypeId", "StartDate", "StartTime", "TicketId" });
    c.SubmitChanges();
}

You may omit the array with property names if all properties sould be updated.

I'd go with @Jarett says if that works.

The real problem is that L2S hides the Entity State from the object itself and so when you even attach it, it thinks it's a brand new Entity. If it knew better L2S wouldn't kill the transaction.That's why @Jarett's option might work because that's built into L2S.

So this is a problem with L2S so unless you change your ORM(which is probably not an option)you have to deal with it's quirks.

A possible option is to switch to PLINQO which overcomes the L2S lack of an entity outliving it's datacontext. It's still L2S just more functionality. Unfortunately you'll need CodeSmith to use PLINQO.