C# – LINQ to SQL: To Attach or Not To Attach

clinq-to-sql

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.

Schema

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

Best Answer

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

    }
Related Topic