software solutions / project leadership / agile coaching and training

LINQ to SQL In Disconnected/N-Tier scenarios: Saving an Object

Posted on February 10, 2008

LINQ to SQL is a great tool, but when you’re using it in an n-tier scenario, there are several problems that you have to solve. A simple example is a web service that allows you to retrieve a record of data and save a record of data when the object that you are loading/saving had child lists of objects. Here are some of the problems that you have to solve:

1) How can you create these web services without having to create a separate set of entity objects (that is, we want to use the entity objects that the LINQ to SQL designer generates for us)?
2) What do we have to do to get LINQ to SQL to work with entities passed in through web services?
3) How do you create these loading/saving web services while keeping the amount of data passed across the wire to a minimum?

I created a sample project using the Northwind sample database. The first thing to do is to create your .dbml file and then drag tables from the Server Explorer onto the design surface. I have something that looks like this:

dbml

I’m also going to click on blank space in the design surface, go to the Properties window, and set the SerializationMode to Unidirectional. This will put the [DataContract] and [DataMember] attributes on the designer-generated entity objects so that they can be used in WCF services.

SerializationMode

Now I’ll create some web services that look something like this:

public class EmployeeService
{
public Employee GetEmployee(int employeeId)
{
NorthwindDataContext dc = new NorthwindDataContext();
Employee employee = dc.Employees.FirstOrDefault(e => e.EmployeeID == employeeId);
return employee;
}

public void SaveEmployee(Employee employee)
{
// TODO
}
}

I have two web service methods — one that loads an Employee by ID and one that saves an Employee.

Notice that both web service methods are using the entity objects generated by LINQ to SQL. There are some situations when you will not want to use the LINQ to SQL generated entities. For example, if you’re exposing a public web service, you probably don’t want to use the LINQ to SQL entities because that can make it a lot harder for you to refactor your database or your object model without having to change the web service definition, which could break code that calls the web service. In my case, I have a web service where I own both sides of the wire and this service is not exposed publicly, so I don’t have these concerns.

The GetEmployee() method is fairly straight-forward — just load up the object and return it. Let’s look at how we should implement SaveEmployee().

In order for the DataContext to be able to save an object that wasn’t loaded from the same DataContext, you have to let the DataContext know about the object. How you do this depends on whether the object has ever been saved before.

How you make this determination is based on your own convention. Since I’m dealing with integer primary keys with identity insert starting at 1, I can assume that if the primary key value is < 1, this object is new.

Let's create a base class for our entity object called BusinessEntityBase and have that class expose a property called IsNew. This property will return a boolean value based on the primary key value of this object.

namespace Northwind
{
[DataContract]
public abstract class BusinessEntityBase
{
public abstract int Id { get; set; }

public virtual bool IsNew
{
get { return this.Id <= 0; }
}
}
}

Now we have to tell Employee to derive from BusinessEntityBase. We can do this because the entities that LINQ to SQL generates are partial classes that don’t derive from any class, so we can define that in our half of the partial class.

namespace Northwind
{
public partial class Employee : BusinessEntityBase
{
public override int Id
{
get { return this.EmployeeID; }
set { this.EmployeeID = value; }
}
}
}

Now we should be able to tell if an Employee object is new or not. I’m also going to do the same thing with the Order class since the Employee object contains a list of Order objects.

namespace Northwind
{
public partial class Order : BusinessEntityBase
{
public override int Id
{
get { return this.OrderID; }
set { this.OrderID = value; }
}
}
}

OK, let’s start filling out the SaveEmployee() method.

public void SaveEmployee(Employee employee)
{
NorthwindDataContext dc = new NorthwindDataContext();
if (employee.IsNew)
dc.Employees.InsertOnSubmit(employee);
else
dc.Employees.Attach(employee);
dc.SubmitChanges();
}

Great. So now I can call the GetEmployee() web method to get an employee, change something on the Employee object, and call the SaveEmployee() web method to save it. But when I do it, nothing happens.

The problem is with this line:

public void SaveEmployee(Employee employee)
{
NorthwindDataContext dc = new NorthwindDataContext();
if (employee.IsNew)
dc.Employees.InsertOnSubmit(employee);
else
dc.Employees.Attach(employee); // <-- PROBLEM HERE
dc.SubmitChanges();
}

The Attach() method attaches the entity object to the DataContext so that the DataContext can save it. But the overload that I called just attached the entity to the DataContext and didn't check to see that anything on the object had been changed. That doesn't do us a whole lot of good. Let's try this overload:

