Another limit when using Linq To SQL is the SQL translation of boolean operation.
For exemple, when writting:
from s in sTable
select new{
BoolValue=BoolValue1&&BoolValue2
}
the corresponding SQL interpretation will be like that:
select BoolValue=case when (BoolValue1 and BoolValue2) then 1
when !(BoolValue1 and BoolValue2) then 0
else NULL end
from sTable
However, the wished result is 1 or 0, not NULL .
Be aware of that when processing boolean operation…
To implement a Linq To SQL Oriented solution, there are many ways to construct your application. But only one interested me : the Context per Unit of Work Architecture.
When using Linq To SQL, you need to instanciate a Context which contains your entities tables and allow you to apply query methods. But when your application is construct with many layers, you only can pass Context through methods to keep the same context during your business operations. It is the same problem when a transaction is required(managed by Linq To SQL Context though optimistic transaction).
When using the Unit of Work architecture proposed by Microsoft and few developpers, most of the common operation is easier. Then, I’ll explain the main idea :
I’ll suppose that we have a 2 layers application, called Web & BLL(contains DAL through Linq To SQL Schema).
We want to :
The first step is to create a controller class in the BLL layer to access to the context.
public class MyDataContext
{
public static IDataContextStorage MyDataContextStorage;
public static LinqContext Current
{
get
{
return MyDataContextStorage.Current;
}
set
{
MyDataContextStorage.Current = value;
}
}
public static void Save()
{
Current.SubmitChanges();
}
}
And create the following interface:
public interface IDataContextStorage
{
LinqContext Current { get; set; }
}
Now, we have a controller which can be called by the Web and BLL Layer. Accessing from web is not a good practice, so we can change to internal if we want to restrict access to BLL only and set the Current Property type return to object. However, I don’t like to cast each time I need my context, so a leave it like that.
MyDataContext is the wrapper to access to the Context. but we now need to store your context during your HTTP Request. We need to create the following class that allow us to store the context and access it for each user :
public class HttpDataContextStorage:IDataContextStorage
{
public MyBLL.LinqContext Current
{
get {
if ((MyBLL.LinqContext)HttpContext.Current.Items["DataContext"] == null)
HttpContext.Current.Items["DataContext"] = new MyBLL.LinqContext();
return (MyBLL.LinqContext)HttpContext.Current.Items["DataContext"]; }
set { HttpContext.Current.Items["DataContext"] = value; }
}
}
This class need to be placed in App_Code in Web layer(there are some HttpContext reference, so…).
For each application request, we are going to create a Context and destroy it when request is finished. Write in your global.asax.cs:
void Application_BeginRequest(object sender, EventArgs e)
{
MyDataContext.MyDataContextStorage = new HttpDataContextStorage();
}
void Application_EndRequest(object sender, EventArgs e)
{
MyDataContext.Current.Dispose();
}
Ok, all is defined.
when we need to execute a Linq to SQL Query, we only need to write :
MyDataContext.Current.Users.Where(u=>u.Id==1);
“Users” is a entity table for this sample.
When saving is required, do: MyDataContext.Save() or MyDataContext.Current.SubmitChanges()
With this strucutre, Linq Context is always available when you need it and allow to perform transaction through many methods calling.
However, some issues are known, as :
This Linq To SQL Context Architecture is a simple sample to show the main idea. Sure, It can be improved to restrict the layers access, check if we use the HTTP Linq Context or a New Context for asynchronous tasks…
Reminder: A Linq Overview Ticket is available here to see others architectures and statistics.
When the Linq word begin to spread to the developper’s world, I immediatly inform me about what is it and what can I do with “that”. After trying Linq To Object, Linq To XML, Linq To SQL and Linq To Entity, I was convinced that it was the right path to follow. But for Data management, I was preferring Linq To SQL because Linq To Entity utilisation was boring(Stored Procedure to write).
After SP1, Linq To Entity was not the same. So I studied it more deeper… And some good things falls.
If I must define what Linq To Entity can bring you compared to Linq To SQL, I will say:
The real purpose of Linq To Entity is to define an object conception that will not necessary match to your database shema relationship. A real good thing when implementing your business layer and rules.
In this example, the class UserDB is a copy of the User Table(with Linq To SQL or others code generation tools).
With linq to Entity, we can build your object model with inheritances and rules(User is defined as abstract). So we cannot used a user directly but we need to use the child classes(not abstract). The mapping to get an administrator user is done by the model by auto-filling the required filters. Magic…
I invite the Linq To SQL users to gather more information about Linq To Entity and see the added functionnalities.
With the power of Linq language, it’s easy to use Linq To SQL to build 95% of the data access interrogation.
But you will notify that 2/3 of your data access methods is always the same, as GetById, GetAll, Insert, DeleteById…
So Microsoft staff has written a class named GenericController that allow you to centralize all standard methods in one class. You only need to inherit from it and a good part of your job is done.
http://code.msdn.microsoft.com/multitierlinqtosql
If you look more deeper in this pack, you will find some other classes written to instanciate one LinqContext by Query or a Linq To SQL Debugger(see also my ticket on DebugWriter).
When you begin to write complex Linq To SQL Queries with many IQueryable associations, it must be difficult to catch all the SQL generated parts by Linq To SQL. That’s why, after searching on the web some information, I have found this little class that can be usefull : DebugWriter.
Associate a new instance of DebugWriter with your LinqContext Log Property and all Linq To SQL Queries will be displayed in the VS Output during Debug Mode.
public class DebugWriter : TextWriter
{
private bool isOpen;
private static UnicodeEncoding encoding;
private readonly int level;
private readonly string category;
/// <summary>
/// Initializes a new instance of the <see cref=”DebuggerWriter”/> class.
/// </summary>
public DebugWriter()
: this(0, Debugger.DefaultCategory)
{
}
/// <summary>
/// Initializes a new instance of the <see cref=”DebuggerWriter”/> class with the specified level and category.
/// </summary>
/// <param name=”level”>A description of the importance of the messages.</param>
/// <param name=”category”>The category of the messages.</param>
public DebugWriter(int level, string category)
: this(level, category, CultureInfo.CurrentCulture)
{
}
/// <summary>
/// Initializes a new instance of the <see cref=”DebuggerWriter”/> class with the specified level, category and format provider.
/// </summary>
/// <param name=”level”>A description of the importance of the messages.</param>
/// <param name=”category”>The category of the messages.</param>
/// <param name=”formatProvider”>An <see cref=”IFormatProvider”/> object that controls formatting.</param>
public DebugWriter(int level, string category, IFormatProvider formatProvider)
: base(formatProvider)
{
this.level = level;
this.category = category;
this.isOpen = true;
}
protected override void Dispose(bool disposing)
{
isOpen = false;
base.Dispose(disposing);
}
public override void Write(char value)
{
if (!isOpen)
{
throw new ObjectDisposedException(null);
}
Debugger.Log(level, category, value.ToString());
}
public override void Write(string value)
{
if (!isOpen)
{
throw new ObjectDisposedException(null);
}
if (value != null)
{
Debugger.Log(level, category, value);
}
}
public override void Write(char[] buffer, int index, int count)
{
if (!isOpen)
{
throw new ObjectDisposedException(null);
}
if (buffer == null || index < 0 || count < 0 || buffer.Length – index < count)
{
base.Write(buffer, index, count); // delegate throw exception to base class
}
Debugger.Log(level, category, new string(buffer, index, count));
}
public override Encoding Encoding
{
get
{
if (encoding == null)
{
encoding = new UnicodeEncoding(false, false);
}
return encoding;
}
}
public int Level
{
get { return level; }
}
public string Category
{
get { return category; }
}
}
Sparing some time is the key to efficiency…
The first time I was using Linq To SQL, I was frustrated by the fact VS2008 did not integrated the more simple fonctionnality called : “Synchronize DBML Shema with Database”. Just removing my classes from DBML Shema and perform a Drag&Drop from Server Explorer to schema was to much for me ^^.
So I wrote a simple VS 2008 plug-in which allow with 2 clics a synchronization between my database and My DBML Schema.
Here the installer :
Rename the .ppt file in .zip file.
After installing, the add-in file must be define in userProfile/documents/VS2008/Addins/ folder and the assembly, for example, in program files folder.
If problems comes, be sure to check the path of the assembly in the .addin file(userProfile). It must reference the assembly in the program files(in this case).
The add-in is too simple that I haven’t done a User Guide. I let you eval 
After using Linq To SQL queries, I was quickly confronted to sort my data with a string expression provided, for example, by a gridview when a user require it. At this moment, I understood that the strongly typed system used with Linq cannot help me for this task… Understanding the fact that allowing a string expression representing an object property is contrary to Linq purpose, I began to search with our best friend ”Google” a soluce to resolve quickly my problem. But the best that I can obtain was a “switch case solution” which, according to the string property name, build the linq query sort filter with the associated object property. Immediatly, I guess my switch case code with 8-10 columns… No…
So, I started to write an extended method “OrderBy” allowing to take a string sort expression. Here the result.
/// <summary>
/// Extends method which allow to sort by string field name.
/// Allow to use a relative object definition for sorting (ex:LinkedObject.FieldsName1)
/// </summary>
/// <typeparam name=”TEntity”>Current Object type for query</typeparam>
/// <param name=”source”>list of defined object</param>
/// <param name=”sortExpression”>string name of the field we want to sort by</param>
/// <returns>Query sorted by sortExpression</returns>
public static IQueryable<TEntity> OrderBy<TEntity>(
this IQueryable<TEntity> source, string sortExpression) where TEntity : class
{
var type = typeof(TEntity);
// Remember that for ascending order GridView just returns the column name and
// for descending it returns column name followed by DESC keyword
// Therefore we need to examine the sortExpression and separate out Column Name and
// order (ASC/DESC)
string[] expressionParts = sortExpression.Split(‘ ‘); // Assuming sortExpression is like [ColumnName DESC] or [ColumnName]
string orderByProperty = expressionParts[0];
string sortDirection = “ASC”;
string methodName = “OrderBy”;
//if sortDirection is descending
if (expressionParts.Length > 1 && expressionParts[1] == “DESC”)
{
sortDirection = “Descending”;
methodName += sortDirection; // Add sort direction at the end of Method name
}
MethodCallExpression resultExp = null;
if (!orderByProperty.Contains(“.”))
{
var property = type.GetProperty(orderByProperty);
var parameter = Expression.Parameter(type, “p”);
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
resultExp = Expression.Call(typeof(Queryable), methodName,
new Type[] { type, property.PropertyType },
source.Expression, Expression.Quote(orderByExp));
}
else
{
Type relationType = type.GetProperty(orderByProperty.Split(‘.’)[0]).PropertyType;
PropertyInfo relationProperty = type.GetProperty(orderByProperty.Split(‘.’)[0]);
PropertyInfo relationProperty2 = relationType.GetProperty(orderByProperty.Split(‘.’)[1]);
var parameter = Expression.Parameter(type, “p”);
var propertyAccess = Expression.MakeMemberAccess(parameter, relationProperty);
var propertyAccess2 = Expression.MakeMemberAccess(propertyAccess, relationProperty2);
var orderByExp = Expression.Lambda(propertyAccess2, parameter);
resultExp = Expression.Call(typeof(Queryable), methodName,
new Type[] { type, relationProperty2.PropertyType },
source.Expression, Expression.Quote(orderByExp));
}
return source.Provider.CreateQuery<TEntity>(resultExp);
}
/// <summary>
/// Allow to add another sorting on a query with a string representation of the field to sort by.
/// </summary>
/// <typeparam name=”TEntity”>Current Object type for query</typeparam>
/// <param name=”source”>list of defined object</param>
/// <param name=”sortExpression”>string name of the field we want to sort by</param>
/// <returns>Query sorted by sortExpression</returns>
public static IOrderedQueryable<TEntity> ThenBy<TEntity>(
this IOrderedQueryable<TEntity> source, string sortExpression) where TEntity : class
{
var type = typeof(TEntity);
// Remember that for ascending order GridView just returns the column name and
// for descending it returns column name followed by DESC keyword
// Therefore we need to examine the sortExpression and separate out Column Name and
// order (ASC/DESC)
string[] expressionParts = sortExpression.Split(‘ ‘); // Assuming sortExpression is like [ColumnName DESC] or [ColumnName]
string orderByProperty = expressionParts[0];
string sortDirection = “ASC”;
string methodName = “ThenBy”;
//if sortDirection is descending
if (expressionParts.Length > 1 && expressionParts[1] == “DESC”)
{
sortDirection = “Descending”;
methodName += sortDirection; // Add sort direction at the end of Method name
}
MethodCallExpression resultExp = null;
if (!orderByProperty.Contains(“.”))
{
var property = type.GetProperty(orderByProperty);
var parameter = Expression.Parameter(type, “p”);
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
resultExp = Expression.Call(typeof(Queryable), methodName,
new Type[] { type, property.PropertyType },
source.Expression, Expression.Quote(orderByExp));
}
else
{
Type relationType = type.GetProperty(orderByProperty.Split(‘.’)[0]).PropertyType;
PropertyInfo relationProperty = type.GetProperty(orderByProperty.Split(‘.’)[0]);
PropertyInfo relationProperty2 = relationType.GetProperty(orderByProperty.Split(‘.’)[1]);
var parameter = Expression.Parameter(type, “p”);
var propertyAccess = Expression.MakeMemberAccess(parameter, relationProperty);
var propertyAccess2 = Expression.MakeMemberAccess(propertyAccess, relationProperty2);
var orderByExp = Expression.Lambda(propertyAccess2, parameter);
resultExp = Expression.Call(typeof(Queryable), methodName,
new Type[] { type, relationProperty2.PropertyType },
source.Expression, Expression.Quote(orderByExp));
}
return (IOrderedQueryable<TEntity>)source.Provider.CreateQuery<TEntity>(resultExp);
}
During my migration from .NET 2.0 to .NET 3.0/3.5, my first tought was : ‘With .NET 3.5, I can used Linq To SQL and stop to bore me with all the code generation tools”. I don’t want to crush the glory of tools like Raptier or Code Smith, but an ORM produce by Microsoft can be better in generation time(class generation and integration) and simplicity. It was true(for common uses). But better than that. Linq offers many others functionalities(strongly typed, composite queries, relashionship between objects and many more). And I began to forget all about others problems when the Graal was not here…
After a month of research, I have started to write a document on my Linq feedback and I share it today
