Contact   •   Products   •   Search

Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs

LINQ to SQL and attaching Entities


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>

Make Donation
Posted in LINQ  


Feedback for this Post

 
# re: LINQ to SQL and attaching Entities
by scottgu August 12, 2007 @ 8:51pm
I'd recommend downloading this set of whitepapers for more information on LINQ to SQL: http://download.microsoft.com/download/2/4/6/246adaed-0bc1-4671-883f-c9cffcf6a55a/orcasmarchctpwhitepapers.zip

Section 6.7 covers multi-tier entities and the attach syntax. One of my upcoming blog posts will cover this in more detail and cover scenarios were you take changes and apply them from a different tier.

Thanks,

Scott
# re: LINQ to SQL and attaching Entities
by Rob Conery August 12, 2007 @ 10:57pm
CLUNQ... priceless :). I get dibs on "CLANQ"...

I'm sure that what we've been trying to do will be covered in RTM... well I'm not sure but strongly hopeful. Great post Rick!
# re: LINQ to SQL and attaching Entities
by Rick Strahl August 12, 2007 @ 10:58pm
Quote:
>>
If a timestamp or a version number column is used for optimistic concurrency check, then the corresponding member must be set before calling Attach(). Values for other members need not be set before calling Attach(). LINQ to SQL uses minimal updates with optimistic concurrency checks; i.e. a member that is not set or checked for optimistic concurrency is ignored.
<<

I had tried that but even with a timestamp field Attach fails complaining that the PK already exists(when providing an existing entity). I added a timestamp field to all tables - re-added them, checked the generated classes that the timestamp field is recognized as a timestamp. Attach still doesn't seem to work here it continues to fail with the Pk exists already.

This doesn't sound much like a good solution as it requires the database to be explicitly set up to support this format. And why should this be necessary? The client knows the PK of the item so it can do its own comparison either with the WHERE clause or when an object is provided by comparing properties.

Wouldn't it make much more sense if:
Attach() could sync two objects (ie. read an instance from the database and compare properties), or otherwise assume that everything has changed and needs to be updated (ie. the DataAdapter 1.x model)?
# re: LINQ to SQL and attaching Entities
by Steve August 13, 2007 @ 9:54am
That whole 'playback' model seems quirky to me.

So, I make an update to an object, let's say from that example in word document:
c2.ContactName = "Steve"; in my webform.

User hits submit. I pass this object to a webservice. Inside the webservice I attach the object.

Now I have to 'replay' and set the value again???
# re: LINQ to SQL and attaching Entities
by bzburns August 13, 2007 @ 6:50pm
This, as well as the nature of anonymous types (in my book, anonymous typing is what truly makes Linq a killer feature) and the intrinsic inability to persist them across tiers also detracts from the attractiveness of Linq to Sql for the middle tier.
# re: LINQ to SQL and attaching Entities
by Rick Strahl August 13, 2007 @ 7:06pm
Yeah the anonymous type issue is also pretty depressing:

http://www.west-wind.com/WebLog/posts/33570.aspx

But that's maybe not as critical as you can often get away with either passing types back as explicit types, creating custom types or in binding scenarios you don't even need the type information.

The real killer in Web applications though is DataBinding and the use of Reflection. LINQ entity lists binding are 3-4 times slower binding than datareader and 3 times+ slower than DataSets even. Reflection is a killer in this scenario.
# re: LINQ to SQL and attaching Entities
by Ryan Haney August 13, 2007 @ 10:46pm
Would it be possible to add a generic Copy method on a base class that could be called after the attach?

Client:

WebService.UpdateCustomer(customer);

Web service:

public bool UpdateCustomer(customer)
{
Customer c = Customer.Load(customer.CustomerID);

c.Copy(customer);

return c.Save();
}

I believe LINQ will only update properties that have changed, so even if you set them again they won't be sent in the update.
# re: LINQ to SQL and attaching Entities
by bzburns August 13, 2007 @ 10:49pm
The databinding numbers are staggering. Do you have any more info on that data?

