N-Tier, Entities, Linq, and Optimistic Concurrency... OH MY!

Introduction

Everyone knows that when developing an enterprise level software solution it is best to layer your application to keep core logic centralized and abstracted away from your presentation layer, and direct access to the storage device of your choice abstracted into its own layer.

If you are a newcomer to linq or haven't worked in the n-tier world, you are browsing the net today because you hit the same wall most developers hit when it comes to abstracting your data context away from your linq 2 sql classes and that glorious n-tier approach.

Microsoft pulled some real bone head moves that really makes developing an n-tier application with linq more than difficult, however, the benefits of linq and entities out-weigh the pains of your blood, sweat, and tears research today because I have your answer (Unless you would rather keep plugging away in the ADO world).

Optimistic Concurrency

First lets start with a term and feature that makes it all possible, optimistic concurrency. This is defined as: "In the field of relational database management systems, optimistic concurrency control (OCC) is a concurrency control method that assumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back." (You can read more on wikipedia: http://en.wikipedia.org/wiki/Optimistic_concurrency)

Row Versioning is accomplished in SQL Server 1 of 2 ways.

• The first, which I recommend, is the use of a TIMESTAMP column on each of your tables (You will see why shortly). This is an auto-generated field in SQL Server 2008 and auto-updated on row edits. SQL Server 2008 handles all of the heavy lifting here (All you guys and gals stuck in the SQL Server 2005 or prior world are out of luck, see the second approach). If you are going this route skip ahead to the "What do I do with Optimistic Concurrency and Linq 2 SQL Classes?" section.
  
  
• The second approach is the use of a DATETIME column on each of your tables. Most people would name a field of this type "LAST_UPDATED" or something equivalent. SQL Server in this scenario does none of the heavy lifting for you, so I'll explain the constraints in what follows:
  

First, when you define your table you have to set a DEFAULT value.

CREATE [YourTableName]

(
  --Your columns

  LAST_UPDATE DATETIME NOT NULL DEFAULT(GETDATE())

)
Second, you have to define a trigger as follows:

CREATE TRIGGER trig_rv[YourTableName]

ON [YourTableName]

  AFTER UPDATE AS

  BEGIN

    UPDATE [YourTableName]

    SET LAST_UPDATE = getdate()

    WHERE

      [YourTableName].[YourPrimaryKey] IN (SELECT [YourPrimaryKey] FROM inserted);

  END  
GO

That's it, now you can catch up to your comrades on SQL Server 2008 in what follows.

What do I do with Optimistic Concurrency and Linq 2 SQL Classes?

Ok, so now you have jumped through the SQL Server hoops, next you need to jump through the Linq to SQL designer hoops (This isn't that painful, and I have a "Find & Replace" solution that accomplishes the same thing).

First, you have to understand a few of those magical [Column(...)] tags you see all through your Linq to SQL classes.

• UpdateCheck (Needs done for EVERY column)
  This is the magic that makes the Optimistic Concurrency work. It has the following values:
  -Never (Never checks if the field has been changed)
  -Always (Always checks)
  -WhenChanged (Only checks when changed)
  
  

  The magical keyword you are looking for here is "UpdateCheck=UpdateCheck.Never", this goes on ALL of your table columns. If you are using auto-generated Linq 2 SQL entities you can do "Find & Replace"
  Find: [Column(S
  Replace: [Column(UpdateCheck=UpdateCheck.Never,S
  Finally, Replace All.

• AutoSync (Only needs updated for the Row Version column)
  I'm not going to go over all of the values for this, but I will outline why and what makes optimistic concurrency work. "AutoSync=AutoSync.Always" This will sync the value of your Row Version column to the database. You only put this on the row version column for each table. This is part 2 of what makes your optimistic concurrency work
• IsDbGenerated=true (Only needs updated for the Row Version column)
• IsVersion=true (Only needs updated for the Row Version column)
• Now I told you that I would tell you a nifty little cheat to do a Find & Replace to bulk update this in your designer.cs file. For starters, I hope you named your row version column the same for every table, if not you will be doing this part manually. (Those of you using Timestamp, double check but these are likely already set for you)
  Find: Copy the entire [Column(...)] tag of any of your row version properties. If you named them all the same, this is a walk in the park.
  Replace: [Column(UpdateCheck = UpdateCheck.Never, Storage="_<<YOURCOLUMNNAME>>", AutoSync=AutoSync.Always, DbType="DateTime NOT NULL", IsDbGenerated=true, IsVersion=true)]
• Ta-da, you have now Enabled Optimistic Concurrency. I'd suggest you keep reading, however most of you will jump out and go try it. It's not exactly as straight forward as you would think. The following sections explain how-what-why you have to do what you have to do.

C#, Linq, and Optimistic Concurrency

There are a few tricks to get those annoying "An attempt to Attach and Entity that is not new..." errors to go away, and the whole host of other errors you will go through reading the quick hit notes on everyone else's blog.

Detatching objects from their associated EntityRef objects

• Type tables are the main offender here. This is where the magic of partial classes works wonderfully. Say you have the following entities.
  
  User
  int UserID
  EntityRef _UserType
  EntityRef _Country
  EntitySet
  
  
  _Addresses
  //Other data, doesn't matter
  Address
  int AddressID
  EntityRef _User
  EntityRef _AddressType
  //Other data, doesn't matter
  
  You will first start by making a matching partial class in the same namespace as the model.
  
  public partial class User
  {
  public void DetachAll()
  {
  this._Country = default(EntityRef<Country>);
  this._UserType = default(EntityRef<UserType>);
  if(this._Addresses.HasLoadedOrAssignedValues)
  foreach(Address a in this._Addresses)
  a.DetatchAll();
  }
  }
  
  public partial class Address
  {
  public void DetachAll()
  {
  this._AddressType = default(EntityRef<AddressType>);
  }
  }

This will allow you to waterfall detach objects upon saving the parent object. You will need to detach EntityRef objects prior to attaching them to any active data context. This is the first key that you will find as a stumbling block in your journey to a perfect n-tier linq implementation. (The only EntityRef you leave attached is the reference to the parent object. In the instance of the Address object, it will have an EntityRef which you want to leave set for singular inserts, updates, etc.)

• Next, any object you get from the database you must detach from the datacontext. (There might be better ways of doing this, however I have not found any yet. Any time an object is attached to a data context, it must be detached to be updated again.)
  
  We will take a GetUserByID(int ID) function as an example, which would be a standard method to have in a business layer.
  
  public User GetUserByID(int ID, bool AttachChildren)
  {
  User u;
  using(YourDataContextObject data = new YourDataContextObject())
  {
  //This line makes the Entity think it is new, remember that annoying error that
  //was making you pull your hair out. This is the solution.
  
  data.ObjectTrackingEnabled = false;
  
  if(AttachChildren)
  {
  //This will attach your children objects to the parent, without this
  //they assume lazy loading. Which in n-tier is not what we want.
  DataLoadOptions dlo = new DataLoadOptions();
  dlo.LoadWith<User>(x => x.Addresses);
  dlo.LoadWith<Address>(x => x.AddressType);
  
  
  

  data.LoadOptions = dlo;

  }

  
  //Forgive me for not making this into standard Linq syntax, blog spot isn't exactly easy

  //to write code in. You will grow to love the Linq methods anyway.

  u = data.Users.SingleOrDefault(x => x.UserID == ID);

  }

  return u;

  }

  
  

That's all folks!
That wraps up today's post regarding n-tier linq applications. There can be a LOT more regarding tricks to make life easier in Save methods to handle Insert, Update, and Delete in 1 method.. but that's really outside the scope of today's post.

If you are looking for IT solutions for your businesses, would like to provide feedback, or give us a referral for new business opportunities (We provide profit sharing on referrals), you can visit our website at www.NightOwlCoders.com.

~Aqua~