Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

LINQ to SQL and attaching Entities


:P
On this page:

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>

Posted in LINQ  

The Voices of Reason


 

scottgu
August 12, 2007

# re: LINQ to SQL and attaching Entities

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

Rob Conery
August 12, 2007

# re: LINQ to SQL and attaching Entities

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!

Rick Strahl
August 12, 2007

# re: LINQ to SQL and attaching Entities

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)?

Steve
August 13, 2007

# re: LINQ to SQL and attaching Entities

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???

bzburns
August 13, 2007

# re: LINQ to SQL and attaching Entities

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.

Rick Strahl
August 13, 2007

# re: LINQ to SQL and attaching Entities

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.

Ryan Haney
August 13, 2007

# re: LINQ to SQL and attaching Entities

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.

bzburns
August 13, 2007

# re: LINQ to SQL and attaching Entities

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.

Rick Strahl
August 14, 2007

# re: LINQ to SQL and attaching Entities

@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.

Matt Warren
August 14, 2007

# re: LINQ to SQL and attaching Entities

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.)

Rick Strahl
August 15, 2007

# re: LINQ to SQL and attaching Entities

@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.

Rick Strahl
August 15, 2007

# re: LINQ to SQL and attaching Entities

@Matt - see follow up post:

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

more detail there.

john gibbons
December 18, 2007

# re: LINQ to SQL and attaching Entities

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.

Brett Ryan
January 17, 2008

# re: LINQ to SQL and attaching Entities

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.

Jeremy
January 24, 2008

# re: LINQ to SQL and attaching Entities

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.

Rick Strahl
January 24, 2008

# re: LINQ to SQL and attaching Entities

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...

Rui Santos
February 02, 2008

# re: LINQ to SQL and attaching Entities

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

Benjamin Eidelman
February 18, 2008

# re: LINQ to SQL and attaching Entities

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

Brett
March 05, 2008

# re: LINQ to SQL and attaching Entities

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.

Brett
March 05, 2008

# re: LINQ to SQL and attaching Entities

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.

Waqas
March 31, 2008

# re: LINQ to SQL and attaching Entities

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!

Lars-Erik
April 25, 2008

# re: LINQ to SQL and attaching Entities


Sorin
May 02, 2008

# re: LINQ to SQL and attaching Entities

Solution posted by Rui Santos doesn't check for a concurent change to the data row.


P.S. Rick you have a great blog

Michael
May 10, 2008

# re: LINQ to SQL and attaching Entities

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.

Roberto
May 22, 2008

# re: LINQ to SQL and attaching Entities

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 ?

Kanakala
August 06, 2008

# re: LINQ to SQL and attaching Entities

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();
                }
            }
        }

Tony
August 15, 2008

# re: LINQ to SQL and attaching Entities

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.

Vlaidmir
October 14, 2008

# re: LINQ to SQL and attaching Entities

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();

Chin Bae
October 23, 2008

# re: LINQ to SQL and attaching Entities

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;
}
.

Mike Russo
November 18, 2008

# re: LINQ to SQL and attaching Entities

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

Rick Strahl
November 18, 2008

# re: LINQ to SQL and attaching Entities

@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.

Leo H
November 21, 2008

# re: LINQ to SQL and attaching Entities

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.

Josimari
July 16, 2009

# re: LINQ to SQL and attaching Entities

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

Nicolas Diaz Aragon
December 29, 2009

# re: LINQ to SQL and attaching Entities

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.

Ali Irawan
January 09, 2010

# re: LINQ to SQL and attaching Entities

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

Christophe Keller
February 12, 2010

# re: LINQ to SQL and attaching Entities

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

Rick Strahl
February 12, 2010

# re: LINQ to SQL and attaching Entities

@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.

Christophe Keller
February 26, 2010

# re: LINQ to SQL and attaching Entities

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.

Rob von Nesselrode
March 09, 2010

# Linq returns the whole table

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

Kristian Ravndal
May 06, 2010

# re: LINQ to SQL and attaching Entities

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;
                }
            }
        }

Kristian Ravndal
May 06, 2010

# re: LINQ to SQL and attaching Entities

@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);
            }
        }

Exhaustpipe
May 19, 2010

# re: LINQ to SQL and attaching Entities

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;
}

Pascal
August 16, 2010

# re: LINQ to SQL and attaching Entities

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.

JohnL
October 14, 2010

# re: LINQ to SQL and attaching Entities

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.

Felice Ferri
November 04, 2018

# re: LINQ to SQL and attaching Entities

Had exactly the same problem.

In my case, the problem was because I was using the same Context to perform a Query.

I mean, I looked for the Record if it didn't exists I did an InsertOnSubmit, if it did exists I did an Attach and there is where it failed (Did everything, rowversion column, tagged as timestamp in the .dbml etc)

When I decided to create to different instances of the Context, one for the search of the existence of the record, dispose that and then instantiate a new one for the Attach/Insert is just worked.


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