Re: anonymous types, it's not so much that it can't be worked around, but we'll be finding ourselves not being able to get past the guilt of implementing Linq to Sql when we can't aren't leveraging anonymous types and jumping through hoops to get real middle tier integration.

Makes me wonder if MS is gearing Linq to Sql toward the "Strongly Typed Data Adapter" crowd :-) I was hoping for a balls to the wall feature and not more excuses to litter presentation layer with provider-specific code.
# re: LINQ to SQL and attaching Entities
by Rick Strahl August 14, 2007 @ 2:32am
@Ryan - Change tracking works through INotifyChanged I believe. So reassigning is all that it would take I think. However, the point is moot if it's complaining about the PK being duplicated in all scenerarios.
# re: LINQ to SQL and attaching Entities
by Matt Warren August 14, 2007 @ 8:23pm
I think you guys are misunderstanding how Attach is used.

Attach is meant for adding new instances of objects to a new instance of a DataContext after the objects have been deserialized from another tier or equivalent. It is not meant for detaching and reattaching the same instances to the same context (or even a different context.) Doing so is strictly forbidden and if the DataContext could consistently tell that you were doing this it would throw an exception, but it can't so it doesn't.

If you want to make an update with data coming from another tier, you have to tell the DataContext how to do it. Calling Attach with just an object instance only provides enough information to let the DataContext pretend the object was just materialized into an unmodified state (as if by querying.) That's why after doing so no updates occur.

LINQ to SQL, by default, uses optimistic concurrency to perform updates and deletes. That means it needs to verify that the database state has not changed before it can perform either. To do so, it needs both the original state of the object and the newly changed state. An overload of Attach lets you specify two object instances, one in the original state and one in the new state. It is also possible to tell LINQ to SQL to 'just update all the fields', you do this with the Attach overload that takes a boolean second parameter. However, in order for optimistic concurrency to work there still must be some 'original' state to prove the database state has not changed. This is where the timestamp column comes in. If your object defines one you are good to go; that's all the original state you need. If you don't then optimistic concurrency cannot be performed, so you get an exception. If you don't care about that, and just want to blast the data into the database you have to opt out of optimistic concurrency by changing the UpdateCheck setting in the mapping to UpdateCheck.Never (its per column.)
# re: LINQ to SQL and attaching Entities
by Rick Strahl August 15, 2007 @ 12:32am
@bzBurns - for databinding the hit is taken on Reflection. When you bind objects any databinding has to use Reflection for retrieving every single value displayed in the databound control. I ran a number of tests with loading up a 5 column thousand row table and bound it to a datagrid with auto column generation. With a DataReader perf was about 40ms, with a DataTable about 43, 44 with a DataSet and 130+ with LINQ generated data.

The numbers pretty much scaled down in this ratio even with smaller pages, and while it probably is not a huge issue on small pages the ration of 3 to 1 remained. Under load test numbers of data reader requests where nearly 3 times higher compared to equivalen LINQ code.

To be fair that's not LINQ's problem - that's databinding against objects which has to use Reflection for everything. Even lowly datasets perform way better because data storage is in collections that can be accessed quicker than properties via Reflection.

<shrug>

Just one more reason why it would be nice if a LINQ query could be translated into a DataReader/DataTable result without first going into Entities.
# re: LINQ to SQL and attaching Entities
by Rick Strahl August 15, 2007 @ 12:35am
@Matt - see follow up post:

http://www.west-wind.com/weblog/posts/135659.aspx

