I am playing around with LINQ to SQL in conjunction with business objects. So rather than using LINQ directly in the front end UI I want an abstraction layer. One problem that I can't seem to resolve with the data context is how to update Entities based on POCO objects that didn't originate out of the DataContext. LINQ wants to manage all objects through its data context which basically manages change tracking for any entities - any changes you make can later be persisted.

That's fine if you use LINQ directly in your UI code but it's not all that great if you use LINQ in the middle tier. Incidentally Rob Connery also mentioned this in his LINQ SonicCast last week. The idea is that you have wrapper methods for CRUD (and all other data access) in the business layer so you'd write code like this:

 
busCustomerBasic Customer = new busCustomerBasic();
 
tt_customer cust = Customer.Load(1);
if (cust == null)
{
    Response.Write(Customer.ErrorMessage);
    return;
}
 
cust.Address = "43 Kaiea Place " + DateTime.Now.ToString();
 
if ( !Customer.Update(cust) )
{
    Response.Write(Customer.ErrorMessage);
    return;
}

True this doesn't look much different than using the LINQ objects directly but the big difference is that you get some level of abstraction for the load and update code so you can inject things like tracing and error handling etc.

So to implement you might use code like this for the Load:

public tt_customer Load(int pk)
{
    try
    {
        tt_customer cust = context.tt_customers.Single(c => c.Pk == pk);
        if (cust == null)
        {
            this.SetError("Invalid Pk");
            return null;
        }
 
        return cust;
    }
    catch (Exception ex)
    {
        this.SetError(ex);
        return null;
    }
 
    return null;
}

and another routine later on that saves this object (assuming that possibly the context has gone away). There's a method called Attach provided on the ITable instance that supposedly allows re-attaching an instance to be change tracked. Unfortunately I can't get this to work:

public bool Update(tt_customer customer)
{
    context = new TimeTrakkerContext();
 
    tt_customer Tcust = context.tt_customers.Single(c => c.Pk == customer.Pk);
    context.tt_customers.Attach(customer, Tcust);
    context.SubmitChanges();       
 
    return true;
}

The code compiles but at runtime it complains:

Cannot add an entity with a key that is already in use.

Huh? Am I not telling LINQ to compare the two objects and assume the differences to be changes? That's what I would expect from an Attach method that takes two object parameters.

I also tried:

    context.tt_customers.Attach(customer, true);

An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.

Nope doesn't like that either. You've got to be kidding, right? How hard could it possibly be to reattach an entity that is actually mapped by the entity manager? The manager knows all the fields involved and at the very least should be able to create an update statement that checks everything or in the case of comparing two entitities and figuring out the differences. Nothing a little Reflection code can't  fix.

I can use this code and it won't bomb outright:

  context.tt_customers.Attach(customer);

But it also doesn't do anything on the server. It basically looks at the object and sees no changes related to the current context so it doesn't update anything. IOW, it attaches and enables change tracking but only from this point forward. But even that doesn't work. If you do:

   context.tt_customers.Attach(customer);
   customer.Address = customer.Address;           

you now get:

Cannot add an entity with a key that is already in use.

again.

So, is this the way it's supposed to work or a bug? I would expect that if you pass in a context loaded object to compare against, that LINQ should be able to reestablish proper update status.

I just don't understand what could possibly be so hard about re-attaching an object. The Context/Tables hold all of the information necessary to figure out what's changed and it would be a fairly trivial matter - using Reflection or simply using a full field SQL Update statement to get updates to go to the server.

And yes there is a potential workaround for this particular issue for some scenarios which involves just letting the business object hang on to the DataContext, which frankly is the more common case. So rather than doing an update as above you'd write say a Save() method that does:

public bool Save()
{
    try
    {
        context.SubmitChanges();
    }
    catch(Exception ex)
    {
        this.SetError(ex);
        return false;
    }
 
    return true;
}

That works *as long as you hang on to the business object*.

Now although I don't think this is great it's not as terrible as it might sound given that the DataContext is not actually connected to the database - all data access is handled atomically by the DataContext connection and disconnecting, but you still have to deal with this persistence. If you retrieve an object from some external source you're downright out of luck.

Using the connected state is also bad news for New entries which have to be explicitly added with Table.Add() and a new object. This means the burden of determining whether you're dealing with new entity or updating an existing one is really up to you, which is also sucky - LINQ to SQL doesn't figure that out automatically.

CLUNQ

The more I look at LINQ the more I'm coming to the conclusion that using LINQ in a middle tier - especially in a generic business object architecture - is not going to work well. There are many little problem issues that when all added up point at more problems being created than solved by the entity generation and easy CRUD layer.

Hopefully I'm just being dense and there are some workarounds for some of these issues, but reading a number of other posts on this 'detached' issue at least it doesn't appear so... Apparently even the ADO.NET Entity framework doesn't address this issue. <shrug>