LINQ to SQL: To Attach or Not To Attach

Question

So I'm have a really hard time figuring out when I should be attaching to an object and when I shouldn't be attaching to an object. First thing's first, here is a small diagram of my (very simplified) object model.

In my DAL I create a new DataContext every time I do a data-related operation. Say, for instance, I want to save a new user. In my business layer I create a new user.

var user = new User();
user.FirstName = "Bob";
user.LastName = "Smith";
user.Username = "bob.smith";
user.Password = StringUtilities.EncodePassword("MyPassword123");
user.Organization = someOrganization; // Assume that someOrganization was loaded and it's data context has been garbage collected.

Now I want to go save this user.

var userRepository = new RepositoryFactory.GetRepository<UserRepository>();
userRepository.Save(user);

Neato! Here is my save logic:

public void Save(User user)
{
 if (!DataContext.Users.Contains(user))
 {
  user.Id = Guid.NewGuid();
  user.CreatedDate = DateTime.Now;
  user.Disabled = false;

  //DataContext.Organizations.Attach(user.Organization);
  DataContext.Users.InsertOnSubmit(user);
 }
 else 
 {
  DataContext.Users.Attach(user);
 }

 DataContext.SubmitChanges();

 // Finished here as well.
 user.Detach();
}

So, here we are. You'll notice that I comment out the bit where the DataContext attachs to the organization. If I attach to the organization I get the following exception:

NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

Hmm, that doesn't work. Let me try it without attaching (i.e. comment out that line about attaching to the organization).

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

WHAAAAT? I can only assume this is trying to insert a new organization which is obviously false.

So, what's the deal guys? What should I do? What is the proper approach? It seems like L2S makes this quite a bit harder than it should be...

EDIT: I just noticed that if I try to look at the pending change set (dataContext.GetChangeSet()) I get the same NotSupportedException I described earlier!! What the hell, L2S?!

Answer 1

It may not work exactly like this under the hood, but here's the way I conceptualize it: When you summon an object from a DataContext, one of the things Linq does is track the changes to this object over time so it knows what to save back if you submit changes. If you lose this original data context, the Linq object summoned from it doesn't have the history of what has changed in it from the time it was summoned from the DB.

For example:

DbDataContext db = new DbDataContext();
User u = db.Users.Single( u => u.Id == HARD_CODED_GUID );
u.FirstName = "Foo";
db.SubmitChanges();

In this case since the User object was summoned from the data context, it was tracking all the changes to "u" and knows how to submit those changes to the DB.

In your example, you had a User object that had been persisted somewhere (or passed from elsewhere and do not have it's original DataContext it was summoned from). In this case, you must attach it to the new data context.

User u; // User object passed in from somewhere else
DbDataContext db = new DbDataContext();
u.FirstName = "Foo";
DbDataContext.Users.Attach( u );
db.SubmitChanges();

Since the relationship between user and organization is just a GUID (OrganizationId) in your data model, you only have to attach the user object.

I'm not sure about your scaffolding code, but maybe something like this:

    private const Guid DEFAULT_ORG = new Guid("3cbb9255-1083-4fc4-8449-27975cb478a5");
    public void Save(User user)
    {
        if (!DataContext.Users.Contains(user))
        {
            user.Id = Guid.NewGuid();
            user.CreatedDate = DateTime.Now;
            user.Disabled = false;
            user.OrganizationId = DEFAULT_ORG; // make the foreign key connection just
                                               // via a GUID, not by assigning an
                                               // Organization object

            DataContext.Users.InsertOnSubmit(user);
        }
        else
        {
            DataContext.Users.Attach(user);
        }

        DataContext.SubmitChanges();

    }

Answer 2

So "attach" is used when you take an object that exists from the database, detach it (say by marshalling it over a webservice somewhere else) and want to put it back into the database. Instead of calling .Attach(), call .InsertOnSubmit() instead. You're almost there conceptually, you're just using the wrong method to do what you want.

Answer 3

I used an big table with 400+ columns. No way am I going to map and test all that!

Get the original object from database, and attach it with the amended object. Just make sure the object coming back in is fully populated other wise it will override it the DB with blanks!

Or you can copy the original GET into memory and work on a proper copy (not just reference) of the MOdel, then pass the original and the changed one in, instead of re getting like I do in the example. This is just an example of how it works.

public void Save(User user)
{

    if (!DataContext.Users.Contains(user))
    {
        user.Id = Guid.NewGuid();
        user.CreatedDate = DateTime.Now;
        user.Disabled = false;
        user.OrganizationId = DEFAULT_ORG; // make the foreign key connection just
                                           // via a GUID, not by assigning an
                                           // Organization object

        DataContext.Users.InsertOnSubmit(user);
    }
    else
    {
        var UserDB = DataContext.Users.FirstOrDefault(db => db.id == user.id); //Costs an extra call but its worth it if oyu have 400 columns!
        DataContext.Users.Attach(user, userDB); //Just maps all the changes on the flu
    }

    DataContext.SubmitChanges();

}