more detail there.
# Exploring EntityKeys, Web Services and Serialization a little further
by Julie Lerman Blog September 02, 2007 @ 7:58pm
# re: LINQ to SQL and attaching Entities
by john gibbons December 18, 2007 @ 4:02pm
what makes no sense about this issue is illustrated in the following code i needed to implement to update in my WCF enabled n-tier environment:

       /// <summary>
        /// Updates a MediaType
        /// </summary>
        /// <param name="mediaType">MediaType Entity</param>
        [OperationBehavior(TransactionScopeRequired=false)]
        public void UpdateMediaType(MediaType mediaType)
        {
            // a local instance of the related data context
            InformationDataContext context;
 
            // if the entity is NOT contained in the table
            // of an instance-level property initialized on first get
            if (!this.InformationDataContext. 
                MediaType.Contains(mediaType))
            {
                // it is OK to set the local instance
                // to the instance-level variable
                context = this.InformationDataContext;
            }
            else
            {
                // if the item IS found it needs to be managed
                // using a new instance of the data context
                context = new InformationDataContext(
                    ConfigurationProvider.ConnectionString);
            }

            // Only with the following implementation
            // Does this line or calls to any of the overloads
            // Successfully persist changes in the manner intended
            context.MediaType.Attach(mediaType, true);

            // Oh, but hooray! We can call "Submit Changes"
            // and experience an abstraction-level once removed 
            // from our dreaded database platform
            context.SubmitChanges();
        }


So what we are saying here is that we have successfully communicated back to our middle tier and hold reference to the object (in this case an entity called a MediaType managed by an InformationDataContext : DataContext) that we want to update but specifically SINCE this reference is held by an object that remains alive we CANNOT access that reference and MUST create a new instance of the associated data context????

I dont think so. And if there is anything close to a database query behind that new data context to look up the associated original value - well that is just sad. Equally so is any claim that this is proper functionality and somehow due to LINQ's candidacy for some particular usage not applicable to given examples.
# re: LINQ to SQL and attaching Entities
by Brett Ryan January 17, 2008 @ 11:45pm
The more I'm trying to use LinQ to SQL within the middle layer of an n-tier solution the more I'm starting to realise that it's not going to work too well, and infact, is more complicated than writing the backend with DataReader's.

LinQ to SQL doesn't allow nice connection pooling mechanisms either, though I can do this myself by passing the connections into the context at creation I would have thought that competing with java's Persistence layer that this would be covered off.

Given that this is a real PITA, I did manage to get LinQ to work correctly after adding a timestamp (rowversion) field. Honestly, I don't normally work with SQL server and haven't encounted a rowversion before.

So here goes.

1. Create your table with a rowversion field, I allow null, though I'm not sure if this is required or not.
2. Drag your table to the designer in VS, or hand craft it if you aren't lazy
3. Set ALL fields "Update Check" to "Never"
4. for your "Version" field, set "Auto generated value" to "True" (again, not sure if this is required), set "Nullable" to true, "Time Stamp" to "True" and "Type" to "System.Data.Linq.Binary".
5. Implement your service similar to the following:

        /// <summary>
        /// Save the product group and return a new copy with updated values.
        /// </summary>
        /// <param name="group">Product group to save.</param>
        /// <returns>Product group with new saved state.</returns>
        public ProductGroup UpdateProductGroup(ProductGroup group) {
            using (FilmLibraryDataContext ctx
                = new FilmLibraryDataContext(connectionString)) {

                // Attach the group to the newly formed context as modified.
                ctx.ProductGroups.Attach(group, true);
                
                // May raise an exception upon a conflict if the row has been
                // modified or deleted.
                ctx.SubmitChanges();

                return group;
            }
        }


6. This will allow optimistic concurency, though you will need the client to track changes (my prefference as you should only require checking differences when conflicts occur).

Please if anyone knows how to NOT use the "rowversion/timestamp" datatype I'd be very interested. My preference is to use a simple "int" data type that is quite simply incramented on Updates and set to 1 on an insert, more than adequate. DateTime values run the risk of DLS changes and I'm not sure how this affects "timestamp" datatypes. This makes it easy for a client to request periodic updates by passing a dictionary of <int id,int ver> getting a returned list of updated objects.

I hope this helps anyone else in the same boat.
# re: LINQ to SQL and attaching Entities
by Jeremy January 24, 2008 @ 10:22pm
It seems like you're all trying to do LinQ-to-Entities functionality only using LINQ-to-SQL instead.
I'd recommend checking out the the ADO.net Entity Framework for use with LINQ rather than forcing it.
# re: LINQ to SQL and attaching Entities
by Rick Strahl January 24, 2008 @ 10:24pm
Yeah sure - but isn't that the point?