public void SaveEmployee(Employee employee)
{
NorthwindDataContext dc = new NorthwindDataContext();
if (employee.IsNew)
dc.Employees.InsertOnSubmit(employee);
else
dc.Employees.Attach(employee, true); // <-- UPDATE
dc.SubmitChanges();
}

This second parameter is going to tell LINQ to SQL that it should treat this entity as modified so that it needs to be saved to the database. Now when I call SaveEmployee(), I get an exception when I call Attach() that says:

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.

What this means is that my database table does not have a timestamp column on it. Without a timestamp, LINQ to SQL can't do it's optimistic concurrency checking. No big deal, I'll go add timestamp columns to the Employees and Orders tables in the database. I'll also have to go into my DBML file and add the column to the table in there. You can either add a new property to the object by right-clicking on the object in the designer and selecting Add / Property, or you can just delete the object from the designer and then dragging it back on from the Server Explorer.

Now the DBML looks like this:

updated dbml

Now let's try calling SaveEmployee() again. This time it works. Here is the SQL that LINQ to SQL ran:


UPDATE [dbo].[Employees]
SET [LastName] = @p2, [FirstName] = @p3, [Title] = @p4, [TitleOfCourtesy] = @p5, [BirthDate] = @p6, [HireDate] = @p7, [Address] = @p8, [City] = @p9, [Region] = @p10, [PostalCode] = @p11, [Country] = @p12, [HomePhone] = @p13, [Extension] = @p14, [Photo] = @p15, [Notes] = @p16, [ReportsTo] = @p17, [PhotoPath] = @p18
WHERE ([EmployeeID] = @p0) AND ([Timestamp] = @p1)

