public string GetFromClause(QueryOptimizer optimizer)
{
var sb = new StringBuilder();
//Calculate the FROM clause
int index = 0;
sb.Append("FROM ");
foreach (var fromClause in _fromLinkList)
{
sb.Append("[" + fromClause.Schema + "].[" + fromClause.TableName + "] AS [" + fromClause.Alias + "] ");
if (optimizer.NoLocking)
{
sb.Append("WITH (READUNCOMMITTED) ");
}
if (!string.IsNullOrEmpty(fromClause.LinkClause))
{
sb.Append(fromClause.LinkClause + " ");
}
if (index < _fromLinkList.Count - 1)
{
sb.AppendLine();
sb.Append("LEFT OUTER JOIN ");
}
index++;
}
sb.AppendLine();
return(sb.ToString());
}
/// <summary />
public static void Update <T>(this IQueryable <T> query, Expression <Func <T, T> > obj, QueryOptimizer optimizer, string connectionString)
where T : Gravitybox.GeoLocation.EFDAL.IBusinessObject, new()
{
if (optimizer == null)
{
optimizer = new QueryOptimizer();
}
if (query == null)
{
throw new Exception("Query must be set");
}
//There is nothing to do
if (query.ToString().Replace("\r", string.Empty).Split(new char[] { '\n' }).LastOrDefault().Trim() == "WHERE 1 = 0")
{
return;
}
var instanceKey = Guid.Empty;
System.Data.Entity.Core.Objects.ObjectContext objectContext = null;
try
{
var propContext = query.Provider.GetType().GetProperty("InternalContext");
if (propContext != null)
{
var context = propContext.GetValue(query.Provider);
if (context != null)
{
var oc = context.GetType().GetProperty("ObjectContext").GetValue(context) as System.Data.Entity.Core.Objects.ObjectContext;
objectContext = oc as System.Data.Entity.Core.Objects.ObjectContext;
instanceKey = ((IContext)context.GetType().GetProperty("Owner").GetValue(context)).InstanceKey;
if (string.IsNullOrEmpty(connectionString))
{
var propCs = context.GetType().GetProperty("OriginalConnectionString");
if (propCs != null)
{
connectionString = (string)propCs.GetValue(context);
}
}
}
}
if (instanceKey == Guid.Empty)
{
var context2 = query.Provider.GetType().GetField("_context", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
if (context2 != null)
{
var context = context2.GetValue(query.Provider);
objectContext = context as System.Data.Entity.Core.Objects.ObjectContext;
var qq = objectContext.InterceptionContext.DbContexts.First() as Gravitybox.GeoLocation.EFDAL.IGeoLocationEntities;
instanceKey = qq.InstanceKey;
if (string.IsNullOrEmpty(connectionString))
{
connectionString = Util.StripEFCS2Normal(objectContext.Connection.ConnectionString);
}
}
}
if (instanceKey == Guid.Empty)
{
throw new Exception("Unknown context");
}
if (string.IsNullOrEmpty(connectionString))
{
var propContext2 = query.GetType().GetProperty("Context");
if (propContext2 != null)
{
var context = propContext2.GetValue(query) as System.Data.Entity.Core.Objects.ObjectContext;
if (context != null)
{
var builder = new System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder(context.Connection.ConnectionString);
if (!string.IsNullOrWhiteSpace(builder.ProviderConnectionString))
{
objectContext = context;
connectionString = builder.ProviderConnectionString;
}
}
}
}
}
catch { }
System.Data.Entity.Core.Objects.ObjectParameterCollection existingParams = null;
{
var objectQuery = query as System.Data.Entity.Core.Objects.ObjectQuery <T>;
if (objectQuery == null)
{
var internalQueryField = query.GetType().GetProperty("InternalQuery", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(query);
if (internalQueryField != null)
{
objectQuery = internalQueryField.GetType().GetProperty("ObjectQuery").GetValue(internalQueryField) as System.Data.Entity.Core.Objects.ObjectQuery <T>;
}
}
if (objectQuery != null)
{
var ss2 = objectQuery.ToTraceString(); //DO NOT REMOVE! must call this to init params
existingParams = objectQuery.GetType().GetProperty("Parameters").GetValue(objectQuery) as System.Data.Entity.Core.Objects.ObjectParameterCollection;
}
}
var startTime = DateTime.Now;
var changedList = new Dictionary <string, object>();
#region Parse Tree
var propBody = obj.GetType().GetProperty("Body");
if (propBody != null)
{
var body = propBody.GetValue(obj);
if (body != null)
{
var propBindings = body.GetType().GetProperty("Bindings");
if (propBindings != null)
{
var members = (IEnumerable <System.Linq.Expressions.MemberBinding>)propBindings.GetValue(body);
foreach (System.Linq.Expressions.MemberAssignment item in members)
{
var name = item.Member.Name;
object value = null;
if (item.Expression.Type == typeof(int?))
{
value = CompileValue <int?>(item.Expression);
}
else if (item.Expression.Type == typeof(int))
{
value = CompileValue <int>(item.Expression);
}
else if (item.Expression.Type == typeof(string))
{
value = CompileValue <string>(item.Expression);
}
else if (item.Expression.Type == typeof(bool?))
{
value = CompileValue <bool?>(item.Expression);
}
else if (item.Expression.Type == typeof(bool))
{
value = CompileValue <bool>(item.Expression);
}
else if (item.Expression.Type == typeof(byte?))
{
value = CompileValue <byte?>(item.Expression);
}
else if (item.Expression.Type == typeof(byte))
{
value = CompileValue <byte>(item.Expression);
}
else if (item.Expression.Type == typeof(char?))
{
value = CompileValue <char?>(item.Expression);
}
else if (item.Expression.Type == typeof(char))
{
value = CompileValue <char>(item.Expression);
}
else if (item.Expression.Type == typeof(decimal?))
{
value = CompileValue <decimal?>(item.Expression);
}
else if (item.Expression.Type == typeof(decimal))
{
value = CompileValue <decimal>(item.Expression);
}
else if (item.Expression.Type == typeof(double?))
{
value = CompileValue <double?>(item.Expression);
}
else if (item.Expression.Type == typeof(double))
{
value = CompileValue <double>(item.Expression);
}
else if (item.Expression.Type == typeof(float?))
{
value = CompileValue <float?>(item.Expression);
}
else if (item.Expression.Type == typeof(float))
{
value = CompileValue <float>(item.Expression);
}
else if (item.Expression.Type == typeof(long?))
{
value = CompileValue <long?>(item.Expression);
}
else if (item.Expression.Type == typeof(long))
{
value = CompileValue <long>(item.Expression);
}
else if (item.Expression.Type == typeof(short?))
{
value = CompileValue <short?>(item.Expression);
}
else if (item.Expression.Type == typeof(short))
{
value = CompileValue <short>(item.Expression);
}
else if (item.Expression.Type == typeof(DateTime?))
{
value = CompileValue <DateTime?>(item.Expression);
}
else if (item.Expression.Type == typeof(DateTime))
{
value = CompileValue <DateTime>(item.Expression);
}
else if (item.Expression.Type == typeof(Guid?))
{
value = CompileValue <Guid?>(item.Expression);
}
else if (item.Expression.Type == typeof(Guid))
{
value = CompileValue <Guid>(item.Expression);
}
else
{
throw new Exception("Data type is not handled '" + item.Expression.Type.Name + "'");
}
changedList.Add(name, value);
}
}
else
{
throw new Exception("Update statement must be in format 'm => new Entity { Field = 0 }'");
}
}
}
#endregion
//Create a mapping for inheritance
var mapping = new List <UpdateSqlMapItem>();
IReadOnlyBusinessObject theObj = new T();
do
{
var md = theObj.GetMetaData();
mapping.Add(new UpdateSqlMapItem {
TableName = md.GetTableName(), FieldList = md.GetFields(), Schema = md.Schema(), Metadata = md
});
var newT = md.InheritsFrom();
if (newT == null)
{
theObj = default(T);
}
else
{
theObj = (IReadOnlyBusinessObject)Activator.CreateInstance(newT, false);
}
} while (theObj != null);
var paramIndex = 0;
var parameters = new List <System.Data.SqlClient.SqlParameter>();
foreach (var key in changedList.Keys)
{
var map = mapping.First(x => x.FieldList.Any(z => z == key));
var fieldSql = map.SqlList;
var value = changedList[key];
if (value == null)
{
fieldSql.Add("[" + map.Metadata.GetDatabaseFieldName(key) + "] = NULL");
}
else if (value is string)
{
fieldSql.Add("[" + map.Metadata.GetDatabaseFieldName(key) + "] = @param" + paramIndex);
parameters.Add(new System.Data.SqlClient.SqlParameter {
ParameterName = "@param" + paramIndex, DbType = System.Data.DbType.String, Value = changedList[key]
});
}
else if (value is DateTime)
{
fieldSql.Add("[" + map.Metadata.GetDatabaseFieldName(key) + "] = @param" + paramIndex);
parameters.Add(new System.Data.SqlClient.SqlParameter {
ParameterName = "@param" + paramIndex, DbType = System.Data.DbType.DateTime, Value = changedList[key]
});
}
else
{
fieldSql.Add("[" + map.Metadata.GetDatabaseFieldName(key) + "] = @param" + paramIndex);
parameters.Add(new System.Data.SqlClient.SqlParameter {
ParameterName = "@param" + paramIndex, Value = changedList[key]
});
}
paramIndex++;
}
var sb = new System.Text.StringBuilder();
#region Per table code
if (typeof(T) == typeof(Gravitybox.GeoLocation.EFDAL.Entity.CanadaPostalCode))
{
sb.AppendLine("set rowcount " + optimizer.ChunkSize + ";");
foreach (var item in mapping.Where(x => x.SqlList.Any()).ToList())
{
sb.AppendLine("UPDATE [X] SET");
sb.AppendLine(string.Join(", ", item.SqlList));
sb.AppendLine("FROM [" + item.Schema + "].[" + item.TableName + "] AS [X] INNER JOIN (");
sb.AppendLine(((IQueryable <Gravitybox.GeoLocation.EFDAL.Entity.CanadaPostalCode>)query).Select(x => new { x.RowId }).ToString());
sb.AppendLine(") AS [Extent2]");
sb.AppendLine("on [X].[RowId] = [Extent2].[RowId]");
sb.AppendLine("select @@ROWCOUNT");
}
}
else if (typeof(T) == typeof(Gravitybox.GeoLocation.EFDAL.Entity.City))
{
sb.AppendLine("set rowcount " + optimizer.ChunkSize + ";");
foreach (var item in mapping.Where(x => x.SqlList.Any()).ToList())
{
sb.AppendLine("UPDATE [X] SET");
sb.AppendLine(string.Join(", ", item.SqlList));
sb.AppendLine("FROM [" + item.Schema + "].[" + item.TableName + "] AS [X] INNER JOIN (");
sb.AppendLine(((IQueryable <Gravitybox.GeoLocation.EFDAL.Entity.City>)query).Select(x => new { x.CityId }).ToString());
sb.AppendLine(") AS [Extent2]");
sb.AppendLine("on [X].[CityId] = [Extent2].[CityId]");
sb.AppendLine("select @@ROWCOUNT");
}
}
else if (typeof(T) == typeof(Gravitybox.GeoLocation.EFDAL.Entity.State))
{
sb.AppendLine("set rowcount " + optimizer.ChunkSize + ";");
foreach (var item in mapping.Where(x => x.SqlList.Any()).ToList())
{
sb.AppendLine("UPDATE [X] SET");
sb.AppendLine(string.Join(", ", item.SqlList));
sb.AppendLine("FROM [" + item.Schema + "].[" + item.TableName + "] AS [X] INNER JOIN (");
sb.AppendLine(((IQueryable <Gravitybox.GeoLocation.EFDAL.Entity.State>)query).Select(x => new { x.StateId }).ToString());
sb.AppendLine(") AS [Extent2]");
sb.AppendLine("on [X].[StateId] = [Extent2].[StateId]");
sb.AppendLine("select @@ROWCOUNT");
}
}
else if (typeof(T) == typeof(Gravitybox.GeoLocation.EFDAL.Entity.Zip))
{
sb.AppendLine("set rowcount " + optimizer.ChunkSize + ";");
foreach (var item in mapping.Where(x => x.SqlList.Any()).ToList())
{
sb.AppendLine("UPDATE [X] SET");
sb.AppendLine(string.Join(", ", item.SqlList));
sb.AppendLine("FROM [" + item.Schema + "].[" + item.TableName + "] AS [X] INNER JOIN (");
sb.AppendLine(((IQueryable <Gravitybox.GeoLocation.EFDAL.Entity.Zip>)query).Select(x => new { x.ZipId }).ToString());
sb.AppendLine(") AS [Extent2]");
sb.AppendLine("on [X].[ZipId] = [Extent2].[ZipId]");
sb.AppendLine("select @@ROWCOUNT");
}
}
else
{
throw new Exception("Entity type not found");
}
#endregion
if (string.IsNullOrEmpty(connectionString))
{
connectionString = GeoLocationEntities.GetConnectionString();
}
var newParams = new List <System.Data.SqlClient.SqlParameter>();
if (existingParams != null)
{
foreach (var ep in existingParams)
{
newParams.Add(new System.Data.SqlClient.SqlParameter {
ParameterName = ep.Name, Value = (ep.Value == null ? System.DBNull.Value : ep.Value)
});
}
}
newParams.AddRange(parameters);
QueryPreCache.AddUpdate(instanceKey, sb.ToString(), newParams, optimizer);
}
/// <summary />
public static void Update <T>(this IQueryable <T> query, Expression <Func <T, T> > obj, QueryOptimizer optimizer)
where T : Gravitybox.GeoLocation.EFDAL.IBusinessObject, new()
{
query.Update(obj: obj, optimizer: optimizer, connectionString: null);
}
/// <summary />
public static void Delete <T>(this System.Data.Entity.DbSet <T> entitySet, Expression <Func <T, bool> > where, QueryOptimizer optimizer)
where T : System.Data.Entity.DbSet <T>, Gravitybox.GeoLocation.EFDAL.IBusinessObject, new()
{
entitySet.Where(where).Delete(optimizer: optimizer, connectionString: null);
}
/// <summary>
/// Delete all records that match a where condition
/// </summary>
public static void Delete <T>(this IQueryable <T> query, QueryOptimizer optimizer, string connectionString)
where T : Gravitybox.GeoLocation.EFDAL.IBusinessObject, new()
{
if (optimizer == null)
{
optimizer = new QueryOptimizer();
}
if (query == null)
{
throw new Exception("Query must be set");
}
//There is nothing to do
if (query.ToString().Replace("\r", string.Empty).Split(new char[] { '\n' }).LastOrDefault().Trim() == "WHERE 1 = 0")
{
return;
}
var instanceKey = Guid.Empty;
System.Data.Entity.Core.Objects.ObjectContext objectContext = null;
try
{
var propContext = query.Provider.GetType().GetProperty("InternalContext");
if (propContext != null)
{
var context = propContext.GetValue(query.Provider);
if (context != null)
{
var oc = context.GetType().GetProperty("ObjectContext").GetValue(context) as System.Data.Entity.Core.Objects.ObjectContext;
objectContext = oc as System.Data.Entity.Core.Objects.ObjectContext;
instanceKey = ((IContext)context.GetType().GetProperty("Owner").GetValue(context)).InstanceKey;
if (string.IsNullOrEmpty(connectionString))
{
var propCs = context.GetType().GetProperty("OriginalConnectionString");
if (propCs != null)
{
connectionString = (string)propCs.GetValue(context);
}
}
}
}
if (instanceKey == Guid.Empty)
{
var context2 = query.Provider.GetType().GetField("_context", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
if (context2 != null)
{
var context = context2.GetValue(query.Provider);
objectContext = context as System.Data.Entity.Core.Objects.ObjectContext;
var qq = objectContext.InterceptionContext.DbContexts.First() as Gravitybox.GeoLocation.EFDAL.IGeoLocationEntities;
if (qq != null)
{
instanceKey = qq.InstanceKey;
}
if (string.IsNullOrEmpty(connectionString))
{
connectionString = Util.StripEFCS2Normal(objectContext.Connection.ConnectionString);
}
}
}
if (instanceKey == Guid.Empty)
{
throw new Exception("Unknown context");
}
if (string.IsNullOrEmpty(connectionString))
{
var propContext2 = query.GetType().GetProperty("Context");
if (propContext2 != null)
{
var context = propContext2.GetValue(query) as System.Data.Entity.Core.Objects.ObjectContext;
if (context != null)
{
var builder = new System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder(context.Connection.ConnectionString);
if (!string.IsNullOrWhiteSpace(builder.ProviderConnectionString))
{
objectContext = context;
connectionString = builder.ProviderConnectionString;
}
}
}
}
}
catch { }
System.Data.Entity.Core.Objects.ObjectParameterCollection existingParams = null;
{
var objectQuery = query as System.Data.Entity.Core.Objects.ObjectQuery <T>;
if (objectQuery == null)
{
var internalQueryField = query.GetType().GetProperty("InternalQuery", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(query);
if (internalQueryField != null)
{
objectQuery = internalQueryField.GetType().GetProperty("ObjectQuery").GetValue(internalQueryField) as System.Data.Entity.Core.Objects.ObjectQuery <T>;
}
}
if (objectQuery != null)
{
var ss2 = objectQuery.ToTraceString(); //DO NOT REMOVE! must call this to init params
existingParams = objectQuery.GetType().GetProperty("Parameters").GetValue(objectQuery) as System.Data.Entity.Core.Objects.ObjectParameterCollection;
}
}
var sb = new System.Text.StringBuilder();
#region Per table code
if (typeof(T) == typeof(Gravitybox.GeoLocation.EFDAL.Entity.CanadaPostalCode))
{
sb.AppendLine("set rowcount " + optimizer.ChunkSize + ";");
sb.AppendLine("delete [X] from [dbo].[CanadaPostalCode] [X] inner join (");
sb.AppendLine(((IQueryable <Gravitybox.GeoLocation.EFDAL.Entity.CanadaPostalCode>)query).Select(x => new { x.RowId }).ToString());
sb.AppendLine(") AS [Extent2]");
sb.AppendLine("on [X].[RowId] = [Extent2].[RowId]");
sb.AppendLine("select @@ROWCOUNT");
}
else if (typeof(T) == typeof(Gravitybox.GeoLocation.EFDAL.Entity.City))
{
sb.AppendLine("set rowcount " + optimizer.ChunkSize + ";");
sb.AppendLine("delete [X] from [dbo].[City] [X] inner join (");
sb.AppendLine(((IQueryable <Gravitybox.GeoLocation.EFDAL.Entity.City>)query).Select(x => new { x.CityId }).ToString());
sb.AppendLine(") AS [Extent2]");
sb.AppendLine("on [X].[CityId] = [Extent2].[CityId]");
sb.AppendLine("select @@ROWCOUNT");
}
else if (typeof(T) == typeof(Gravitybox.GeoLocation.EFDAL.Entity.State))
{
sb.AppendLine("set rowcount " + optimizer.ChunkSize + ";");
sb.AppendLine("delete [X] from [dbo].[State] [X] inner join (");
sb.AppendLine(((IQueryable <Gravitybox.GeoLocation.EFDAL.Entity.State>)query).Select(x => new { x.StateId }).ToString());
sb.AppendLine(") AS [Extent2]");
sb.AppendLine("on [X].[StateId] = [Extent2].[StateId]");
sb.AppendLine("select @@ROWCOUNT");
}
else if (typeof(T) == typeof(Gravitybox.GeoLocation.EFDAL.Entity.Zip))
{
sb.AppendLine("set rowcount " + optimizer.ChunkSize + ";");
sb.AppendLine("delete [X] from [dbo].[Zip] [X] inner join (");
sb.AppendLine(((IQueryable <Gravitybox.GeoLocation.EFDAL.Entity.Zip>)query).Select(x => new { x.ZipId }).ToString());
sb.AppendLine(") AS [Extent2]");
sb.AppendLine("on [X].[ZipId] = [Extent2].[ZipId]");
sb.AppendLine("select @@ROWCOUNT");
}
else
{
throw new Exception("Entity type not found");
}
#endregion
if (string.IsNullOrEmpty(connectionString))
{
connectionString = GeoLocationEntities.GetConnectionString();
}
var newParams = new List <System.Data.SqlClient.SqlParameter>();
if (existingParams != null)
{
foreach (var ep in existingParams)
{
newParams.Add(new System.Data.SqlClient.SqlParameter {
ParameterName = ep.Name, Value = (ep.Value == null ? System.DBNull.Value : ep.Value)
});
}
}
QueryPreCache.AddDelete(instanceKey, sb.ToString(), newParams, optimizer);
}
/// <summary>
/// Delete all records that match a where condition
/// </summary>
public static void Delete <T>(this IQueryable <T> query, QueryOptimizer optimizer)
where T : Gravitybox.GeoLocation.EFDAL.IBusinessObject, new()
{
query.Delete(optimizer: optimizer, connectionString: null);
}
internal static void AddUpdate(Guid instanceKey, string sql, List <System.Data.SqlClient.SqlParameter> parameters, QueryOptimizer optimizer)
{
try
{
var newItem = new PreCacheItem
{
SQL = sql,
Parameters = parameters,
Optimizer = optimizer,
};
if (!_queryUpdateCache.ContainsKey(instanceKey))
{
_queryUpdateCache.TryAdd(instanceKey, new List <PreCacheItem>());
}
_queryUpdateCache[instanceKey].Add(newItem);
}
catch
{
throw;
}
}