Linq to Entities isn't here yet and probably won't be for a while and when this post was actually made L2E had the same fucked up behavior.

Only after people started complaining about L2Sql did the Entity Framework folks relent and look into makeing this actually happen for the EF. And even the model they have now is a tack on approach at best...
# re: LINQ to SQL and attaching Entities
by Rui Santos February 02, 2008 @ 5:49pm
Hy, a resolve my problems with updates using 2 differents "connections"

Like this:

public bool Update(tt_customer customer) {
context = new TimeTrakkerContext();
context.tt_customers.Attach(customer, GetCustumerById(custumer.Pk));
context.SubmitChanges();
return true;
}

public tt_custumer GetCustumerById(int id) {
context = new TimeTrakkerContext();
return context.tt_customers.Single(c => c.Pk == id);
}

That way I have no more problems with "Cannot add an entity with a key that is already in use"

Cya
# re: LINQ to SQL and attaching Entities
by Benjamin Eidelman February 18, 2008 @ 5:19pm
Great post, it shows very well some of the first impressions we had playing with Linq!

Thanks a lot,

I reference this article from my blog: http://blog.pampanotes.com/2008/02/first-thoughts-on-designing-linq.html
# re: LINQ to SQL and attaching Entities
by Brett March 05, 2008 @ 6:28am
It's probably a little late for your purposes but I got the <I>"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"</I> issue solved by both adding the timestamp column to all associated tables AND by adding the <I>IsVersion = true</I> Named Parameter to my DataMember types. That worked well for adds and updates but when I was trying to delete child objects on my types, nothing happens. That's when I tried the Attach(TEntity current, TEntity old) and ran into the <I>"Cannot add an entity with a key that is already in use"</I>.

Did you ever solve that? Linq and WCF just don't seem to play very well together at all.
# re: LINQ to SQL and attaching Entities
by Brett March 05, 2008 @ 6:40am
I gotta remember to read update posts before spouting off. I see that you managed to find a workaround so feel free to completely ignore the previous comment.

Thanks.
# re: LINQ to SQL and attaching Entities
by Waqas March 31, 2008 @ 7:53am
Hi, im facing a same problem! but it doesnt worked with the above suggestion!

my code looks like this

foreach (DLinq.Competence competence in competences)
{
DLinq.Competence c = NetcampusDB.Competences.SingleOrDefault(x => x.ID == competence.ID);

if (c == null)
{
NetcampusDB.Competences.InsertOnSubmit(competence); //this works fine for the first time and empty database got filled
}
/*but after this if the database is not empty, for the update this attach method doesnt work and says "cannot add an entity with a key that is already in use */
else
{
NetcampusDB.Competences.Attach(competence, GetCompetence(competence.ID));
}
}

NetcampusDB.SubmitChanges();

where i have
private DLinq.Competence GetCompetence(int id)
{
DLinq.Competence c = NetcampusDB.Competences.Single(x => x.ID == id);
return c;
}

kindly help me in this regard! Thanks in advance!
# LINQ to SQL Entity Base - Disconnected LINQ
by Alex on ASP.NET April 03, 2008 @ 7:24pm
LINQ to SQL Entity Base - Disconnected LINQ
# re: LINQ to SQL and attaching Entities
by Lars-Erik April 25, 2008 @ 6:16am
# re: LINQ to SQL and attaching Entities
by Sorin May 02, 2008 @ 3:06pm
Solution posted by Rui Santos doesn't check for a concurent change to the data row.


P.S. Rick you have a great blog
# re: LINQ to SQL and attaching Entities
by Michael May 10, 2008 @ 7:59pm
I found it easier just to create one DataContext for the request like so:

<code lang="c#">
public partial class DataClassesDataContext
{
public static DataClassesDataContext Instance
{
get
{
if (HttpContext.Current.Items["DataClassesDataContext"] == null)
HttpContext.Current.Items["DataClassesDataContext"] = new DataClassesDataContext();

return HttpContext.Current.Items["DataClassesDataContext"] as DataClassesDataContext;
}
}
}
</code&gt.

Then use that instance for all queries made. If you store objects in session then you may want to store the instance in session as well. I then just define a Save method on the objects like so:

<code lang="c#">
public void Save()
{
try { DataClassesDataContext.Instance.TableName.InsertOnSubmit(this); }
catch (Exception) {}
DataClassesDataContext.Instance.SubmitChanges();
}
</code&gt.

This approach falls down from a logical standpoint if you assign a value to an object loaded from the database that you don't want saved but call save on another object. But I haven't done that on any of the asp.net sites I work on.
# re: LINQ to SQL and attaching Entities
by Roberto May 22, 2008 @ 5:02am
Great articles, your're right, i encounter the same problem.

And MORE, when you have an array of serialized "Products" entity for example, including or not the same value for ProductID, i get an exception :

CODE:

List<Product> list = Deserialize<List<Product>>( xmlString )
// list include 2 items with ProductID = 1 for both of them


using( Northwind db = new ... )
{
// OF COURSE, TO USE THIS, I SET IsVersion on ProductID column for instance (you can create a much more specific database column Timestamp or version)
// It's fascinating because i only want to use the IsPrimaryKey in order to update my entity but LINQ refuses to
// work in such way except with IsVersion

db.Products.Attach( list[ 0 ], true ); // list[ 0 ].ProductID is 1
db.SubmitChanges(); // IS OK

// BUT THE FOLLOWING IS NOT OK (exception raised)

db.Products.Attach( list[ 1 ], true ); // list[ 0 ].ProductID is 1 also
db.SubmitChanges(); // IS NOT OK = "Cannot add an entity with a key that is already in use."

}

So how can i do, one process serialized many records (including sometimes the same ProductID record with up to date values of course) and the other process cannot read the serialization string in order to save them EASILY. It's really fascinating.

I searched how to disable the DataContext cache.... No success....
I tried with ObjectTrackingEnabled to false but Attach() raises an error.... No success

I found a solution, I create one DataContext per record to update in database....It's not perfect but it works as expected. LINQ and perfomance, are you kidding !!!!

I will appreciate a functions like these :
Attach( Entitiy, bool replaceEntityInCache )
Attach( Entity, bool useOnlyPrimaryKey )
.....

I have the feeling that LINQ is very poor when you work with disconnected record.
What a pity ?
# re: LINQ to SQL and attaching Entities
by Kanakala August 06, 2008 @ 10:31pm
hi,

I had the same problem in my application. I resolved this issue by initializing the datacontext object in each iteration. Check the following code.

TimeManagerDataAccessDataContext objTimeManagerDataAccessDataContext = new TimeManagerDataAccessDataContext();

public void AddNewSupportingDocument(IEnumerable<tblSupportingDocument>     
                                                       objSupportingDocument ,int intStatusId)
{
            if (objSupportingDocument != null)
            {
                DeleteDocuments(intStatusId);
                foreach (var SupportDocument in objSupportingDocument)
                {
                    objTimeManagerDataAccessDataContext = new 
                                                                        TimeManagerDataAccessDataContext();
                   objTimeManagerDataAccessDataContext.GetTable<tblSupportingDocument>
                              ().InsertOnSubmit(SupportDocument);
                    objTimeManagerDataAccessDataContext.SubmitChanges();
                }
            }
        }
# re: LINQ to SQL and attaching Entities
by Tony August 15, 2008 @ 5:37am
I do a similar thing in LINQ to Entities. However, instead of going to all the trouble trying to set the properties' updated status, I simply create the object, then call AcceptAllChanges, which commits all the changes in memory, then update the fields. The updating of the fields is tracked by the object tracker. The context object now thinks the object has been changed, not added, so when updating, it will retrieve and update only the fields that changed since the last AcceptAllChanges(). I've written it up as a short article at http://tonesdotnetblog.wordpress.com.
# re: LINQ to SQL and attaching Entities
by Vlaidmir October 14, 2008 @ 7:39am
Workaround.


var originalEntity = dc.GetTable<T>().Where(x => x.id== entity.id).SingleOrDefault();

