Data manipulation with OR mappers usually ends up with records being to be pulled back from the database to the business logic tier or client, changes applied and record-based update statements generated by the OR mapper and sent back to the db server. This is fine when working with a small number of records or when the data is needed in a higher tier for the update to take place (e.g. a user editing a customer record).
However, there are situations where this is a less-than-ideal way of working with the data. When a large number of records need to be updated, e.g. all products in a category discontinued, seat “2A-2C” blocked on all future flights etc the data is usually not needed in the application layers. Instead, set based operations done directly in the database are the most efficient way to do these operations. Many ORMs, including Linq-to-SQL and Entity Framework do not support set-based operations so set-based operations instead result in the above mentioned record-based operations with ensuing unnecessary network traffic, extra work for the database server etc.
When using Linq-to-SQL out of the box there are two ways to achieve set-based operations where an update, delete or “insert into … select … from …” statement will be executed on the database without actually returning any data:
- Stored procedures; the set based update/delete/insert is added to a stored procedure, the stored procedure is in turn added to the model and called as a method on the data context.
- SQL-in-strings; the good old method of concatenating a SQL statement in a string and running it using ExecuteCommand.
Both methods work fine, but the drawback is that the language integration and LINQ flexibility is lost when using either of the methods.
Wouldn’t it be neat with an extension allowing set-based updates, deletes etc based on the L2S-generated entity classes?
I’m thinking something along the lines of this. I’m going to use Northwind as the example here instead of the res system db just to make the examples more simple to follow for everyone familiar with Northwind. Say we want to discontinue all products in a product category:
using (NorthwindDBML.DataClasses1DataContext dc = new DataClasses1DataContext())
{
dc.Update<Product>(
(where => where.Category.CategoryID == 1),
(set => set.Discontinued == true)
);
}
The first lambda expression passed to the Update method is the where clause for the update statement, and the second lambda defines the updates that will take place. The final result should be a SQL update statement along the lines of:
update dbo.Products set [Discontinued] = 1 where ProductID in (SELECT [t0].[ProductID]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]
WHERE [t1].[CategoryID] = @p0)
Another example, say we want to add the IDD prefix “+49″ in front of the phone number for all German customers if they don’t already have the it. That would be expressed as:
dc.Update<Customer>(
(where => where.Country == "Germany" && !where.Phone.StartsWith("+49")),
(set => set.Phone == "+49" + set.Phone),
LockModeEnum.Row);
…and would result in a SQL update statement like this:
update dbo.Customers with (rowlock) set [Phone] = (@p0 + [Phone]) where CustomerID in (SELECT [t0].[CustomerID]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[Country] = @p1) AND (NOT ([t0].[Phone] LIKE @p2)))
To make it play nice, it takes a parameter for locking hints too. Isn’t that neat?
As already established, this is not supported in Linq-to-SQL out of the box. (and not supported in entity framework at all). So do we have to wait for Microsoft to maybe add that in a future version? No, won’t work. They already said that entity framework is their “preferred” data access technology despite L2S being more popular among their user base than EF so I don’t think they will spend time and money on implementing something like this.
Instead, I played around a bit and came up with the following. This is just a first version so there are probably bugs in it. In addition, if you have tables using reserved keywords such as “from”, “where” etc as the table-name or in column names then it will break. It may also break when used with future versions of L2S (if there will be any). That said, here’s the first implementation of set-based language integrated update statements using Linq-to-SQL data contexts and Linq-to-SQL generated classes. (delete, “insert into … select … from …” will come in a future version).
Enjoy:
//Linq-to-SQL set-based operations. Copyright (c) 2008, Huagati Systems Co.,Ltd. ( www.huagati.com )
//DISCLAIMER: Use at your own risk, this is a code sample provided as-is with no warranties. By using the code below you take responsibility for any damage that may occur as a result.
//You are not allowed re-publish this code sample (including but not limited to articles, blogs, source code libraries etc) without prior written consent.
//You may however include it in in your own software provided that this header remains intact in the source code,
// and that the main purpose or usage area of that software is not to add functionality to Linq-to-SQL.
//You may not modify it for usage with ADO.NET Entity Framework without prior written permission. If you do, your license to use it is automatically revoked.
using System;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Reflection;
using System.Reflection.Emit;
using System.Text.RegularExpressions;
namespace System.Data.Linq.SetBased
{
public enum LockModeEnum
{
NotSpecified,
Row,
Page,
Table,
TableExclusive,
NoWait
}
public static class LinqSetbasedExtensions
{
public static void Update<T>(this DataContext dc, Expression<Func<T, bool>> selectionCriteria, Expression<Func<T, bool>> updateOperation)
{
Update<T>(dc, selectionCriteria, updateOperation, LockModeEnum.NotSpecified);
}
public static void Update<T>(this DataContext dc, Expression<Func<T, bool>> selectionCriteria, Expression<Func<T, bool>> updateOperation, LockModeEnum lockMode)
{
IQueryable<T> baseQuery = ((IQueryable<T>)dc.GetTable(typeof(T))).Where(selectionCriteria);
Update<T>(dc, baseQuery, updateOperation, lockMode);
}
public static void Update<T>(this DataContext dc, IQueryable<T> baseQuery, Expression<Func<T, bool>> updateOperation, LockModeEnum lockMode)
{
//get the type representing the table we're dealing with
Type baseType = typeof(T);
//get primary key members for the type we're dealing with
List<MetaDataMember> primaryKeyMembers = GetPKMembers(dc, baseType);
if (primaryKeyMembers.Count == 0)
{
throw new InvalidOperationException(baseType.Name + " has no primary key members.");
}
//create a new class containing only the PK members of the table we're going to update
MemberBinding[] memberBindings = null;
ParameterExpression[] baseTypeParams = new ParameterExpression[] { Expression.Parameter(typeof(T), "") };
Type pkType = CreatePKClass(typeof(T), primaryKeyMembers, baseTypeParams[0], out memberBindings);
//change the query to only get the PK members
IQueryable pkQuery = GetPKQuery(baseQuery, pkType, baseTypeParams, memberBindings);
//extract the select query for the primary key members
System.Data.Common.DbCommand pkSelectCommand = dc.GetCommand(pkQuery);
string whereClause = null;
if (primaryKeyMembers.Count == 1)
{
whereClause = "where " + primaryKeyMembers[0].Name + " in (" + pkSelectCommand.CommandText + ")";
}
else
{
string ct = pkSelectCommand.CommandText;
whereClause = ct.Substring(ct.IndexOf("\r\nfrom ", StringComparison.InvariantCultureIgnoreCase));
}
//generate a select query with the update expression as the where clause
IQueryable<T> updateOPQuery = ((IQueryable<T>)dc.GetTable(typeof(T)).AsQueryable());
updateOPQuery = updateOPQuery.Where(updateOperation);
//get the table name
string qualifiedTableName = dc.Mapping.GetTable(typeof(T)).TableName;
//extract the where clause from the update operation query
System.Data.Common.DbParameterCollection setClauseParameters;
string updateClause = GetSetClauseFromWhere(dc.GetCommand(updateOPQuery), qualifiedTableName, out setClauseParameters);
//concatenate the update statement
string updateStatement = ReplaceSQLParameters(
"update " + qualifiedTableName + " " + GetLockHint(lockMode)
+ " set " + updateClause
+ " " + whereClause);
//merge parameter groups into a single array
object[] parameters = MergeParameters(setClauseParameters, pkSelectCommand.Parameters);
//excute the update
dc.ExecuteCommand(updateStatement, parameters);
}
private static List<MetaDataMember> GetPKMembers(DataContext dc, Type baseType)
{
//get primary key members for a L2S class/type
return
(
from pkMember in dc.Mapping.GetTable(baseType).RowType.DataMembers
where pkMember.IsPrimaryKey == true
select pkMember
).ToList<MetaDataMember>();
}
private static IQueryable GetPKQuery(IQueryable baseQuery, Type pkType, ParameterExpression[] baseTypeParams, MemberBinding[] memberBindings)
{
//generate a lambda for creating a new instance of the PK type, mapped to the base type for the table
LambdaExpression lambda
= Expression.Lambda(
Expression.MemberInit(
Expression.New(pkType),
memberBindings),
baseTypeParams
);
//create a new query returning only the PK members as instances of the new pk type
return
baseQuery.Provider.CreateQuery(
Expression.Call(typeof(Queryable),
"Select",
new Type[] { baseQuery.ElementType, lambda.Body.Type },
baseQuery.Expression,
Expression.Quote(lambda)
)
);
}
private static Type CreatePKClass(Type baseType, List<MetaDataMember> primaryKeyMembers, ParameterExpression baseTypeParam, out MemberBinding[] memberBindings)
{
//generate a new assembly and class
AssemblyBuilder assembly
= AppDomain.CurrentDomain.DefineDynamicAssembly(
new AssemblyName("PKClasses"), AssemblyBuilderAccess.Run);
ModuleBuilder moduleBuilder = assembly.DefineDynamicModule("PKModule");
TypeBuilder typeBuilder = moduleBuilder.DefineType("PKClass_" + baseType.Name, TypeAttributes.Class | TypeAttributes.Public);
//add the primary key members to the new type
foreach (MetaDataMember pkMember in primaryKeyMembers)
{
string memberName = pkMember.Name;
Type memberType = pkMember.Type;
//create a private field for storage
FieldBuilder storageField = typeBuilder.DefineField("_" + memberName, memberType, FieldAttributes.Private);
//add a get method, returning the storage field
MethodBuilder propertyGet
= typeBuilder.DefineMethod("get_" + memberName,
MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig,
memberType, Type.EmptyTypes);
ILGenerator getIL = propertyGet.GetILGenerator();
getIL.Emit(OpCodes.Ldarg_0);
getIL.Emit(OpCodes.Ldfld, storageField);
getIL.Emit(OpCodes.Ret);
//add a set method, setting the storage field
MethodBuilder propertySet
= typeBuilder.DefineMethod("set_" + memberName,
MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig,
null, new Type[] { memberType });
ILGenerator setIL = propertySet.GetILGenerator();
setIL.Emit(OpCodes.Ldarg_0);
setIL.Emit(OpCodes.Ldarg_1);
setIL.Emit(OpCodes.Stfld, storageField);
setIL.Emit(OpCodes.Ret);
//create a public property for the member
PropertyBuilder memberProperty = typeBuilder.DefineProperty(memberName, System.Reflection.PropertyAttributes.HasDefault, memberType, null);
//assign the get/set methods to the property
memberProperty.SetGetMethod(propertyGet);
memberProperty.SetSetMethod(propertySet);
}
//return the type of the generated class
Type pkClass = typeBuilder.CreateType();
//get member bindings mapping the members of the pk class to the corresponding members in the base class
//(out param)
memberBindings =
(
from pkMember in primaryKeyMembers
select (MemberBinding)Expression.Bind(pkClass.GetProperty(pkMember.Name),
Expression.Property(baseTypeParam, baseType.GetProperty(pkMember.Name)))
).ToArray<MemberBinding>();
//return the new class' type
return pkClass;
}
private static string GetLockHint(LockModeEnum lockMode)
{
//return a lock hint based on the specified lock mode
switch (lockMode)
{
case LockModeEnum.NotSpecified:
return "";
case LockModeEnum.Row:
return "with (rowlock)";
case LockModeEnum.Table:
return "with (tablock)";
case LockModeEnum.TableExclusive:
return "with (tablockx)";
case LockModeEnum.Page:
return "with (paglock)";
case LockModeEnum.NoWait:
return "with (nowait)";
default:
return "";
}
}
private static string GetSetClauseFromWhere(System.Data.Common.DbCommand updateOperationCommand, string qualifiedTableName, out System.Data.Common.DbParameterCollection commandParameters)
{
//get just the criteria part of the query
string commandText = updateOperationCommand.CommandText;
commandText = commandText.Substring(commandText.IndexOf(qualifiedTableName) + qualifiedTableName.Length);
commandText = commandText.Substring(commandText.IndexOf("where", StringComparison.InvariantCultureIgnoreCase) + 5);
//new stringbuilder for generating the set clause
System.Text.StringBuilder ctBuilder = new System.Text.StringBuilder();
//replace 'and' and 'or' with comma separators
string[] separators = { " and ", " or ", " AND ", " OR "};
foreach (string statementPart in commandText.Split(separators, StringSplitOptions.None))
{
//remove unnecessary parenthesis wrappers
string sp = statementPart.Trim();
if (sp.StartsWith("(") && sp.EndsWith(")"))
{
sp = sp.Substring(1, sp.Length - 2);
}
//remove "[t0]." table alias
sp = sp.Replace("[t0].", "");
//add to the string builder
ctBuilder.Append(sp);
ctBuilder.AppendLine(", ");
}
ctBuilder.Remove(ctBuilder.Length - 4, 4);
//return the command parameters in the out param variable
commandParameters = updateOperationCommand.Parameters;
//return the where-clause turned into a set clause
return ctBuilder.ToString();
}
private static string ReplaceSQLParameters(string sqlCommand)
{
//replace @0, @1, etc with {0}, {1} etc...
Regex paramMatching = new Regex(@"@p\d", RegexOptions.Compiled);
int paramNo = 0;
while (paramMatching.IsMatch(sqlCommand))
{
sqlCommand = paramMatching.Replace(sqlCommand, "{" + paramNo.ToString() + "}", 1);
paramNo++;
}
return sqlCommand;
}
private static object[] MergeParameters(params System.Data.Common.DbParameterCollection[] parameterGroups)
{
//merge parameter groups into a one-dimensional object array. (nested loops as the dbparametercollection don't implement IEnumerable)
List<object> parameters = new List<object>();
foreach (System.Data.Common.DbParameterCollection parameterGroup in parameterGroups)
{
foreach (System.Data.Common.DbParameter parameter in parameterGroup)
{
parameters.Add(parameter.Value);
}
}
return parameters.ToArray();
}
}
}
