C# NoSQL
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Common; using System.Data.Linq; using System.Reflection; using System.Linq.Expressions; using System.Collections; using System.Text.RegularExpressions; namespace Rocky.Data { /// <summary> /// BusinessObject to PersistentObject Queryable /// </summary> public abstract class EntityContext<T> : Disposable where T : class, new() { #region StaticMembers internal static readonly bool EnableLog; private static Hashtable _stored; private static Regex _regWithNoLock = new Regex(@"(] AS \[t\d+\])", RegexOptions.Compiled); static EntityContext() { _stored = Hashtable.Synchronized(new Hashtable()); EnableLog = bool.TrueString.Equals(System.Configuration.ConfigurationManager.AppSettings["DbEnableLog"], StringComparison.OrdinalIgnoreCase); } protected static void SetDataTypeMap(Type bizType, Type dataType) { _stored[bizType] = dataType; } #endregion #region Fields private object _boxedContext; private DataContext _context; private IDictionary _trackerItems; #endregion #region Properties /// <summary> /// 延迟提交数量 /// </summary> public byte LazySize { get; set; } protected T Context { get { base.CheckDisposed(); return (T)_boxedContext; } } private IDictionary TrackerItems { get { if (_trackerItems == null) { object services = _context.GetType().GetProperty("Services", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.GetProperty) .GetValue(_context, null); var fieldFlags = BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.GetField; object tracker = services.GetType().GetField("tracker", fieldFlags).GetValue(services); _trackerItems = (IDictionary)tracker.GetType().GetField("items", fieldFlags).GetValue(tracker); } return _trackerItems; } } #endregion #region Constructor public EntityContext(T context = null) { if (context == null) { context = new T(); } _boxedContext = context; _context = _boxedContext as DataContext; if (_context == null) { throw new InvalidOperationException("The T must be DataContext."); } if (EnableLog) { _context.Log = new DbLogger(); } } protected override void DisposeInternal(bool disposing) { if (disposing) { if (_context.ObjectTrackingEnabled) { this.LazySize = 0; this.SaveChanges(); } _context.Dispose(); } //_context = null; _boxedContext = null; } #endregion #region Methods /// <summary> /// BO Type to DO Type /// </summary> /// <param name="bizType"></param> /// <returns></returns> protected virtual Type MapDataType(Type bizType) { Type dataType = (Type)_stored[bizType]; if (dataType == null) { throw new InvalidOperationException("bizType"); } return dataType; } /// <summary> /// BO to DO /// </summary> /// <param name="bizObj"></param> /// <returns></returns> protected abstract void AssignDataObject(object dataObj, object bizObj); protected void Create<TEntity>(TEntity bizObj, Action<object> dataAction = null) where TEntity : class { Type fromType = typeof(TEntity), toType = MapDataType(fromType); var dataObj = Activator.CreateInstance(toType); AssignDataObject(dataObj, bizObj); _context.GetTable(toType).InsertOnSubmit(dataObj); if (dataAction != null) { dataAction(dataObj); } } protected void Update<TEntity>(TEntity bizObj, Action<object> dataAction = null) where TEntity : class { Type fromType = typeof(TEntity), toType = MapDataType(fromType); var metaType = _context.Mapping.GetMetaType(toType); var source = _context.GetTable(toType); var parameters = Expression.Parameter(toType, "item"); PropertyInfo prop = fromType.GetProperty(metaType.IdentityMembers[0].Name, PropertyAccess.PropertyBinding); Expression body = Expression.Equal(Expression.Property(parameters, (PropertyInfo)metaType.IdentityMembers[0].Member), Expression.Constant(prop.GetValue(bizObj, null), prop.PropertyType)); for (int i = 1; i < metaType.IdentityMembers.Count; i++) { prop = fromType.GetProperty(metaType.IdentityMembers[i].Name, PropertyAccess.PropertyBinding); body = Expression.AndAlso(body, Expression.Equal(Expression.Property(parameters, (PropertyInfo)metaType.IdentityMembers[i].Member), Expression.Constant(prop.GetValue(bizObj, null), prop.PropertyType))); } var predicate = Expression.Lambda(body, parameters); var dataObj = source.Provider.CreateQuery( Expression.Call(typeof(Queryable), "Where", new Type[] { toType }, new Expression[] { source.Expression, Expression.Quote(predicate) }) ).Cast<object>().SingleOrDefault(); AssignDataObject(dataObj, bizObj); if (dataAction != null) { dataAction(dataObj); } } protected void Delete<TEntity>(TEntity bizObj, Action<object> dataAction = null) where TEntity : class { Type fromType = typeof(TEntity), toType = MapDataType(fromType); var dataObj = Activator.CreateInstance(toType); AssignDataObject(dataObj, bizObj); var table = _context.GetTable(toType); table.Attach(dataObj); table.DeleteOnSubmit(dataObj); if (dataAction != null) { dataAction(dataObj); } } protected TEntity QuerySingle<TEntity>(params object[] identityValues) where TEntity : class { if (identityValues.IsNullOrEmpty()) { throw new ArgumentException("identityValues"); } Type fromType = typeof(TEntity), toType = MapDataType(fromType); var metaType = _context.Mapping.GetMetaType(toType); if (metaType.IdentityMembers.Count != identityValues.Length) { throw new InvalidProgramException("IdentityMembers"); } IQueryable<TEntity> source = Queryable<TEntity>(); var parameters = Expression.Parameter(fromType, "item"); Expression body = Expression.Equal(Expression.Property(parameters, fromType.GetMethod("get_" + metaType.IdentityMembers[0].Name)), Expression.Constant(identityValues[0], identityValues[0].GetType())); for (int i = 1; i < metaType.IdentityMembers.Count; i++) { body = Expression.AndAlso(body, Expression.Equal(Expression.Property(parameters, fromType.GetMethod("get_" + metaType.IdentityMembers[i].Name)), Expression.Constant(identityValues[i], identityValues[i].GetType()))); } var predicate = Expression.Lambda(body, parameters); return source.Provider.CreateQuery<TEntity>( Expression.Call(typeof(Queryable), "Where", new Type[] { fromType }, new Expression[] { source.Expression, Expression.Quote(predicate) }) ).SingleOrDefault(); } protected IQueryable<TEntity> Queryable<TEntity>(DataContext context = null) where TEntity : class { if (context == null) { context = _context; } Type fromType = typeof(TEntity), toType = MapDataType(fromType); string key = context.GetType().Name + fromType.FullName; Expression selector = (Expression)_stored[key]; if (selector == null) { var parameters = Expression.Parameter(toType, "item"); var q = from fromProperty in fromType.GetProperties(BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.Public) join toProperty in toType.GetProperties() on fromProperty.Name equals toProperty.Name let b = fromProperty.PropertyType.IsAssignableFrom(toProperty.PropertyType) let p = fromProperty.DeclaringType == fromType ? fromProperty.GetSetMethod() : fromProperty.DeclaringType.GetProperty(fromProperty.Name).GetSetMethod() select b ? Expression.Bind(p, Expression.Property(parameters, toProperty.GetGetMethod())) : Expression.Bind(p, Expression.Convert(Expression.Property(parameters, toProperty.GetGetMethod()), fromProperty.PropertyType)); var body = Expression.MemberInit(Expression.New(fromType), q.ToArray()); _stored[key] = selector = Expression.Lambda(body, parameters); } IQueryable source = context.GetTable(toType); return source.Provider.CreateQuery<TEntity>( Expression.Call(typeof(Queryable), "Select", new Type[] { toType, fromType }, new Expression[] { source.Expression, Expression.Quote(selector) }) ); } protected virtual void SaveChanges() { base.CheckDisposed(); byte lazySize = this.LazySize; if (lazySize > 0) { var trackerItems = this.TrackerItems; PropertyInfo prop = null; foreach (object value in trackerItems.Values) { if (prop == null) { prop = value.GetType().GetProperty("IsNew", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.GetProperty); } if (Convert.ToBoolean(prop.GetValue(value, null))) { goto save; } } if (lazySize > trackerItems.Count) { return; } } save: try { _context.SubmitChanges(ConflictMode.ContinueOnConflict); } //catch (DuplicateKeyException ex) //{ // this.TrackerItems.Remove(ex.Object); // goto save; //} catch (ChangeConflictException) { foreach (var conflict in _context.ChangeConflicts) { // 使用当前数据库中的值，覆盖Linq缓存中实体对象的值 //conflict.Resolve(RefreshMode.OverwriteCurrentValues); // 使用Linq缓存中实体对象的值，覆盖当前数据库中的值 //conflict.Resolve(RefreshMode.KeepCurrentValues); // 只更新实体对象中改变的字段的值，其他的保留不变 conflict.Resolve(RefreshMode.KeepChanges); } goto save; } } #endregion #region ExecuteQuery protected DbCommand PrepareCommand(IQueryable query, bool withNoLock) { var command = _context.GetCommand(query) as System.Data.SqlClient.SqlCommand; if (command == null) { throw new NotSupportedException("PrepareCommand WithNoLock"); } if (withNoLock) { string cmdText = command.CommandText; IEnumerable<Match> matches = _regWithNoLock.Matches(cmdText).Cast<Match>().OrderByDescending(m => m.Index); foreach (Match m in matches) { int splitIndex = m.Index + m.Value.Length; cmdText = cmdText.Substring(0, splitIndex) + " WITH (NOLOCK)" + cmdText.Substring(splitIndex); } command.CommandText = cmdText; } return command; } /// <summary> /// LoadOptions /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="query"></param> /// <param name="withNoLock"></param> /// <returns></returns> public List<TEntity> ExecuteQuery<TEntity>(IQueryable query, bool withNoLock = false) { var cmd = PrepareCommand(query, withNoLock); bool isClosed = cmd.Connection.State == System.Data.ConnectionState.Closed; try { if (isClosed) { cmd.Connection.Open(); } using (var dr = cmd.ExecuteReader()) { return _context.Translate<TEntity>(dr).ToList(); } } finally { if (isClosed) { cmd.Connection.Close(); } } } #endregion } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; namespace Rocky.Data { internal class DbLogger : TextWriter { private log4net.ILog _logger; public DbLogger() { _logger = log4net.LogManager.GetLogger(this.GetType()); } public override Encoding Encoding { get { return Encoding.UTF8; } } public override void Write(string value) { _logger.Debug(value); } public override void Write(char[] buffer, int index, int count) { Write(new string(buffer, index, count)); } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Collections.ObjectModel; using System.Linq.Expressions; namespace Rocky.Data { public static class ExpressionExtensions { public static Expression Visit<T>( this Expression exp, Func<T, Expression> visitor) where T : Expression { return ExpressionVisitor<T>.Visit(exp, visitor); } public static TExp Visit<T, TExp>( this TExp exp, Func<T, Expression> visitor) where T : Expression where TExp : Expression { return (TExp)ExpressionVisitor<T>.Visit(exp, visitor); } public static Expression<TDelegate> Visit<T, TDelegate>( this Expression<TDelegate> exp, Func<T, Expression> visitor) where T : Expression { return ExpressionVisitor<T>.Visit<TDelegate>(exp, visitor); } public static IQueryable<TSource> Visit<T, TSource>( this IQueryable<TSource> source, Func<T, Expression> visitor) where T : Expression { return source.Provider.CreateQuery<TSource>(ExpressionVisitor<T>.Visit(source.Expression, visitor)); } } /// <summary> /// This class visits every Parameter expression in an expression tree and calls a delegate /// to optionally replace the parameter. This is useful where two expression trees need to /// be merged (and they don't share the same ParameterExpressions). /// </summary> public class ExpressionVisitor<T> : ExpressionVisitor where T : Expression { Func<T, Expression> visitor; public ExpressionVisitor(Func<T, Expression> visitor) { this.visitor = visitor; } public static Expression Visit( Expression exp, Func<T, Expression> visitor) { return new ExpressionVisitor<T>(visitor).Visit(exp); } public static Expression<TDelegate> Visit<TDelegate>( Expression<TDelegate> exp, Func<T, Expression> visitor) { return (Expression<TDelegate>)new ExpressionVisitor<T>(visitor).Visit(exp); } public override Expression Visit(Expression exp) { if (exp is T && visitor != null) exp = visitor((T)exp); return base.Visit(exp); } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Data.Linq; using System.Linq.Expressions; using System.Reflection; using System.Globalization; using System.IO; using System.Runtime.CompilerServices; namespace Rocky.Data { public static class LinqToSqlExtensions { #region DumpCSV /// <summary> /// Creates a *.csv file from an IQueryable query, dumping out the 'simple' properties/fields. /// </summary> /// <param name="query">Represents a SELECT query to execute.</param> /// <param name="fileName">The name of the file to create.</param> /// <remarks> /// <para>If the <paramref name="query"/> contains any properties that are entity sets (i.e. rows from a FK relationship) the values will not be dumped to the file.</para> /// <para>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</para> /// </remarks> public static void DumpCSV(this IQueryable query, string fileName) { using (var output = new FileStream(fileName, FileMode.Create)) using (var writer = new StreamWriter(output)) { var firstRow = true; PropertyInfo[] properties = null; FieldInfo[] fields = null; Type type = null; bool typeIsAnonymous = false; foreach (var r in query) { if (type == null) { type = r.GetType(); typeIsAnonymous = type.IsAnonymous(); properties = type.GetProperties(); fields = type.GetFields(); } var firstCol = true; if (typeIsAnonymous) { if (firstRow) { foreach (var p in properties) { if (!firstCol) writer.Write(","); else { firstCol = false; } writer.Write(p.Name); } writer.WriteLine(); } firstRow = false; firstCol = true; foreach (var p in properties) { if (!firstCol) writer.Write(","); else { firstCol = false; } DumpValue(p.GetValue(r, null), writer); } } else { if (firstRow) { foreach (var p in fields) { if (!firstCol) writer.Write(","); else { firstCol = false; } writer.Write(p.Name); } writer.WriteLine(); } firstRow = false; firstCol = true; foreach (var p in fields) { if (!firstCol) writer.Write(","); else { firstCol = false; } DumpValue(p.GetValue(r), writer); } } writer.WriteLine(); } } } private static void DumpValue(object v, StreamWriter writer) { if (v != null) { switch (Type.GetTypeCode(v.GetType())) { // csv encode the value case TypeCode.String: string value = (string)v; if (value.Contains(",") || value.Contains('"') || value.Contains("\n")) { value = value.Replace("\"", "\"\""); if (value.Length > 31735) { value = value.Substring(0, 31732) + "..."; } writer.Write("\"" + value + "\""); } else { writer.Write(value); } break; default: writer.Write(v); break; } } } private static bool IsAnonymous(this Type type) { if (type == null) throw new ArgumentNullException("type"); // HACK: The only way to detect anonymous types right now. return Attribute.IsDefined(type, typeof(CompilerGeneratedAttribute), false) && type.IsGenericType && type.Name.Contains("AnonymousType") && (type.Name.StartsWith("<>") || type.Name.StartsWith("VB$")) && (type.Attributes & TypeAttributes.NotPublic) == TypeAttributes.NotPublic; } #endregion #region SelectMutlipleResults /// <summary> /// Batches together multiple IQueryable queries into a single DbCommand and returns all data in /// a single roundtrip to the database. /// </summary> /// <param name="context">The DataContext to execute the batch select against.</param> /// <param name="queries">Represents a collections of SELECT queries to execute.</param> /// <returns>Returns an IMultipleResults object containing all results.</returns> public static IMultipleResults SelectMutlipleResults(this DataContext context, IQueryable[] queries) { var commandList = new List<DbCommand>(); foreach (IQueryable query in queries) { var command = context.GetCommand(query); commandList.Add(command); } SqlCommand batchCommand = CombineCommands(commandList); batchCommand.Connection = context.Connection as SqlConnection; DbDataReader dr = null; if (batchCommand.Connection.State == ConnectionState.Closed) { batchCommand.Connection.Open(); dr = batchCommand.ExecuteReader(CommandBehavior.CloseConnection); } else { dr = batchCommand.ExecuteReader(); } IMultipleResults mr = context.Translate(dr); return mr; } /// <summary> /// Combines multiple SELECT commands into a single SqlCommand so that all statements can be executed in a /// single roundtrip to the database and return multiple result sets. /// </summary> /// <param name="commandList">Represents a collection of commands to be batched together.</param> /// <returns>Returns a single SqlCommand that executes all SELECT statements at once.</returns> private static SqlCommand CombineCommands(List<DbCommand> selectCommands) { SqlCommand batchCommand = new SqlCommand(); SqlParameterCollection newParamList = batchCommand.Parameters; int commandCount = 0; foreach (DbCommand cmd in selectCommands) { string commandText = cmd.CommandText; DbParameterCollection paramList = cmd.Parameters; int paramCount = paramList.Count; for (int currentParam = paramCount - 1; currentParam >= 0; currentParam--) { DbParameter param = paramList[currentParam]; DbParameter newParam = (DbParameter)((ICloneable)param).Clone(); string newParamName = param.ParameterName.Replace("@", string.Format("@{0}_", commandCount)); commandText = commandText.Replace(param.ParameterName, newParamName); newParam.ParameterName = newParamName; newParamList.Add(newParam); } if (batchCommand.CommandText.Length > 0) { batchCommand.CommandText += ";"; } batchCommand.CommandText += commandText; commandCount++; } return batchCommand; } #endregion /// <summary> /// Immediately deletes all entities from the collection with a single delete command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param> /// <typeparam name="TPrimaryKey">Represents the object type for the primary key of rows contained in <paramref name="table"/>.</typeparam> /// <param name="primaryKey">Represents the primary key of the item to be removed from <paramref name="table"/>.</param> /// <returns>The number of rows deleted from the database (maximum of 1).</returns> /// <remarks> /// <para>If the primary key for <paramref name="table"/> is a composite key, <paramref name="primaryKey"/> should be an anonymous type with property names mapping to the property names of objects of type <typeparamref name="TEntity"/>.</para> /// </remarks> public static int DeleteByPK<TEntity>(this Table<TEntity> table, object primaryKey) where TEntity : class { DbCommand delete = table.GetDeleteByPKCommand<TEntity>(primaryKey); var parameters = from p in delete.Parameters.Cast<DbParameter>() select p.Value; return table.Context.ExecuteCommand(delete.CommandText, parameters.ToArray()); } /// <summary>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete a entity row via the supplied primary key.</summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param> /// <typeparam name="TPrimaryKey">Represents the object type for the primary key of rows contained in <paramref name="table"/>.</typeparam> /// <param name="primaryKey">Represents the primary key of the item to be removed from <paramref name="table"/>.</param> /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete a entity row via the supplied primary key.</returns> /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks> public static string DeleteByPKPreview<TEntity>(this Table<TEntity> table, object primaryKey) where TEntity : class { DbCommand delete = table.GetDeleteByPKCommand<TEntity>(primaryKey); return delete.PreviewCommandText(false) + table.Context.GetLog(); } private static DbCommand GetDeleteByPKCommand<TEntity>(this Table<TEntity> table, object primaryKey) where TEntity : class { Type type = primaryKey.GetType(); bool typeIsAnonymous = type.IsAnonymous(); string dbName = table.GetDbName(); var metaTable = table.Context.Mapping.GetTable(typeof(TEntity)); var keys = from mdm in metaTable.RowType.DataMembers where mdm.IsPrimaryKey select new { mdm.MappedName, mdm.Name, mdm.Type }; SqlCommand deleteCommand = new SqlCommand(); deleteCommand.Connection = table.Context.Connection as SqlConnection; var whereSB = new StringBuilder(); foreach (var key in keys) { // Add new parameter with massaged name to avoid clashes. whereSB.AppendFormat("[{0}] = @p{1}, ", key.MappedName, deleteCommand.Parameters.Count); object value = primaryKey; if (typeIsAnonymous || (type.IsClass && type != typeof(string))) { if (typeIsAnonymous) { PropertyInfo property = type.GetProperty(key.Name); if (property == null) { throw new ArgumentOutOfRangeException(string.Format("The property {0} which is defined as part of the primary key for {1} was not supplied by the parameter primaryKey.", key.Name, metaTable.TableName)); } value = property.GetValue(primaryKey, null); } else { FieldInfo field = type.GetField(key.Name); if (field == null) { throw new ArgumentOutOfRangeException(string.Format("The property {0} which is defined as part of the primary key for {1} was not supplied by the parameter primaryKey.", key.Name, metaTable.TableName)); } value = field.GetValue(primaryKey); } if (value.GetType() != key.Type) { throw new InvalidCastException(string.Format("The property {0} ({1}) does not have the same type as {2} ({3}).", key.Name, value.GetType(), key.MappedName, key.Type)); } } else if (value.GetType() != key.Type) { throw new InvalidCastException(string.Format("The value supplied in primaryKey ({0}) does not have the same type as {1} ({2}).", value.GetType(), key.MappedName, key.Type)); } deleteCommand.Parameters.Add(new SqlParameter(string.Format("@p{0}", deleteCommand.Parameters.Count), value)); } string wherePK = whereSB.ToString(); if (wherePK == "") { throw new MissingPrimaryKeyException(string.Format("{0} does not have a primary key defined. Batch updating/deleting can not be used for tables without a primary key.", metaTable.TableName)); } deleteCommand.CommandText = string.Format("DELETE {0}\r\nWHERE {1}", dbName, wherePK.Substring(0, wherePK.Length - 2)); return deleteCommand; } /// <summary> /// Immediately deletes all entities from the collection with a single delete command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param> /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param> /// <returns>The number of rows deleted from the database.</returns> /// <remarks> /// <para>Similiar to stored procedures, and opposite from DeleteAllOnSubmit, rows provided in <paramref name="entities"/> will be deleted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para> /// <para>Additionally, to improve performance, instead of creating a delete command for each item in <paramref name="entities"/>, a single delete command is created.</para> /// </remarks> public static int DeleteBatch<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class { DbCommand delete = table.GetDeleteBatchCommand<TEntity>(entities); var parameters = from p in delete.Parameters.Cast<DbParameter>() select p.Value; return table.Context.ExecuteCommand(delete.CommandText, parameters.ToArray()); } /// <summary> /// Immediately deletes all entities from the collection with a single delete command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param> /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param> /// <returns>The number of rows deleted from the database.</returns> /// <remarks> /// <para>Similiar to stored procedures, and opposite from DeleteAllOnSubmit, rows provided in <paramref name="entities"/> will be deleted immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para> /// <para>Additionally, to improve performance, instead of creating a delete command for each item in <paramref name="entities"/>, a single delete command is created.</para> /// </remarks> public static int DeleteBatch<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter) where TEntity : class { return table.DeleteBatch(table.Where(filter)); } /// <summary> /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param> /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param> /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns> /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks> public static string DeleteBatchPreview<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class { DbCommand delete = table.GetDeleteBatchCommand<TEntity>(entities); return delete.PreviewCommandText(false) + table.Context.GetLog(); } /// <summary> /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param> /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param> /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns> /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks> public static string DeleteBatchPreview<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter) where TEntity : class { return table.DeleteBatchPreview(table.Where(filter)); } /// <summary> /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param> /// <param name="entities">Represents the collection of items which are to be removed from <paramref name="table"/>.</param> /// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns> /// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks> public static string DeleteBatchSQL<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class { DbCommand delete = table.GetDeleteBatchCommand<TEntity>(entities); return delete.PreviewCommandText(true); } /// <summary> /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be deleted.</param> /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param> /// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to delete all entities from the collection with a single delete command.</returns> /// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks> public static string DeleteBatchSQL<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter) where TEntity : class { return table.DeleteBatchSQL(table.Where(filter)); } /// <summary> /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param> /// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param> /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param> /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns> /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks> public static string UpdateBatchPreview<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class { DbCommand update = table.GetUpdateBatchCommand<TEntity>(entities, evaluator); return update.PreviewCommandText(false) + table.Context.GetLog(); } /// <summary> /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param> /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param> /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param> /// <returns>Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns> /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks> public static string UpdateBatchPreview<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class { return table.UpdateBatchPreview(table.Where(filter), evaluator); } /// <summary> /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param> /// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param> /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param> /// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns> /// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks> public static string UpdateBatchSQL<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class { DbCommand update = table.GetUpdateBatchCommand<TEntity>(entities, evaluator); return update.PreviewCommandText(true); } /// <summary> /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param> /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param> /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param> /// <returns>Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to update all entities from the collection with a single update command.</returns> /// <remarks>This method is useful for debugging purposes or when LINQ generated queries need to be passed to developers without LINQ/LINQPad.</remarks> public static string UpdateBatchSQL<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class { return table.UpdateBatchSQL(table.Where(filter), evaluator); } /// <summary> /// Immediately updates all entities in the collection with a single update command based on a <typeparamref name="TEntity"/> created from a Lambda expression. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param> /// <param name="entities">Represents the collection of items which are to be updated in <paramref name="table"/>.</param> /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param> /// <returns>The number of rows updated in the database.</returns> /// <remarks> /// <para>Similiar to stored procedures, and opposite from similiar InsertAllOnSubmit, rows provided in <paramref name="entities"/> will be updated immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para> /// <para>Additionally, to improve performance, instead of creating an update command for each item in <paramref name="entities"/>, a single update command is created.</para> /// </remarks> public static int UpdateBatch<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class { DbCommand update = table.GetUpdateBatchCommand<TEntity>(entities, evaluator); var parameters = from p in update.Parameters.Cast<DbParameter>() select p.Value; return table.Context.ExecuteCommand(update.CommandText, parameters.ToArray()); } /// <summary> /// Immediately updates all entities in the collection with a single update command based on a <typeparamref name="TEntity"/> created from a Lambda expression. /// </summary> /// <typeparam name="TEntity">Represents the object type for rows contained in <paramref name="table"/>.</typeparam> /// <param name="table">Represents a table for a particular type in the underlying database containing rows are to be updated.</param> /// <param name="filter">Represents a filter of items to be updated in <paramref name="table"/>.</param> /// <param name="evaluator">A Lambda expression returning a <typeparamref name="TEntity"/> that defines the update assignments to be performed on each item in <paramref name="entities"/>.</param> /// <returns>The number of rows updated in the database.</returns> /// <remarks> /// <para>Similiar to stored procedures, and opposite from similiar InsertAllOnSubmit, rows provided in <paramref name="entities"/> will be updated immediately with no need to call <see cref="DataContext.SubmitChanges()"/>.</para> /// <para>Additionally, to improve performance, instead of creating an update command for each item in <paramref name="entities"/>, a single update command is created.</para> /// </remarks> public static int UpdateBatch<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class { return table.UpdateBatch(table.Where(filter), evaluator); } /// <summary> /// Returns the Transact SQL string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to perform the query's select statement. /// </summary> /// <param name="context">The DataContext to execute the batch select against.</param> /// <param name="query">Represents the SELECT query to execute.</param> /// <returns>Returns the Transact SQL string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns> /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks> public static string PreviewSQL(this DataContext context, IQueryable query) { var cmd = context.GetCommand(query); return cmd.PreviewCommandText(true); } /// <summary> /// Returns a string representation the LINQ <see cref="IProvider"/> command text and parameters used that would be issued to perform the query's select statement. /// </summary> /// <param name="context">The DataContext to execute the batch select against.</param> /// <param name="query">Represents the SELECT query to execute.</param> /// <returns>Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns> /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks> public static string PreviewCommandText(this DataContext context, IQueryable query) { var cmd = context.GetCommand(query); return cmd.PreviewCommandText(false); } /// <summary> /// Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present. /// </summary> /// <param name="cmd">The <see cref="DbCommand"/> to analyze.</param> /// <param name="forTransactSQL">Whether or not the text should be formatted as 'logging' similiar to LINQ to SQL output, or in valid Transact SQL syntax ready for use with a 'query analyzer' type tool.</param> /// <returns>Returns a string representation of the <see cref="DbCommand.CommandText"/> along with <see cref="DbCommand.Parameters"/> if present.</returns> /// <remarks>This method is useful for debugging purposes or when used in other utilities such as LINQPad.</remarks> private static string PreviewCommandText(this DbCommand cmd, bool forTransactSQL) { var output = new StringBuilder(); if (!forTransactSQL) output.AppendLine(cmd.CommandText); foreach (DbParameter parameter in cmd.Parameters) { int num = 0; int num2 = 0; PropertyInfo property = parameter.GetType().GetProperty("Precision"); if (property != null) { num = (int)Convert.ChangeType(property.GetValue(parameter, null), typeof(int), CultureInfo.InvariantCulture); } PropertyInfo info2 = parameter.GetType().GetProperty("Scale"); if (info2 != null) { num2 = (int)Convert.ChangeType(info2.GetValue(parameter, null), typeof(int), CultureInfo.InvariantCulture); } SqlParameter parameter2 = parameter as SqlParameter; if (forTransactSQL) { output.AppendFormat("DECLARE {0} {1}{2}; SET {0} = {3}\r\n", new object[] { parameter.ParameterName, ( parameter2 == null ) ? parameter.DbType.ToString() : parameter2.SqlDbType.ToString(), ( parameter.Size > 0 ) ? "( " + parameter.Size.ToString( CultureInfo.CurrentCulture ) + " )" : "", GetParameterTransactValue( parameter, parameter2 ) }); } else { output.AppendFormat("-- {0}: {1} {2} (Size = {3}; Prec = {4}; Scale = {5}) [{6}]\r\n", new object[] { parameter.ParameterName, parameter.Direction, (parameter2 == null) ? parameter.DbType.ToString() : parameter2.SqlDbType.ToString(), parameter.Size.ToString(CultureInfo.CurrentCulture), num, num2, (parameter2 == null) ? parameter.Value : parameter2.SqlValue }); } } if (forTransactSQL) output.Append("\r\n" + cmd.CommandText); return output.ToString(); } private static string GetParameterTransactValue(DbParameter parameter, SqlParameter parameter2) { if (parameter2 == null) return parameter.Value.ToString(); // Not going to deal with NON SQL parameters. switch (parameter2.SqlDbType) { case SqlDbType.Char: case SqlDbType.Date: case SqlDbType.DateTime: case SqlDbType.DateTime2: case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.NVarChar: case SqlDbType.SmallDateTime: case SqlDbType.Text: case SqlDbType.VarChar: case SqlDbType.UniqueIdentifier: return string.Format("'{0}'", parameter2.SqlValue); default: return parameter2.SqlValue.ToString(); } } private static DbCommand GetDeleteBatchCommand<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class { var deleteCommand = table.Context.GetCommand(entities); deleteCommand.CommandText = string.Format("DELETE {0}\r\n", table.GetDbName()) + GetBatchJoinQuery<TEntity>(table, entities); return deleteCommand; } private static DbCommand GetUpdateBatchCommand<TEntity>(this Table<TEntity> table, IQueryable<TEntity> entities, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class { var updateCommand = table.Context.GetCommand(entities); var setSB = new StringBuilder(); int memberInitCount = 1; // Process the MemberInitExpression (there should only be one in the evaluator Lambda) to convert the expression tree // into a valid DbCommand. The Visit<> method will only process expressions of type MemberInitExpression and requires // that a MemberInitExpression be returned - in our case we'll return the same one we are passed since we are building // a DbCommand and not 'really using' the evaluator Lambda. evaluator.Visit<MemberInitExpression>(delegate(MemberInitExpression expression) { if (memberInitCount > 1) { throw new NotImplementedException("Currently only one MemberInitExpression is allowed for the evaluator parameter."); } memberInitCount++; setSB.Append(GetDbSetStatement<TEntity>(expression, table, updateCommand)); return expression; // just return passed in expression to keep 'visitor' happy. }); // Complete the command text by concatenating bits together. updateCommand.CommandText = string.Format("UPDATE {0}\r\n{1}\r\n\r\n{2}", table.GetDbName(), // Database table name setSB.ToString(), // SET fld = {}, fld2 = {}, ... GetBatchJoinQuery<TEntity>(table, entities)); // Subquery join created from entities command text if (updateCommand.CommandText.IndexOf("[arg0]") >= 0 || updateCommand.CommandText.IndexOf("NULL AS [EMPTY]") >= 0) { // TODO (Chris): Probably a better way to determine this by using an visitor on the expression before the // var selectExpression = Expression.Call... method call (search for that) and see which funcitons // are being used and determine if supported by LINQ to SQL throw new NotSupportedException(string.Format("The evaluator Expression<Func<{0},{0}>> has processing that needs to be performed once the query is returned (i.e. string.Format()) and therefore can not be used during batch updating.", table.GetType())); } return updateCommand; } private static string GetDbSetStatement<TEntity>(MemberInitExpression memberInitExpression, Table<TEntity> table, DbCommand updateCommand) where TEntity : class { var entityType = typeof(TEntity); if (memberInitExpression.Type != entityType) { throw new NotImplementedException(string.Format("The MemberInitExpression is intializing a class of the incorrect type '{0}' and it should be '{1}'.", memberInitExpression.Type, entityType)); } var setSB = new StringBuilder(); var tableName = table.GetDbName(); var metaTable = table.Context.Mapping.GetTable(entityType); // Used to look up actual field names when MemberAssignment is a constant, // need both the Name (matches the property name on LINQ object) and the // MappedName (db field name). var dbCols = from mdm in metaTable.RowType.DataMembers select new { mdm.MappedName, mdm.Name }; // Walk all the expression bindings and generate SQL 'commands' from them. Each binding represents a property assignment // on the TEntity object initializer Lambda expression. foreach (var binding in memberInitExpression.Bindings) { var assignment = binding as MemberAssignment; if (binding == null) { throw new NotImplementedException("All bindings inside the MemberInitExpression are expected to be of type MemberAssignment."); } // TODO (Document): What is this doing? I know it's grabbing existing parameter to pass into Expression.Call() but explain 'why' // I assume it has something to do with fact we can't just access the parameters of assignment.Expression? // Also, any concerns of whether or not if there are two params of type entity type? ParameterExpression entityParam = null; assignment.Expression.Visit<ParameterExpression>(delegate(ParameterExpression p) { if (p.Type == entityType) entityParam = p; return p; }); // Get the real database field name. binding.Member.Name is the 'property' name of the LINQ object // so I match that to the Name property of the table mapping DataMembers. string name = binding.Member.Name; var dbCol = (from c in dbCols where c.Name == name select c).FirstOrDefault(); if (dbCol == null) { throw new ArgumentOutOfRangeException(name, string.Format("The corresponding field on the {0} table could not be found.", tableName)); } // If entityParam is NULL, then no references to other columns on the TEntity row and need to eval 'constant' value... if (entityParam == null) { // Compile and invoke the assignment expression to obtain the contant value to add as a parameter. var constant = Expression.Lambda(assignment.Expression, null).Compile().DynamicInvoke(); // use the MappedName from the table mapping DataMembers - that is field name in DB table. if (constant == null) { setSB.AppendFormat("[{0}] = null, ", dbCol.MappedName); } else { // Add new parameter with massaged name to avoid clashes. setSB.AppendFormat("[{0}] = @p{1}, ", dbCol.MappedName, updateCommand.Parameters.Count); updateCommand.Parameters.Add(new SqlParameter(string.Format("@p{0}", updateCommand.Parameters.Count), constant)); } } else { // TODO (Documentation): Explain what we are doing here again, I remember you telling me why we have to call but I can't remember now. // Wny are we calling Expression.Call and what are we passing it? Below comments are just 'made up' and probably wrong. // Create a MethodCallExpression which represents a 'simple' select of *only* the assignment part (right hand operator) of // of the MemberInitExpression.MemberAssignment so that we can let the Linq Provider do all the 'sql syntax' generation for // us. // // For Example: TEntity.Property1 = TEntity.Property1 + " Hello" // This selectExpression will be only dealing with TEntity.Property1 + " Hello" var selectExpression = Expression.Call( typeof(Queryable), "Select", new Type[] { entityType, assignment.Expression.Type }, // TODO (Documentation): How do we know there are only 'two' parameters? And what is Expression.Lambda // doing? I assume it's returning a type of assignment.Expression.Type to match above? Expression.Constant(table), Expression.Lambda(assignment.Expression, entityParam)); setSB.AppendFormat("[{0}] = {1}, ", dbCol.MappedName, GetDbSetAssignment(table, selectExpression, updateCommand, name)); } } var setStatements = setSB.ToString(); return "SET " + setStatements.Substring(0, setStatements.Length - 2); // remove ', ' } /// <summary> /// Some LINQ Query syntax is invalid because SQL (or whomever the provider is) can not translate it to its native language. /// DataContext.GetCommand() does not detect this, only IProvider.Execute or IProvider.Compile call the necessary code to /// check this. This function invokes the IProvider.Compile to make sure the provider can translate the expression. /// </summary> /// <remarks> /// An example of a LINQ query that previously 'worked' in the *Batch methods but needs to throw an exception is something /// like the following: /// /// var pay = /// from h in HistoryData /// where h.his.Groups.gName == "Ochsner" && h.hisType == "pay" /// select h; /// /// HistoryData.UpdateBatchPreview( pay, h => new HistoryData { hisIndex = ( int.Parse( h.hisIndex ) - 1 ).ToString() } ).Dump(); /// /// The int.Parse is not valid and needs to throw an exception like: /// /// Could not translate expression '(Parse(p.hisIndex) - 1).ToString()' into SQL and could not treat it as a local expression. /// /// Unfortunately, the IProvider.Compile is internal and I need to use Reflection to call it (ugh). I've several e-mails sent into /// MS LINQ team members and am waiting for a response and will correct/improve code as soon as possible. /// </remarks> private static void ValidateExpression(ITable table, Expression expression) { var context = table.Context; PropertyInfo providerProperty = context.GetType().GetProperty("Provider", BindingFlags.Instance | BindingFlags.NonPublic); var provider = providerProperty.GetValue(context, null); var compileMI = provider.GetType().GetMethod("System.Data.Linq.Provider.IProvider.Compile", BindingFlags.Instance | BindingFlags.NonPublic); // Simply compile the expression to see if it will work. compileMI.Invoke(provider, new object[] { expression }); } private static string GetDbSetAssignment(ITable table, MethodCallExpression selectExpression, DbCommand updateCommand, string bindingName) { ValidateExpression(table, selectExpression); // Convert the selectExpression into an IQueryable query so that I can get the CommandText var selectQuery = (table as IQueryable).Provider.CreateQuery(selectExpression); // Get the DbCommand so I can grab relavent parts of CommandText to construct a field // assignment and based on the 'current TEntity row'. Additionally need to massage parameter // names from temporary command when adding to the final update command. var selectCmd = table.Context.GetCommand(selectQuery); var selectStmt = selectCmd.CommandText; selectStmt = selectStmt.Substring(7, // Remove 'SELECT ' from front ( 7 ) selectStmt.IndexOf("\r\nFROM ") - 7) // Return only the selection field expression .Replace("[t0].", "") // Remove table alias from the select .Replace(" AS [value]", "") // If the select is not a direct field (constant or expression), remove the field alias .Replace("@p", "@p" + bindingName); // Replace parameter name so doesn't conflict with existing ones. foreach (var selectParam in selectCmd.Parameters.Cast<DbParameter>()) { var paramName = string.Format("@p{0}", updateCommand.Parameters.Count); // DataContext.ExecuteCommand ultimately just takes a object array of parameters and names them p0-N. // So I need to now do replaces on the massaged value to get it in proper format. selectStmt = selectStmt.Replace( selectParam.ParameterName.Replace("@p", "@p" + bindingName), paramName); updateCommand.Parameters.Add(new SqlParameter(paramName, selectParam.Value)); } return selectStmt; } private static string GetBatchJoinQuery<TEntity>(Table<TEntity> table, IQueryable<TEntity> entities) where TEntity : class { var metaTable = table.Context.Mapping.GetTable(typeof(TEntity)); var keys = from mdm in metaTable.RowType.DataMembers where mdm.IsPrimaryKey select new { mdm.MappedName }; var joinSB = new StringBuilder(); var subSelectSB = new StringBuilder(); foreach (var key in keys) { joinSB.AppendFormat("j0.[{0}] = j1.[{0}] AND ", key.MappedName); // For now, always assuming table is aliased as t0. Should probably improve at some point. // Just writing a smaller sub-select so it doesn't get all the columns of data, but instead // only the primary key fields used for joining. subSelectSB.AppendFormat("[t0].[{0}], ", key.MappedName); } var selectCommand = table.Context.GetCommand(entities); var select = selectCommand.CommandText; var join = joinSB.ToString(); if (join == "") { throw new MissingPrimaryKeyException(string.Format("{0} does not have a primary key defined. Batch updating/deleting can not be used for tables without a primary key.", metaTable.TableName)); } join = join.Substring(0, join.Length - 5); // Remove last ' AND ' #region - Better ExpressionTree Handling Needed - /* Below is a sample query where the let statement was used to simply the 'where clause'. However, it produced an extra level in the query. var manage = from u in User join g in Groups on u.User_Group_id equals g.gKey into groups from g in groups.DefaultIfEmpty() let correctGroup = groupsToManage.Contains( g.gName ) || ( groupsToManage.Contains( "_GLOBAL" ) && g.gKey == null ) where correctGroup && ( users.Contains( u.User_Authenticate_id ) || userEmails.Contains( u.User_Email ) ) || userKeys.Contains( u.User_id ) select u; Produces this SQL: SELECT [t2].[User_id] AS [uKey], [t2].[User_Authenticate_id] AS [uAuthID], [t2].[User_Email] AS [uEmail], [t2].[User_Pin] AS [uPin], [t2].[User_Active] AS [uActive], [t2].[uAdminAuthID], [t2].[uFailureCount] FROM ( SELECT [t0].[User_id], [t0].[User_Authenticate_id], [t0].[User_Email], [t0].[User_Pin], [t0].[User_Active], [t0].[uFailureCount], [t0].[uAdminAuthID], (CASE WHEN [t1].[gName] IN (@p0) THEN 1 WHEN NOT ([t1].[gName] IN (@p0)) THEN 0 ELSE NULL END) AS [value] FROM [User] AS [t0] LEFT OUTER JOIN [Groups] AS [t1] ON [t0].[User_Group_id] = ([t1].[gKey]) ) AS [t2] WHERE (([t2].[value] = 1) AND (([t2].[User_Authenticate_id] IN (@p1)) OR ([t2].[User_Email] IN (@p2)))) OR ([t2].[User_id] IN (@p3)) If I put the entire where in one line... where ( groupsToManage.Contains( g.gName ) || ( groupsToManage.Contains( "_GLOBAL" ) && g.gKey == null ) ) && ( users.Contains( u.User_Authenticate_id ) || userEmails.Contains( u.User_Email ) ) || userKeys.Contains ( u.User_id ) I get this SQL: SELECT [t0].[User_id] AS [uKey], [t0].[User_Authenticate_id] AS [uAuthID], [t0].[User_Email] AS [uEmail], [t0].[User_Pin] AS [uPin], [t0].[User_Active] AS [uActive], [t0].[uAdminAuthID], [t0].[uFailureCount] FROM [User] AS [t0] LEFT OUTER JOIN [Groups] AS [t1] ON [t0].[User_Group_id] = ([t1].[gKey]) WHERE (([t1].[gName] IN (@p0)) AND (([t0].[User_Authenticate_id] IN (@p1)) OR ([t0].[User_Email] IN (@p2)))) OR ([t0].[User_id] IN (@p3)) The second 'cleaner' SQL worked with my original 'string parsing' of simply looking for [t0] and stripping everything before it to get rid of the SELECT and any 'TOP' clause if present. But the first SQL introduced a layer which caused [t2] to be used. So I have to do a bit different string parsing. There is probably a more efficient way to examine the ExpressionTree and figure out if something like this is going to happen. I will explore it later. */ #endregion var endSelect = select.IndexOf("[t"); // Get 'SELECT ' and any TOP clause if present var selectClause = select.Substring(0, endSelect); var selectTableNameStart = endSelect + 1; // Get the table name LINQ to SQL used in query generation var selectTableName = select.Substring(selectTableNameStart, // because I have to replace [t0] with it in the subSelectSB select.IndexOf("]", selectTableNameStart) - (selectTableNameStart)); // TODO (Chris): I think instead of searching for ORDER BY in the entire select statement, I should examine the ExpressionTree and see // if the *outer* select (in case there are nested subselects) has an orderby clause applied to it. var needsTopClause = selectClause.IndexOf(" TOP ") < 0 && select.IndexOf("\r\nORDER BY ") > 0; var subSelect = selectClause + (needsTopClause ? "TOP 100 PERCENT " : "") // If order by in original select without TOP clause, need TOP + subSelectSB.ToString() // Append just the primary keys. .Replace("[t0]", string.Format("[{0}]", selectTableName)); subSelect = subSelect.Substring(0, subSelect.Length - 2); // Remove last ', ' subSelect += select.Substring(select.IndexOf("\r\nFROM ")); // Create a sub SELECT that *only* includes the primary key fields var batchJoin = String.Format("FROM {0} AS j0 INNER JOIN (\r\n\r\n{1}\r\n\r\n) AS j1 ON ({2})\r\n", table.GetDbName(), subSelect, join); return batchJoin; } private static string GetDbName<TEntity>(this Table<TEntity> table) where TEntity : class { var entityType = typeof(TEntity); var metaTable = table.Context.Mapping.GetTable(entityType); var tableName = metaTable.TableName; if (!tableName.StartsWith("[")) { string[] parts = tableName.Split('.'); tableName = string.Format("[{0}]", string.Join("].[", parts)); } return tableName; } private static string GetLog(this DataContext context) { PropertyInfo providerProperty = context.GetType().GetProperty("Provider", BindingFlags.Instance | BindingFlags.NonPublic); var provider = providerProperty.GetValue(context, null); Type providerType = provider.GetType(); PropertyInfo modeProperty = providerType.GetProperty("Mode", BindingFlags.Instance | BindingFlags.NonPublic); FieldInfo servicesField = providerType.GetField("services", BindingFlags.Instance | BindingFlags.NonPublic); object services = servicesField != null ? servicesField.GetValue(provider) : null; PropertyInfo modelProperty = services != null ? services.GetType().GetProperty("Model", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.GetProperty) : null; return string.Format("-- Context: {0}({1}) Model: {2} Build: {3}\r\n", providerType.Name, modeProperty != null ? modeProperty.GetValue(provider, null) : "unknown", modelProperty != null ? modelProperty.GetValue(services, null).GetType().Name : "unknown", "3.5.21022.8"); } } }
-----------------------NoSQL
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.Common; using System.Data.Linq; using System.IO; using System.Reflection; using System.Linq.Expressions; using Rocky; using Rocky.Data; using Rocky.Caching; namespace NoSQL { /// <summary> /// PS: DataContext用的是PO，但DistributedCache用的是DTO；CacheContext这里是使用的无主外键关系的PO。 /// </summary> /// <typeparam name="TDataContext"></typeparam> public sealed class CacheContext<TDataContext> : EntityContext<TDataContext>, ICacheContext where TDataContext : DataContext, new() { #region Fields private static readonly MethodInfo _LinqWhere = typeof(Queryable).GetMethods().Where(item => item.Name == "Where").First(); private TDataContext _context; private DistributedCache _cache; private ICacheKeyGenerator _queryKeyGenerator; #endregion #region Properties /// <summary> /// DataAccess DLL名称，随意更改会出异常 /// <example>Soubisc.DataAccess.{0}, Soubisc.DataAccess</example> /// </summary> public string DataAccessFormat { get; private set; } public TDataContext Linq { get { return _context; } } DataContext ICacheContext.Database { get { return _context; } } DistributedCache ICacheContext.Cache { get { return _cache; } } public bool ReadOnly { get { return !_context.ObjectTrackingEnabled; } set { _context.ObjectTrackingEnabled = !value; } } public bool QueryNoLock { get; set; } #endregion #region Constructors public CacheContext(string dataAccessFormat) { this.DataAccessFormat = dataAccessFormat; _context = base.Context; this.ReadOnly = true; _cache = NoSQLHelper.CreateCache(_context.Mapping.DatabaseName); _queryKeyGenerator = new CacheKeyGenerator(); _queryKeyGenerator.AutoHash = true; } protected override void DisposeInternal(bool disposing) { if (disposing) { _cache.Dispose(); } _cache = null; _queryKeyGenerator = null; base.DisposeInternal(disposing); } #endregion #region BaseCore protected override Type MapDataType(Type bizType) { try { return base.MapDataType(bizType); } catch (InvalidOperationException) { int i = bizType.Name.LastIndexOf("Entity"); if (i == -1) { throw new InvalidOperationException("BO Mapping"); } Type dataType = Type.GetType(string.Format(DataAccessFormat, bizType.Name.Remove(i)), false); if (dataType == null) { Type objType = typeof(object); while (dataType == null && bizType.BaseType != objType) { bizType = bizType.BaseType; i = bizType.Name.LastIndexOf("Entity"); if (i == -1) { continue; } dataType = Type.GetType(string.Format(DataAccessFormat, bizType.Name.Remove(i)), false); } if (dataType == null) { throw new InvalidOperationException("BO Mapping"); } } SetDataTypeMap(bizType, dataType); return dataType; } } protected override void AssignDataObject(object dataObj, object bizObj) { Type fromType = bizObj.GetType(), toType = MapDataType(fromType); var mapper = NoSQLHelper.MapperManager.GetMapperImpl(fromType, toType, EmitMapper.MappingConfiguration.DefaultMapConfig.Instance); mapper.Map(bizObj, dataObj, null); } public TTO MapEntity<TFrom, TTO>(TFrom from, TTO to) { return MapEntity(from, to, null); } /// <summary> /// http://emitmapper.codeplex.com/wikipage?title=Customization%20using%20default%20configurator /// </summary> /// <typeparam name="TFrom"></typeparam> /// <typeparam name="TTO"></typeparam> /// <param name="from"></param> /// <param name="to"></param> /// <param name="ignoreNames">"RowID" int</param> /// <returns></returns> public TTO MapEntity<TFrom, TTO>(TFrom from, TTO to, string[] ignoreNames) { Type tFrom = typeof(TFrom), tTo = typeof(TTO); EmitMapper.Mappers.ObjectsMapperBaseImpl mapper = null; if (ignoreNames.IsNullOrEmpty()) { mapper = NoSQLHelper.MapperManager.GetMapperImpl(tFrom, tTo, EmitMapper.MappingConfiguration.DefaultMapConfig.Instance); } else { mapper = NoSQLHelper.MapperManager.GetMapperImpl(tFrom, tTo, new EmitMapper.MappingConfiguration.DefaultMapConfig().IgnoreMembers(tFrom, tTo, ignoreNames)); } return (TTO)mapper.Map(from, to, null); } #endregion #region Methods private string GenerateQueryKey(DbCommand cmd) { string longKey = cmd.CommandText; if (cmd.Parameters.Count > 0) { var buffer = new StringBuilder(longKey).AppendLine(); foreach (DbParameter param in cmd.Parameters) { switch (param.Direction) { case ParameterDirection.Input: case ParameterDirection.InputOutput: if (param.Value != null) { buffer.AppendFormat("{0}:{1},", param.ParameterName, param.Value); } break; } } buffer.Length--; longKey = buffer.ToString(); } _queryKeyGenerator.HashKey(ref longKey); Runtime.LogDebug("GenerateQueryKey:{0}.", longKey); return longKey; } public ICacheKeyMapper CreateKeyMapper<TEntity>() { var model = MetaTable.GetTable(typeof(TEntity)); return new PrimaryKeyMapper(model.PrimaryKey); } public void Initialize<TEntity>(IQueryable<TEntity> query) where TEntity : class { var model = MetaTable.GetTable(typeof(TEntity)); var keyMapper = new PrimaryKeyMapper(model.PrimaryKey); foreach (var entity in query) { _cache.AddOrGetExisting(keyMapper.GenerateKey(entity), entity, DistributedCache.InfiniteAbsoluteExpiration); } } /// <summary> /// Delete all cache items, and save db changes. /// This only used 4 debug. /// </summary> [System.Diagnostics.Conditional(Runtime.DebugSymbal)] public void FlushAll() { _cache.FlushAll(); if (!this.ReadOnly) { base.LazySize = 0; base.SaveChanges(); } } #endregion #region Linq /// <summary> /// 从数据库加载 /// </summary> /// <param name="model"></param> /// <param name="value"></param> /// <param name="sourceCmd"></param> /// <returns>DataRow</returns> /// <exception cref="System.Data.Linq.ChangeConflictException">数据库表中该行已被删除</exception> private object LoadItem(MetaTable model, Array value, DbCommand sourceCmd) { Func<PropertyInfo, object, LambdaExpression> func = (prop, constValue) => { var param = Expression.Parameter(model.EntityType, "item"); var propLeft = Expression.Property(param, prop); if (constValue.GetType() != prop.PropertyType) { constValue = Convert.ChangeType(constValue, prop.PropertyType); } var valueRight = Expression.Constant(constValue, prop.PropertyType); var body = Expression.Equal(propLeft, valueRight); return Expression.Lambda(body, param); }; IQueryable query = _context.GetTable(model.EntityType); for (int i = 0; i < value.Length; i++) { query = query.Provider.CreateQuery(Expression.Call(null, _LinqWhere.MakeGenericMethod(model.EntityType), new Expression[] { query.Expression, Expression.Quote(func(model.PrimaryKey[i].EntityProperty, value.GetValue(i))) })); } var cmd = PrepareCommand(query, false); cmd.Connection = sourceCmd.Connection; cmd.Transaction = sourceCmd.Transaction; Runtime.LogDebug(string.Format("LoadItem:{0}.", cmd.CommandText)); var tor = _context.Translate(model.EntityType, cmd.ExecuteReader()).GetEnumerator(); if (!tor.MoveNext()) { throw new ChangeConflictException("数据库表中该行已被删除"); } return tor.Current; } /// <summary> /// 执行查询 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="query"></param> /// <returns></returns> public IEnumerable<TEntity> ExecuteQuery<TEntity>(IQueryable<TEntity> query) where TEntity : class { LinqResolver resolver; var resultSet = ExecuteQuery(query, out resolver); int modelCount = resolver.QueriedModels.Count; if (modelCount == 1) { if (resolver.QueriedModels[0].EntityType == query.ElementType) { return resultSet.Cast<TEntity>(); } else { return resultSet.Select(item => (TEntity)resolver.SelectAssign(item)); } } else { var list = new List<TEntity>((int)Math.Ceiling((double)resultSet.Length / modelCount)); object[] param = new object[modelCount]; for (int i = 0, j; i < resultSet.Length; i += modelCount) { for (j = 0; j < modelCount; j++) { param[j] = resultSet[i + j]; } list.Add((TEntity)resolver.SelectAssign(param)); } return list; } } private object[] ExecuteQuery(IQueryable query, out LinqResolver resolver) { resolver = new LinqResolver(query); var cmd = PrepareCommand(query, this.QueryNoLock); string queryKey = GenerateQueryKey(cmd); var primaryKeys = (List<string>)_cache.Get(queryKey); if (primaryKeys == null) { try { cmd.Connection.Open(); //发布事务锁定读取，但不锁定写入；通过数据库事务来避免并发下此处的多次重复写入DistributedCache cmd.Transaction = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted); primaryKeys = (List<string>)_cache.Get(queryKey); if (primaryKeys == null) { primaryKeys = new List<string>(); #region InsertPrimaryKey var buffer = new StringBuilder(cmd.CommandText); string tValue = "[t", fValue = "FROM "; int offset = 7; offset = cmd.CommandText.IndexOf(tValue, offset); int maxTIndex = int.Parse(cmd.CommandText.Substring(offset + tValue.Length, 1)); do { offset = cmd.CommandText.IndexOf(fValue, offset); buffer.Insert(offset, " "); bool withOrdinal = cmd.CommandText.Substring(offset + fValue.Length, 1) == "["; if (withOrdinal) { for (int i = 0; i < resolver.QueriedModels.Count; i++) { foreach (var pk in resolver.QueriedModels[i].PrimaryKey) { buffer.Insert(offset, string.Format(",t{0}.[{1}] '{2}'", i, pk.MappedName, pk.FullName)); } } cmd.CommandText = buffer.ToString(); break; } foreach (var pk in resolver.QueriedModels.SelectMany(t => t.PrimaryKey)) { string sqlField = string.Format(",t{0}.[{1}]", maxTIndex, pk.FullName); buffer.Insert(offset, sqlField); offset += sqlField.Length; } offset += fValue.Length; maxTIndex--; cmd.CommandText = buffer.ToString(); } while (offset != -1); #endregion Runtime.LogDebug("ExecuteQuery:{0}.", cmd.CommandText); var dr = cmd.ExecuteReader(); while (dr.Read()) { foreach (var model in resolver.QueriedModels) { Array pkArray = model.PrimaryKey.Select(pk => dr[pk.FullName]).ToArray(); var keyMapper = new PrimaryKeyMapper(model.PrimaryKey); string key = keyMapper.GenerateKey(pkArray); var item = _cache.Get(key); if (item == null) { try { item = LoadItem(model, pkArray, cmd); _cache.AddOrGetExisting(key, item, DistributedCache.InfiniteAbsoluteExpiration); } catch (ChangeConflictException) { //数据库表中该行已被删除 continue; } } primaryKeys.Add(key); } } } } finally { cmd.Transaction.Dispose(); cmd.Connection.Close(); } if (primaryKeys != null) { _cache.Set(queryKey, primaryKeys, DateTimeOffset.Now.AddMinutes(10D)); } } // 因为无法得到DistributedCache server remove callback，所以在查询时检测结果集的有效性。 var resultSet = _cache.GetValues(primaryKeys).Values.ToArray(); try { for (int i = 0; i < resultSet.Length; i++) { if (resultSet[i] == null) { if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); } MetaTable model; var value = PrimaryKeyMapper.ResolveKey(primaryKeys[i], out model); try { resultSet[i] = LoadItem(model, value, cmd); _cache.AddOrGetExisting(primaryKeys[i], resultSet[i], DistributedCache.InfiniteAbsoluteExpiration); } catch (ChangeConflictException) { //数据库表中该行已被删除 continue; } } } } finally { if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } return resultSet; } #endregion } }
posted on 2013-05-31 11:54 RockyLOMO 阅读(1254) 评论(0) 编辑 收藏 举报