foreach (var property in newEntity.GetType().GetProperties())
{
PropertyInfo orignProperty = originalEntity.GetType().GetProperty(property.Name);
orignProperty.SetValue(originalEntity, property.GetValue(newEntity, null), null);
}

dc.SubmitChanges();
# re: LINQ to SQL and attaching Entities
by Chin Bae October 23, 2008 @ 9:30am
This is how I work around this issue (using the original example):
public bool Update(tt_customer customer)
{
     context = new TimeTrakkerContext();
     //tt_customer Tcust = context.tt_customers.Single(c => c.Pk == customer.Pk);
     //create a new instance to avoid object tracking
     tt_customer Tcust = new tt_customer { Pk = customer.Pk };
     context.tt_customers.Attach(customer, Tcust);
     context.SubmitChanges();
     return true;
}
.
# re: LINQ to SQL and attaching Entities
by Mike Russo November 18, 2008 @ 9:42am
I follow yur articles alot ... anyways after some digging I was able to figure out how the Attach works without doing a pre-fetch ...

Within my middle tier function I have this code ...
            using (var context = GetContext())
            {
                Company company = new Company(companyId);
                context.Companies.Attach(company);
                company.UserName = userName;
                company.Password = password; 
                company.EMail = email;
                company.Notes = notes;
                context.SubmitChanges();
            }


First, the Attach should be called before you set any properties ... second if you set the PK property you get an exception so what you do is add a new contructor via partial class ...

    partial class Company
    {
        public Company(int companyName)
        {
            _CompanyId = companyName;
        }
    }


This bypassed the PropertyChange ... it has worked out well for me ... however I also use VS2008 and latest .NET Framework
# re: LINQ to SQL and attaching Entities
by Rick Strahl November 18, 2008 @ 11:39am
@Mike - Interesting, but it doesn't really solve the problem if you're dealing with an existing entity that you want to update - it works only if you manually reassign all values. Also you still end up with issues for child entities, which in the end is the real problem.

It's interesting to see all the different approaches that have been mentioned in this thread. Obviously there are ways this can be done - iit'd just be nice if Microsoft would at least take one of these approaches and automate it behind the scenes.
# re: LINQ to SQL and attaching Entities
by Leo H November 21, 2008 @ 11:12am
Has anyone implemented Rick's suggestion of looping through the properties and assigning only when the property values differt? I am new at LINQ to Sql, and I've noticed that the Properties that we need to assign are decorated with the [Column] attribute. It will work when dealing with single instances but I have not yet thought of how to handle a collection of instances returned from the Db.
# re: LINQ to SQL and attaching Entities
by Josimari July 16, 2009 @ 9:17pm
Link to Sql - Problems on Updating Database

Hello Everyone, I'm quite new to Linq and have had some problems updating my dabase. I don't know if it is actually happening for be using a global data context or I'm missing something. I have my typed DataContex and one Static Public Var to initialize it located within the namespace AlpaCommon, like following:

My partial datacontext***************

// partial datacontext classnamespaceAlpaCommon{public partial class AlpaDataContext : System.Data.Linq.DataContext{//Insert method is working...public void InsertAnimal2(Animal instance){Animais.InsertOnSubmit(instance);SubmitChanges();} //Delete method is working...public void DeleteAnimal2(int animalID){var animal = (from a in Animais where a.AnimalID == animalID select a).First();Animais.DeleteOnSubmit(animal);SubmitChanges();}//Update method IS NOT working...public void UpdateAnimal2(Animal newAnimal){.var animal = (from a in Animais where a.AnimalID == newAnimal.AnimalID select a).First();animal = newAnimal;SubmitChanges();}This is where I'm instanciating the datacontext e other variables I'll need in the whole app*********

//global DataContext instancenamespace AlpaCommon{public static class Globals{public static AlpaDataContext db = new AlpaDataContext();This is the call to the update method **************************

