It’s hard not to fall in love with LINQ to SQL. It provides a type safe, powerful and extremely flexible way to implement data access in .NET applications. And it looks so easy to use in those nifty Microsoft evangelist presentations!

Unfortunately, after having a closer look at LINQ, I found that using LINQ in a multi-tier application can be quite a struggle. This article shows the typical pitfalls of implementing the data layer with LINQ to SQL and provides an simple, convenient and flexible way to circumvent most of them. (more…)

Since I am currently working on a multi-tier ASP.NET application, I frequently have to display Linq entities in data-driven controls such as GridView, DetailView, etc.

Most of my Linq entities have a version attribute to speed up database operations, and sometimes the entities are not only displayed, but also updated and saved back to the database. The first time I tried this, I got the following exception:

A first chance exception of type ‘System.Data.Linq.ChangeConflictException’ occurred in System.Data.Linq.dll

The reason for this is that I was not displaying the version anywhere in the Detailsview. When saving the entity back to the database, the ObjectDataSource created a new entity with default values for all properties that were not bound to any datafields in the DetailsView. So, the entity was re-instantiated with Version==null and when trying to update the database table for the Linq entity, Linq was looking for a row with a 0 version column.

I thought I might work around this by simply version property of the Linq entity to an invisible Column in the Detailsview. But that only gave me a new exception when trying to update the entity:

Sytem. InvalidOperationException: Cannot convert value of parameter ‘Version’ from ‘System.String’ to ‘System.Data.Linq.Binary’

The reason for this is that the Version attribute in Linq entities is of the type System.Data.Linq.Binary, which can be converted to a string, but not back from string to System.Data.Linq.Binary.

It took me quite a while to find at least some workaround for this problem. A working – but somewhat awkward – solution is to create a VersionString propery in each entity:

public partial class Customer
{
public string VersionString
{
get { return _Version.TimestampToString(); }
set { _Version = value.StringToTimestamp(); }
}
}

public static class DateTimeExtensions
{
public static string TimestampToString(this Binary binary)
{
return Convert.ToBase64String(binary.ToArray());
}
public static Binary StringToTimestamp(this string s)
{
return new Binary(Convert.FromBase64String(s));
}
}

Then I bound the VersionString to a hidden field in the DetailsView and the problem was gone. But this solution is not ideal since it requires the VersionString property to be defined for every single Linq entity type that uses a timestamp.

But wait, there’s a much easier way, I stumbled across this solution while playing around with Detailsview a few days ago: Simply add the Version propery to the DataKeyNames property of the DetailsGrid. For example, if your DataKeyNames propery is usually DataKeyNames=”ID”, write DataKeyNames=”ID,Version” instead.

This way the DetailsView serializes the Version property to it’s view state and deserializes it again when the entity has to be recreated. No need to fiddle with the VersionString approach above.