SELECT [t1].[Timestamp]
FROM [dbo].[Employees] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[EmployeeID] = @p19)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [5]
-- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
-- @p2: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Buchanan]
-- @p3: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Steven]
-- @p4: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [Sales Manager]
-- @p5: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Mr.]
-- @p6: Input DateTime (Size = 0; Prec = 0; Scale = 0) [3/4/1955 12:00:00 AM]
-- @p7: Input DateTime (Size = 0; Prec = 0; Scale = 0) [10/17/1993 12:00:00 AM]
-- @p8: Input NVarChar (Size = 15; Prec = 0; Scale = 0) [14 Garrett Hill]
-- @p9: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]
-- @p10: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p11: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [SW2 8JR]
-- @p12: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [UK]
-- @p13: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [(71) 555-4848]
-- @p14: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [3453]
-- @p15: Input Image (Size = 21626; Prec = 0; Scale = 0) [SqlBinary(21626)]
-- @p16: Input NText (Size = 448; Prec = 0; Scale = 0) [Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree in 1976. Upon joining the company as a sales representative in 1992, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London. He was promoted to sales manager in March 1993. Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management." He is fluent in French.]
-- @p17: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p18: Input NVarChar (Size = 37; Prec = 0; Scale = 0) [http://accweb/emmployees/buchanan.bmp]
-- @p19: Input Int (Size = 0; Prec = 0; Scale = 0) [5]

Notice that it passed back all of the properties in the SQL statement -- not just the one that I changed (I only changed one property when I made this call). But isn't it horribly ineffecient to save every property when only one property changed?

Well, you don't have much choice here. Now there is another overload of Attach() that takes in the original version of the object instead of the boolean parameter. In other words, it is saying that it will compare your object with the original version of the object and see if any properties are different, and then only update those properties in the SQL statement.

Unfortunately, there's no good way to use this overload in this case, nor do I think you would want to. I suppose you could load up the existing version of the entity from the database and then pass that into Attach() as the "original", but now we're doing even more work -- we're doing a SELECT that selects the entire row, and then we're doing an UPDATE that only updates the changed properties. I would rather stick with the one UPDATE that updates everything.

31 Comments »

  1. How does the linq to sql classes know that the ‘Timestamp’ column is special? What if I wanted to name that column differently?

    Good post. Looking forward to hearing more about linq to sql in the future.

    Steve Horn — February 10, 2008 @ 10:41 pm

  2. Steve -

    When you’re in the LINQ to SQL designer and you select a property in an object, one of the options in the Properties window is “Time Stamp” (true/false). If you drag a table onto the designer and the column is of type timestamp, it will set this flag automatically for you.

    You can name the column whatever you want, that has no bearing on anything.

    Jon Kruger — February 11, 2008 @ 6:17 am

  3. Hi!,

    i’ve posted about inclusing Linq-to-Sql in N-Tier Applications,
    and found interesting the way you described the problem

    here is my article about it:
    http://blog.pampanotes.com/2008/02/first-thoughts-on-designing-linq.html

    Benjamin Eidelman — February 18, 2008 @ 8:35 pm

  4. [...] WCF Workflow Services Context Be careful with ServiceAuthorizationManager.CheckAccess() C# generics – parameter variance, its constraints and how it affects WCF Introduction to WCF Extensibility article published at LevelExtreme Magazine [...]

    Weekly crumbs #7 - Service Endpoint — February 18, 2008 @ 11:11 pm

  5. Thanks for your reply,
    Currently, we’re working on a solution to the disconnected-track-changing issue.
    I’ve seen that you propose a EntityBaseClass solution by adding a state property to the entity, we’ve been discusing between a similar option (maybe using an interface instead of base class), and a “Portable Change Set” implementation of EntitySet

    This two feature requires customization of the auto-generated code, and the good news are, that creating a custom code-generator isn’t as hard as it looks.

    We’re working on a code generator for dbml’s, and we will probably publish it (open sourced) when it’s more polished. By now, If you want we can share some implementation details.

    Regards,

    Benjamin Eidelman — February 19, 2008 @ 7:58 am

  6. LINQ to SQL In Disconnected/N-Tier scenarios: Saving an Object…

    You’ve been kicked (a good thing) – Trackback from DotNetKicks.com…

    DotNetKicks.com — February 24, 2008 @ 10:15 pm

  7. Great post! Please keep us posted on how you handle the Orders collection. I’m already familiar with concept, so I’m curious how you handle it (I don’t want to give you any ideas because I’m not confident that my technique is correct).

    Sean — February 26, 2008 @ 9:38 am

  8. Hi Jon,

    I like this! It works well, except in the scenario (like I got stuck in!) where the database table has INSERT/UPDATE triggers that test for certain columns being modified, and acting upon those changes. A trivial example:

    IF UPDATE (LastName)
    BEGIN
    — Do some logic based on this change
    END

    In the case of the UPDATE statement generated above, it always causes the LastName column to appear UPDATED. If I could have changed this logic, I would have, but I inherited a badly-built database…

    David Mc. — February 26, 2008 @ 10:56 am

  9. Some other links about DataContext:
    Rick Strahl tackled the Timestamp issue here:
    http://www.west-wind.com/WebLog/posts/151425.aspx

    And discusses lifetime management here:
    http://www.west-wind.net/WebLog/posts/246222.aspx

    Rob Conery — February 26, 2008 @ 1:18 pm

  10. John,

    I added this post to a list of LINQ to SQL tutorials @ http://rocksthoughts.com/blog/archive/2008/02/23/linq-to-sql-tutorials-articles-and-opinions.aspx

    While I love LINQ to SQL some of the ways they implemented features in the first version are a little inefficient like you stated in your post referring to the update.

    I wrote a post about something similar here: http://rocksthoughts.com/blog/archive/2008/02/19/linq-to-sql-v-1-0-hickups.aspx

    Chris Rock — February 26, 2008 @ 2:58 pm

  11. John,

    As promised, we published a custom code generator tool, that allows you to keep the O/R Designer GUI, but replace the code generation to allow truly POCOs, a disconnected N-tier change tracking (or even your own custom code generation), we published under GPL here:
    http://www.codeplex.com/ULinqGen

    And I published some details about our implementation of disconnected change tracking in this post:
    http://pampanotes.tercerplaneta.com/2008/03/implementing-n-tier-change-tracking.html

    It would be really interesting sharing some comments on this.

    Regards,

    Benjamin Eidelman — March 12, 2008 @ 5:44 pm

  12. Hi,

    I’ve tried the steps you described here to save a disconnected LinqToSql entity, but when I try to attach the disconnected entity to a new DataContext instance I get the following exception:

    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.

    Here’s the code I’ve used to do this:

    LinqTestDataContext dc2 = new LinqTestDataContext();

    dc2.Companies.Attach(company, true);

    dc2.SubmitChanges();

    Note that I’ve loaded the company entity using a different datacontext instance and I’ve also added a TimeStamp field on it.

    Regards

    Marius Puscas — March 28, 2008 @ 4:51 am

  13. Hi Jon,

    Interesting post about Linq to SQL. I thought I’ll let you know about my product that actually can work in a disconnected environment and also allows you to do inserts/updates including complete control over what fields are included in such operations. There are lot of other features that allow the developer to create high performance applications very quickly and easily.

    For example, if you have an “OrderItems”, an “Orders” object and a “Customers” object, the Quick Objects framework doesn’t make 3 calls to database, it can do it in one and you still can decide what fields should be selected. Same control over concurrency, you are not forced to perform concurrency check on the entire object and you can choose what fields should be used for concurrency check (if you want – very easily).

    Honestly, it is the only ORM/framework with this kind of flexibility and control. Best of all it is very light weight and has a code generation built in so a developer is ready to code in just a few minutes.

    Well, you can see it at http://www.quickobjects.com and if you like to review it please let me know and I can get you a complimentary copy ;)

    Thanks,
    Ish

    Quick Objects = Fastest Way To Powerful Applications

    Ish Singh — April 11, 2008 @ 11:09 pm

  14. I did exactly what you said but I´m still getting :
    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.

    Antonio Jr — April 25, 2008 @ 3:12 pm

  15. LINQ over webservices is a nightmare. I haven’t managed to get any updating working over webservice, and haven’t found anyone non-third party solutions. Any chance you could update this tutorial demonstarting an update over web service? Timestamping does not serialise across web service, they must be specified as byte[] to do so, then the conversion back is a major headache.

    Edward — May 13, 2008 @ 4:29 am

  16. An update to my previous rant. This seems to have solved the timestamp issue for web services it (for a top level entity), will try associative next. Basically – set the TimeStamp property on the DataContext Entity to Private, then extend the class you wish to detach via webservices with a single property viz:
    public partial class MyEntity
    {
    public string VersionStamp
    {
    get { return _TimeStamp.TimestampToString(); }
    set { _TimeStamp = value.StringToTimestamp(); }
    }
    }

    The TimeStamp to String and Back Methods can be found here….http://geekswithblogs.net/AndrewSiemer/archive/2008/02/11/converting-a-system.data.linq.binary-or-timestamp-to-a-string-and-back.aspx

    Edward — May 13, 2008 @ 10:54 am

  17. @Edward,

    What kind of web services are you using? We’re using LINQ to SQL with WCF web services and it has no problem with the timestamps.

    Trying to do an update over web services isn’t trivial. The biggest challenge is dealing with entities that have properties that return lists (EntitySet) because you don’t want to pass the entire list back when you want to save the entity when you didn’t change anything in the EntitySet. You also have to deal with re-attaching entities to the DataContext using the Attach() method, which also has some issues that you have to deal with.

    I’ve been meaning to write a post about these things, but I’ve been having trouble finding time to write. :) Check back later and maybe I’ll write something up.

    Jon

    Jon Kruger — May 13, 2008 @ 5:04 pm

  18. Edward

    We have no released a Community Edition of Quick Objects which is free for commercial or non-commercial uses. The best thing is that the free version can be used in WCF. It truly is very easy to use and the client application can either use proxy class or reuse the business objects. Take a look at couple of WCF videos on our website to see it for yourself.

    Good luck,
    Ish

    Ish Singh — May 28, 2008 @ 3:31 pm

  19. Thanks Edward.
    I ran into the exact issue. When added the timestamp i got a serialization error. I’ve implemented your suggestion and it works like a charm.

    Jarem — June 8, 2008 @ 10:04 am

  20. I have written an article related to this on codeproject

    Please have a look and post your valuable comments / suggestions there.

    Sameer — June 10, 2008 @ 5:22 am

  21. Thanks for the great article. This is what I’m looking for.

    mrnon — October 17, 2008 @ 4:47 pm

  22. I have many tables and is not practical to add a time stamp to each one of them. Isn’t there a different way to implement disconected linq entities?

    Fer — January 20, 2009 @ 9:50 pm

  23. @Fer,

    I’m sure you could keep track of changes manually and then compare the version in the database to the original version of your entity (before you made changes to it) but that would probably be really slow and take way longer to implement than adding the timestamps. I would just bite the bullet and add the timestamps and let LINQ to SQL handle the concurrency checks.

    Jon Kruger — January 21, 2009 @ 8:18 am

  24. Hey Jon, nice post. I just wanted to add that Linq checks optimistic concurrency on each column by default using the column’s Update Check property. So you can avoid altering the tables by adding the timestamp column. Regards.

    Nico — March 16, 2009 @ 11:46 pm

  25. You know, the thing about SQL is, that there is virtually nothing that can replace it.

    Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

    SQL Tutorials — April 30, 2009 @ 9:00 pm

  26. Does anyone know if there is another language or set of commands beside SQL for talking with databases?

    I’m working on a project and am doing some research thanks

    SQL Tutorials — April 30, 2009 @ 10:04 pm

  27. Great tip on the IsNew. Works like a champ

    David Adams — June 7, 2009 @ 3:57 am

  28. hi ,
    as jon said
    “The biggest challenge is dealing with entities that have properties that return lists (EntitySet) because you don’t want to pass the entire list back when you want to save the entity when you didn’t change anything in the EntitySet.”

    is he posting any solution to that issue?

    jamal — April 28, 2010 @ 9:44 am

  29. hi again ,
    can i use datetime type of sql server and not timestamp type . i already have update_On field of type datetime , in my data model.

    jamal — April 28, 2010 @ 9:47 am

  30. [...] http://jonkruger.com/blog/2008/02/10/linq-to-sql-in-disconnectedn-tier-scenarios-saving-an-object/ [...]

    One WCF Service to Rule them All? (SOAP w https, oData, JSON, POX, etc…) | DeveloperQuestion.com — October 20, 2010 @ 2:22 pm

  31. Maintain state of a dynamic variable:
    @Jon:
    Thanks for such a great article! I actually came here looking to solve a problem, thinking that disconnected model may be the solution I want, but it doesn’t seems the case.
    I Hope you can help me with that?
    So, the problem is that :

    I am using linq and thus I have a few “var” types object created dynamically, as in

    var Order = CustomerDataContext.Order.Where(e => e.OrderPending)

    and many others like this, so the problem is how can I maintain this “var” across method calls, I am using

    ThreadPool.QueueUserForWorkItem(ProcessOrder, var)

    but this method (ProcessOrder) accepts object (as per specification of the delegate as specified in QueueUserForWorkItem) and I cannot cast the back the parameter back into Order type. Also, since its dynamic it cannot even be declared as static, so that I may maintain state, so what should I do? How should I maintain this var, or if I cannot, then what is the other way to do what I am doing?

    The point is, after I filter, and pass this filtered record to some other method, and not only (QueueUserForWorkItem) how do I get its same recordset or say same filtered list back, also if the method expects an argument of type object, as you cannot convert it into Order type because that var was just local variable to the caller function.

    I can still create a new Order, but that would destroy the whole purpose of filtering some records and then passing those records to another method to be processed? If we create a new Order in other method say, (ProcessOrder) in this case, it will be a new Order type and not the one that was filtered on some condition and passed as parameter to this method. I hope I made my point clear.

    Any help would be really appreciated.

    Razor — March 3, 2012 @ 12:53 am

Leave a comment





SERVICES
SOFTWARE SOLUTIONS
I have over 10 years of software development experience on several different platforms (mostly Ruby and .NET). I recognize that software is expensive, so I'm always trying to find ways to speed up the software development process, but at the same time remembering that high quality is essential to building software that stands the test of time.
PROJECT LEADERSHIP
I have experience leading and architecting large Agile software projects and coordinating all aspects of a project's lifecycle. Whether you're looking for technical expertise or someone to lead all aspects of an Agile project, I have proven experience from multiple projects in different environments that can help make your project a success.
AGILE COACHING
I believe that Agile processes and tools should be applied with common sense. I've spent the last 6 years working on Agile projects as a consultant in many different environments, both in leadership roles and as a practitioner doing the work. I can help you find out how Agile can work best in your organization, not just apply a prescriptive process.
TEST DRIVEN DEVELOPMENT TRAINING
TDD Boot Camp is a hands-on, three day, comprehensive training course that will teach you all of the skills, tools, frameworks that you will need to use test-driven development to develop real world .NET applications. If you're not looking for something that intensive, check out the the half-day version.
Have any questions? Contact me for more information.
PRESENTATIONS
The Business of You: 10 Steps For Running Your Career Like a Business
From CONDG 2012, Stir Trek 2014
From Stir Trek 2013, DogFoodCon 2013
From Stir Trek 2012, QA or the Highway 2014
(presented with Brandon Childers, Chris Hoover, Laurel Odronic, and Lan Bloch from IGS Energy) from Path to Agility 2012
(presented with Paul Bahler and Kevin Chivington from IGS Energy)
From CodeMash 2011
An idea of how to make JavaScript testable, presented at Stir Trek 2011. The world of JavaScript frameworks has changed greatly since then, but I still agree with the concepts.
A description of how test-driven development works along with some hands-on examples.
From CodeMash 2010
From CodeMash 2010