using AlpaCommon;namespace Animais{public partial class Altera : System.Web.UI.Page{protected void btnUpdate_Click(object sender, EventArgs e){try{//cria um novo Objeto do tipo AnimalAnimal animalAltera = new Animal();//set new values animalAltera.AnimalID = Convert.ToInt32(Request.Params["AnimalID"]);animalAltera.Castrado = CastradoCheckBox.Checked;animalAltera.DisponivelAdocao = DisponivelCheckBox.Checked;animalAltera.Adotado = AdotadoCheckBox.Checked;animalAltera.Nome = NomeTextBox.Text;animalAltera.Tipo = TipoDropDownList.SelectedValue;animalAltera.Sexo = SexoDropDownList.SelectedValue;animalAltera.Descricao = DescricaoTextBox.Text;animalAltera.Local = LocalTextBox.Text;animalAltera.Foto = AlteraFoto(); AlpaCommon.Globals.db.UpdateAnimal2(animalAltera);redirect = redirectSucesso;}catch{redirect = redirectErro;}finally{Helper.Redirect(redirect);}}

--------------------------------------------------------------------------------


I'm not catching any exception, it just does not update the database. Am I missing something in my updating or calling method? I'm looking forward for suggestions.

Thank you

Josimari Martarelli
# re: LINQ to SQL and attaching Entities
by Nicolas Diaz Aragon December 29, 2009 @ 12:08pm
All those nasty comments about linq-to-sql are out of place. There are no flaws in the implementation but there is however one catch: you have to be deft enough to use it.

API like the one found in linq to sql has been around in java enterprise edition for years, so I eagerly waited for the first release of the dot net framework with linq bundled-in.

The exception telling you that your entity can't be attached because the key has been modified occurs because the key is the only means of reaching an entity. The key is the entity's identity so if you change it, it will seem as if it were another entity. Change whatever you like but keep the original key, then try to attach using the new values and the original values.

Aside from the technical stuff, if you really want to use linq in N-tier remote applications you have to get past the ADO paradigm and embrace the linq way of staring at the world.

Best regards.
Nicolas.
# re: LINQ to SQL and attaching Entities
by Ali Irawan January 09, 2010 @ 2:16am
Hi i got this issue to when using LINQ with ASP.NET MVC

but i try to look out and found this
http://www.codeproject.com/KB/linq/linqmultitier.aspx

Best regards
# re: LINQ to SQL and attaching Entities
by Christophe Keller February 12, 2010 @ 12:01am
Hi,

I've written a simple article which explains how to properly reattach and submit a changed entity. It's rather easy once you know how to do it:

http://christophekeller.blogspot.com/2010/02/linq-to-sql-updating-reattached-entity.html
# re: LINQ to SQL and attaching Entities
by Rick Strahl February 12, 2010 @ 11:15am
@Christophe, uhm not really. It works for single entities but if you have child entities or collections you'll find that those don't update properly. Lots of issues in the re-attachment process.
# re: LINQ to SQL and attaching Entities
by Christophe Keller February 26, 2010 @ 4:36am
In that case you need to reattach the child entities or collections too. I agree that it can quickly become cumbersome if you need to reattach more than the main entity and maybe a single child collection.
# Linq returns the whole table
by Rob von Nesselrode March 09, 2010 @ 7:39pm
Hi Rick,

I've been doing some maintenance work on a heavily Linq'd middle tier and was suprised to find that it seems to always retrieve the whole table via the Datacontext.GetTable<>().

Even with you line from above:

tt_customer Tcust = context.tt_customers.Single(c => c.Pk == customer.Pk);


The whole table will be pulled into the "repository".

Am I right? or have I been in the sun too long...

Underlying this is the real question: "how to get back just one record in the first place".



Regards

Rob
# re: LINQ to SQL and attaching Entities
by Kristian Ravndal May 06, 2010 @ 3:55am
I use this to solve the Attach problem:

        private YourDataContext _context;
 
        /// <summary>
        /// Used to extend all entities in the datacontext
        /// </summary>
        public interface IBaseEntity
        {
            /// <summary>
            /// Expected to be tables primary key
            /// </summary>
            int ID { get; set; }
        }
 
        /// <summary>
        /// Saves an entity to the database. 
        /// </summary>
        /// <returns>-1 = Failed. 0 = Update Ok, otherwise it returns the ID of the object</returns>
        public int SaveEntity<TEntity>(TEntity entity) where TEntity : class, IBaseEntity
        {
            var original = _context.GetTable<TEntity>().FirstOrDefault(p => p.ID == entity.ID);
            if (original != null)
            {
                return UpdateEntity(entity, original);
            }
            
            try
            {
                _context.GetTable<TEntity>().InsertOnSubmit(entity);
                _context.SubmitChanges();
                return entity.ID;
            }
            catch
            {
                return -1;
            }
        }
 
        public int UpdateEntity<TEntity>(TEntity source, TEntity original) where TEntity : class, IBaseEntity
        {
            using (var saveContext = new YourDataContext(ConnectionString))
            {
                saveContext.GetTable<TEntity>().Attach(source, original);
                try
                {
                    saveContext.SubmitChanges();
                    return 0;
                }
                catch
                {
                    return -1;
                }
            }
        }
# re: LINQ to SQL and attaching Entities
by Kristian Ravndal May 06, 2010 @ 4:05am
@Rob

// this
context.GetTable<TType>().First(p=>p.ID == 1);
// is the same as this
context.TType.First(p=>p.ID == 1);


You can track what queries LTS generates like this:
        public static void Main(string[] args)
        {
            using(var context = new DataContext())
            {
                // Sets the context to show every query done to the console window
                context.Log = Console.Out;
 
                // this wil generate a sql like: select top(1) from table where id = 1
                context.GetTable<TEntity>().First(p => p.ID == 1);
 
                // this wil generate a sql like: select * from table
                // and then perform a normal linq query on the object collection. 
                context.GetTable<TEntity>().ToList().First(p => p.ID == 1);
            }
        }
# re: LINQ to SQL and attaching Entities
by Exhaustpipe May 19, 2010 @ 6:51am
I know this is offtopic, but since i've spent several hours googling (i'm new to the linq), i've decided to post this simple code for updating with linq for some poor soul that might be just looking to do that:

public bool UpdateMember(Member objMem)
{
DataClassesDataContext db = new DataClassesDataContext();

var mmbrs =
from p in db.Members
where p.Member_id == objMem.Member_id
select p;
foreach (var item in mmbrs)
{
item.Address = objMem.Address;
item.City = objMem.City;
item.FirstName = objMem.FirstName;
item.LastName = objMem.LastName;
}
//db.Members.Attach(objMem, true);//GetMemberById(objMem.Member_id)
db.SubmitChanges();

return true;
}
# re: LINQ to SQL and attaching Entities
by Pascal August 16, 2010 @ 9:02pm
When It came to the decision to use linq or not I went pro linq - and Im regretting it now. As u said there are many unresolved issues and "bugs" that makes things more complicated. I have done so many work arounds that managing the code afterwards becomes horribly difficult and I can only advise every one not to use linq2sql for bigger projects. The concept is great but the implementation is (so far) not usable.
# re: LINQ to SQL and attaching Entities
by JohnL October 14, 2010 @ 4:40pm
I was irritated by this same problem, but found a solution through reflection - I just made a generic copier, so I didn't have to name each changable property in code.

        static void CopyProperties<T>(ref T Target, T Source)
        {
            foreach (PropertyInfo PI in Target.GetType().GetProperties())
            {
                if (PI.CanWrite && PI.CanRead)
                {
                    PI.SetValue(Target, PI.GetValue(Source, null), null);
                }
            }
        }


And while you will still end up with some extra traffic, it makes this sort of thing easy:

        static void Save(Test_TableData ChangedData)
        {
            using (DataClasses1DataContext D = new DataClasses1DataContext())
            {
                Test_TableData UpdateTarget = D.Test_TableDatas.SingleOrDefault(i => i.ID == ChangedData.ID);
                if (UpdateTarget != null)
                {
                    CopyProperties<Test_TableData>(ref UpdateTarget, ChangedData);
                }
                else
                {
                    D.Test_TableDatas.InsertOnSubmit(ChangedData);
                }
                D.SubmitChanges();
            }
        }


I am not working with incredibly complex SQL tables or anything, but for the time being, this gets the job done with relatively little code.
 


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2014