/// <summary>
/// Apply a fetching options to the specified entity's query.
/// </summary>
/// <returns>The entity's query with the applied options.</returns>
public static IQueryable <T> ApplyFetchingOptions <T>([NotNull] this IQueryable <T> query, [NotNull] IRepositoryConventions conventions, [CanBeNull] IQueryOptions <T> options, [NotNull] Func <Type, IQueryable <object> > joinQueryCallback) where T : class
{
Guard.NotNull(query, nameof(query));
Guard.NotNull(joinQueryCallback, nameof(joinQueryCallback));
var mainTableType = typeof(T);
var mainTableProperties = mainTableType.GetRuntimeProperties().ToList();
var fetchingPaths = options.DefaultIfFetchStrategyEmpty(conventions).PropertyPaths.ToList();
if (fetchingPaths.Any())
{
foreach (var path in fetchingPaths)
{
var joinTablePropertyInfo = mainTableProperties.Single(x => x.Name.Equals(path));
var isJoinPropertyCollection = joinTablePropertyInfo.PropertyType.IsGenericCollection();
var joinTableType = isJoinPropertyCollection
? joinTablePropertyInfo.PropertyType.GetGenericArguments().First()
: joinTablePropertyInfo.PropertyType;
var innerQuery = joinQueryCallback(joinTableType);
// Only do a join when the primary table has a foreign key property for the join table
var joinTableForeignKeyPropertyInfo = conventions
.GetForeignKeyPropertyInfos(joinTableType, mainTableType)
.FirstOrDefault();
if (joinTableForeignKeyPropertyInfo != null)
{
var mainTablePrimaryKeyPropertyInfo = conventions.GetPrimaryKeyPropertyInfos(mainTableType).First();
var mainTablePropertyInfo = joinTableType.GetRuntimeProperties().Single(x => x.PropertyType == mainTableType);
// TODO: NEEDS TO COME BACK TO THIS
// Needs a way to dynamically set the child and parent property to point at each other using the Queryable extension methods.
if (isJoinPropertyCollection)
{
var innerList = innerQuery.ToList();
query = query.LeftJoin(
innerList,
outer => outer != null ? mainTablePrimaryKeyPropertyInfo.GetValue(outer) : null,
inner => inner != null ? joinTableForeignKeyPropertyInfo.GetValue(inner) : null,
(outer, inner) =>
{
if (inner != null)
{
// Sets the main table property in the join table
mainTablePropertyInfo.SetValue(inner, outer);
}
return(outer);
}).ToList().GroupJoin(
innerList,
outer => outer != null ? mainTablePrimaryKeyPropertyInfo.GetValue(outer) : null,
inner => inner != null ? joinTableForeignKeyPropertyInfo.GetValue(inner) : null,
(outer, inner) =>
{
if (outer != null)
{
// Type casting
var items = CastToList(joinTableType, inner);
// Sets the join table property in the main table
joinTablePropertyInfo.SetValue(outer, items);
}
return(outer);
})
.AsQueryable();
}
else
{
query = query.LeftJoin(
innerQuery.AsEnumerable <object>(),
outer => outer != null ? mainTablePrimaryKeyPropertyInfo.GetValue(outer) : null,
inner => inner != null ? joinTableForeignKeyPropertyInfo.GetValue(inner) : null,
(outer, inner) =>
{
if (outer != null && inner != null)
{
// Sets the main table property in the join table
mainTablePropertyInfo.SetValue(inner, outer);
// Sets the join table property in the main table
joinTablePropertyInfo.SetValue(outer, inner);
}
return(outer);
})
.AsQueryable();
}
}
}
}
return(query);
}
public static void CreateSelectStatement <T>(IRepositoryConventions conventions, IQueryOptions <T> options, string defaultSelect, bool applyFetchOptions, out string sql, out Dictionary <string, object> parameters, out Dictionary <Type, Dictionary <string, PropertyInfo> > navigationProperties, out Func <string, Type> getTableTypeByColumnAliasCallback)
{
Guard.NotNull(conventions, nameof(conventions));
parameters = new Dictionary <string, object>();
navigationProperties = new Dictionary <Type, Dictionary <string, PropertyInfo> >();
var tableAliasCount = 1;
var tableAliasMapping = new Dictionary <string, string>();
var tableNameAndTypeMapping = new Dictionary <string, Type>();
var tableTypeAndNameMapping = new Dictionary <Type, string>();
var tableColumnAliasMapping = new Dictionary <string, Dictionary <string, string> >();
var columnAliasMappingCount = new Dictionary <string, int>();
var columnAliasTableNameMapping = new Dictionary <string, string>();
var crossJoinCountColumnAlias = string.Empty;
var crossJoinTableAlias = string.Empty;
var select = string.Empty;
string GenerateTableAlias(Type tableType)
{
if (tableType == null)
{
throw new ArgumentNullException(nameof(tableType));
}
var tableAlias = $"Extent{tableAliasCount++}";
var tableName = conventions.GetTableName(tableType);
tableAliasMapping.Add(tableName, tableAlias);
tableNameAndTypeMapping.Add(tableName, tableType);
tableTypeAndNameMapping.Add(tableType, tableName);
tableColumnAliasMapping[tableName] = new Dictionary <string, string>();
return(tableAlias);
}
string GenerateColumnAlias(PropertyInfo pi)
{
if (pi == null)
{
throw new ArgumentNullException(nameof(pi));
}
var columnName = conventions.GetColumnName(pi);
var columnAlias = columnName;
var tableName = conventions.GetTableName(pi.DeclaringType);
if (columnAliasMappingCount.TryGetValue(columnName, out int columnAliasCount))
{
++columnAliasCount;
columnAlias = $"{columnAlias}{columnAliasCount}";
columnAliasMappingCount[columnName] = columnAliasCount;
}
else
{
columnAliasMappingCount.Add(columnName, 0);
}
tableColumnAliasMapping[tableName][columnName] = columnAlias;
columnAliasTableNameMapping[columnAlias] = tableName;
return(columnAlias);
}
string EnsureColumnAlias(string prefix)
{
var columnAlias = prefix;
while (columnAliasTableNameMapping.ContainsKey(columnAlias))
{
var count = new string(columnAlias.Where(char.IsDigit).ToArray());
columnAlias = columnAlias + count + 1;
}
return(columnAlias);
}
string EnsureTableAlias(string prefix)
{
var tableAlias = prefix;
while (tableAliasMapping.ContainsValue(tableAlias))
{
var count = new string(tableAlias.Where(char.IsDigit).ToArray());
tableAlias = tableAlias + count + 1;
}
return(tableAlias);
}
string GetTableNameFromType(Type tableType)
=> tableTypeAndNameMapping[tableType];
string GetTableAliasFromName(string tableName)
=> tableAliasMapping[tableName];
string GetTableAliasFromType(Type tableType)
=> GetTableAliasFromName(GetTableNameFromType(tableType));
string GetColumnAliasFromProperty(PropertyInfo pi)
{
if (pi == null)
{
throw new ArgumentNullException(nameof(pi));
}
var columnName = conventions.GetColumnName(pi);
var tableName = conventions.GetTableName(pi.DeclaringType);
var columnMapping = tableColumnAliasMapping[tableName];
if (!columnMapping.ContainsKey(columnName))
{
return(null);
}
return(columnMapping[columnName]);
}
getTableTypeByColumnAliasCallback = columnAlias =>
{
if (columnAliasTableNameMapping.ContainsKey(columnAlias))
{
var tableName = columnAliasTableNameMapping[columnAlias];
return(tableNameAndTypeMapping[tableName]);
}
return(null);
};
var sb = new StringBuilder();
var joinStatementSb = new StringBuilder();
var mainTableType = typeof(T);
var mainTableName = conventions.GetTableName(mainTableType);
var mainTableAlias = GenerateTableAlias(mainTableType);
var mainTableProperties = mainTableType.GetRuntimeProperties().ToList();
var mainTablePrimaryKeyPropertyInfo = conventions.GetPrimaryKeyPropertyInfos <T>().First();
var mainTablePrimaryKeyName = conventions.GetColumnName(mainTablePrimaryKeyPropertyInfo);
var fetchingPaths = applyFetchOptions
? options.DefaultIfFetchStrategyEmpty(conventions).PropertyPaths.ToList()
: Enumerable.Empty <string>().ToList();
const string DEFAULT_CROSS_JOIN_COLUMN_ALIAS = "C1";
const string DEFAULT_CROSS_JOIN_TABLE_ALIAS = "GroupBy1";
var properties = GetProperties(conventions, mainTableType);
foreach (var pi in properties.Values)
{
GenerateColumnAlias(pi);
}
// -----------------------------------------------------------------------------------------------------------
// Select clause
// -----------------------------------------------------------------------------------------------------------
if (string.IsNullOrEmpty(defaultSelect))
{
// Default select
select = string.Join($",{Environment.NewLine}\t", properties.Select(x =>
{
var colAlias = GetColumnAliasFromProperty(x.Value);
var colName = x.Key;
return($"[{mainTableAlias}].[{colName}] AS [{colAlias}]");
}));
}
else
{
select = defaultSelect;
}
// Append join tables from fetchStrategy
// Only supports a one to one table join for now...
if (fetchingPaths.Any())
{
sb.Append($"SELECT{Environment.NewLine}\t{select}");
foreach (var path in fetchingPaths)
{
var joinTablePropertyInfo = mainTableProperties.Single(x => x.Name.Equals(path));
var joinTableType = joinTablePropertyInfo.PropertyType.IsGenericCollection()
? joinTablePropertyInfo.PropertyType.GetGenericArguments().First()
: joinTablePropertyInfo.PropertyType;
var joinTableForeignKeyPropertyInfo = conventions.GetForeignKeyPropertyInfos(joinTableType, mainTableType).FirstOrDefault();
// Only do a join when the primary table has a foreign key property for the join table
if (joinTableForeignKeyPropertyInfo != null)
{
var joinTableForeignKeyName = conventions.GetColumnName(joinTableForeignKeyPropertyInfo);
var joinTableProperties = joinTableType.GetRuntimeProperties().ToList();
var joinTableName = conventions.GetTableName(joinTableType);
var joinTableAlias = GenerateTableAlias(joinTableType);
var joinTableColumnNames = string.Join($",{Environment.NewLine}\t",
joinTableProperties
.Where(Extensions.PropertyInfoExtensions.IsPrimitive)
.Select(x =>
{
var colAlias = GenerateColumnAlias(x);
var colName = conventions.GetColumnName(x);
return($"[{joinTableAlias}].[{colName}] AS [{colAlias}]");
}));
if (string.IsNullOrEmpty(defaultSelect))
{
sb.Append($",{Environment.NewLine}\t");
sb.Append(joinTableColumnNames);
}
joinStatementSb.Append(Environment.NewLine);
joinStatementSb.Append($"LEFT OUTER JOIN [{joinTableName}] AS [{joinTableAlias}] ON [{mainTableAlias}].[{mainTablePrimaryKeyName}] = [{joinTableAlias}].[{joinTableForeignKeyName}]");
navigationProperties.Add(joinTableType, joinTableProperties.ToDictionary(conventions.GetColumnName, x => x));
}
}
if (options != null && options.PageSize != -1 && string.IsNullOrEmpty(defaultSelect))
{
crossJoinCountColumnAlias = EnsureColumnAlias(DEFAULT_CROSS_JOIN_COLUMN_ALIAS);
crossJoinTableAlias = EnsureTableAlias(DEFAULT_CROSS_JOIN_TABLE_ALIAS);
// Cross join counter column
sb.Append(",");
sb.Append(Environment.NewLine);
sb.Append($"\t[{crossJoinTableAlias}].[{crossJoinCountColumnAlias}] AS [{crossJoinCountColumnAlias}]");
}
sb.Append(Environment.NewLine);
sb.Append($"FROM [{mainTableName}] AS [{mainTableAlias}]");
if (joinStatementSb.Length > 0)
{
joinStatementSb.Remove(0, Environment.NewLine.Length);
sb.Append(Environment.NewLine);
sb.Append(joinStatementSb);
}
}
else
{
sb.Append($"SELECT{Environment.NewLine}\t{select}{Environment.NewLine}FROM [{mainTableName}] AS [{mainTableAlias}]");
}
if (options != null)
{
// -----------------------------------------------------------------------------------------------------------
// Cross Join clause
// -----------------------------------------------------------------------------------------------------------
if (options.PageSize != -1 && string.IsNullOrEmpty(defaultSelect))
{
if (string.IsNullOrEmpty(crossJoinTableAlias))
{
crossJoinTableAlias = EnsureTableAlias(DEFAULT_CROSS_JOIN_TABLE_ALIAS);
crossJoinCountColumnAlias = EnsureColumnAlias(DEFAULT_CROSS_JOIN_COLUMN_ALIAS);
}
sb.Append(Environment.NewLine);
sb.Append("CROSS JOIN (");
sb.Append(Environment.NewLine);
sb.Append($"\tSELECT COUNT(*) AS [{crossJoinCountColumnAlias}]");
sb.Append(Environment.NewLine);
sb.Append($"\tFROM [{mainTableName}] AS [{mainTableAlias}]");
if (joinStatementSb.Length > 0)
{
sb.Append(Environment.NewLine);
sb.Append("\t");
sb.Append(joinStatementSb);
}
if (options.SpecificationStrategy != null)
{
new ExpressionTranslator().Translate(
options.SpecificationStrategy.Predicate,
GetTableAliasFromType,
GetColumnAliasFromProperty,
out var expSql,
out _);
sb.Append(Environment.NewLine);
sb.Append($"\tWHERE {expSql}");
}
sb.Append(Environment.NewLine);
sb.Append($") AS [{crossJoinTableAlias}]");
}
// -----------------------------------------------------------------------------------------------------------
// Where clause
// -----------------------------------------------------------------------------------------------------------
if (options.SpecificationStrategy != null)
{
new ExpressionTranslator().Translate(
options.SpecificationStrategy.Predicate,
GetTableAliasFromType,
GetColumnAliasFromProperty,
out var expSql,
out var expParameters);
sb.Append($"{Environment.NewLine}WHERE ");
sb.Append(expSql);
foreach (var item in expParameters)
{
parameters.Add(item.Key, item.Value);
}
}
// -----------------------------------------------------------------------------------------------------------
// Sorting clause
// -----------------------------------------------------------------------------------------------------------
if (string.IsNullOrEmpty(defaultSelect))
{
var sorting = options.SortingPropertiesMapping.ToDictionary(x => x.Key, x => x.Value);
if (!sorting.Any())
{
// Sorts on the Id key by default if no sorting is provided
foreach (var primaryKeyPropertyInfo in conventions.GetPrimaryKeyPropertyInfos <T>())
{
sorting.Add(primaryKeyPropertyInfo.Name, SortOrder.Ascending);
}
}
sb.Append(Environment.NewLine);
sb.Append("ORDER BY ");
foreach (var sort in sorting)
{
var sortOrder = sort.Value;
var sortProperty = sort.Key;
var lambda = ExpressionHelper.GetExpression <T>(sortProperty);
var tableType = ExpressionHelper.GetMemberExpression(lambda).Expression.Type;
var tableName = GetTableNameFromType(tableType);
var tableAlias = GetTableAliasFromName(tableName);
var sortingPropertyInfo = ExpressionHelper.GetPropertyInfo(lambda);
var columnAlias = GetColumnAliasFromProperty(sortingPropertyInfo);
sb.Append($"[{tableAlias}].[{columnAlias}] {(sortOrder == SortOrder.Descending ? "DESC" : "ASC")}, ");
}
sb.Remove(sb.Length - 2, 2);
}
// -----------------------------------------------------------------------------------------------------------
// Paging clause
// -----------------------------------------------------------------------------------------------------------
if (options.PageSize != -1)
{
sb.Append(Environment.NewLine);
sb.Append($"OFFSET {options.PageSize} * ({options.PageIndex} - 1) ROWS");
sb.Append(Environment.NewLine);
sb.Append($"FETCH NEXT {options.PageSize} ROWS ONLY");
}
}
sql = sb.ToString();
}
public TElement Map <TElement>(DbDataReader r, Func <T, TElement> elementSelector)
{
if (r == null)
{
throw new ArgumentNullException(nameof(r));
}
if (elementSelector == null)
{
throw new ArgumentNullException(nameof(elementSelector));
}
var key = GetDataReaderPrimaryKey <T>(r);
var entity = _entityDataReaderMapping.ContainsKey(key)
? (T)_entityDataReaderMapping[key]
: Activator.CreateInstance <T>();
var entityType = typeof(T);
var joinTableInstances = _navigationProperties.Keys.ToDictionary(x => x, Activator.CreateInstance);
for (var i = 0; i < r.FieldCount; i++)
{
var name = r.GetName(i);
var value = r[name];
if (value == DBNull.Value)
{
value = null;
}
if (_properties.ContainsKey(name))
{
if (!r.IsDBNull(r.GetOrdinal(name)))
{
_properties[name].SetValue(entity, value);
}
}
else if (joinTableInstances.Any())
{
var joinTableProperty = _getTableTypeByColumnAliasCallback(name);
if (joinTableProperty != null)
{
var joinTableType = _getTableTypeByColumnAliasCallback(name);
if (joinTableType != null)
{
var columnPropertyInfosMapping = _navigationProperties.Single(x => x.Key == joinTableType).Value;
if (columnPropertyInfosMapping.ContainsKey(name))
{
columnPropertyInfosMapping[name].SetValue(joinTableInstances[joinTableType], value);
}
else
{
columnPropertyInfosMapping[NormalizeColumnAlias(name)].SetValue(joinTableInstances[joinTableType], value);
}
}
}
}
}
if (joinTableInstances.Any())
{
var mainTablePrimaryKeyPropertyInfo = _conventions.GetPrimaryKeyPropertyInfos <T>().First();
var mainTablePrimaryKeyValue = mainTablePrimaryKeyPropertyInfo.GetValue(entity);
// Needs to make sure we are not dealing with navigation properties that are not actually linked to this entity
var validJoinTableInstances = joinTableInstances
.Where(x =>
{
var joinTableForeignKeyPropertyInfo = _conventions
.GetForeignKeyPropertyInfos(x.Key, entityType)
.First();
var joinTableForeignKeyValue = joinTableForeignKeyPropertyInfo.GetValue(x.Value);
return(mainTablePrimaryKeyValue.Equals(joinTableForeignKeyValue));
})
.ToDictionary(x => x.Key, x => x.Value);
if (validJoinTableInstances.Any())
{
var mainTableProperties = entityType.GetRuntimeProperties().ToList();
foreach (var item in validJoinTableInstances)
{
var joinTableInstance = item.Value;
var joinTableType = item.Key;
var isJoinPropertyCollection = false;
// Sets the main table property in the join table
var mainTablePropertyInfo = joinTableType.GetRuntimeProperties().Single(x => x.PropertyType == entityType);
mainTablePropertyInfo.SetValue(joinTableInstance, entity);
// Sets the join table property in the main table
var joinTablePropertyInfo = mainTableProperties.Single(x =>
{
isJoinPropertyCollection = x.PropertyType.IsGenericCollection();
var type = isJoinPropertyCollection
? x.PropertyType.GetGenericArguments().First()
: x.PropertyType;
return(type == joinTableType);
});
if (isJoinPropertyCollection)
{
var collection = joinTablePropertyInfo.GetValue(entity, null);
if (collection == null)
{
var collectionTypeParam = joinTablePropertyInfo.PropertyType.GetGenericArguments().First();
collection = Activator.CreateInstance(typeof(List <>).MakeGenericType(collectionTypeParam));
joinTablePropertyInfo.SetValue(entity, collection);
}
collection.GetType().GetMethod("Add").Invoke(collection, new[] { joinTableInstance });
}
else
{
joinTablePropertyInfo.SetValue(entity, joinTableInstance);
}
}
}
}
_entityDataReaderMapping[key] = entity;
return(elementSelector(entity));
}
private void ValidateTable(Type entityType, IEnumerable <SchemaTableColumn> schemaTableColumns, string tableName)
{
var error = new Dictionary <string, bool>();
var propertiesMapping = entityType
.GetRuntimeProperties()
.Where(x => x.IsPrimitive())
.OrderBy(_conventions.GetColumnOrderOrDefault)
.ToDictionary(_conventions.GetColumnName, x => x);
var columns = propertiesMapping.Keys.ToArray();
// Ensure we have the same number of columns and they all have the same name
if (schemaTableColumns.Count() != columns.Length)
{
error["ColumnCount_Mismatch"] = true;
}
if (!schemaTableColumns.All(x => columns.Contains(x.ColumnName)))
{
error["ColumnName_Mismatch"] = true;
}
if (error.Any())
{
throw new InvalidOperationException(string.Format(Resources.SchemaTableColumnsMismatch, entityType.Name));
}
// Gets all the constraints
var schemaTableColumnConstraintsMapping = GetSchemaTableColumnConstraintsMapping(tableName, columns);
var primaryKeyPropertiesMapping = _conventions.GetPrimaryKeyPropertyInfos(entityType).ToDictionary(_conventions.GetColumnName, x => x);
// Gets all the foreign keys
var foreignKeyPropertyInfosMapping = entityType
.GetRuntimeProperties()
.Where(x => x.IsComplex())
.Select(pi => _conventions.GetForeignKeyPropertyInfos(entityType, pi.PropertyType)
.ToDictionary(_conventions.GetColumnName, x => pi))
.SelectMany(x => x)
.ToDictionary(x => x.Key, x => x.Value);
// Validate all the columns
foreach (var schemaTableColumn in schemaTableColumns)
{
var columnName = schemaTableColumn.ColumnName;
var propertyInfo = propertiesMapping[columnName];
// Property type
var columnDataType = DbHelper.MapToType(schemaTableColumn.DataType);
if (columnDataType != propertyInfo.PropertyType)
{
error["PropertyType_Mismatch"] = true;
}
// Property order
if (!error.Any())
{
var columnAttribute = propertyInfo.GetCustomAttribute <ColumnAttribute>();
if (columnAttribute != null && columnAttribute.Order != -1)
{
var order = schemaTableColumn.OrdinalPosition;
// This is cant be a simple 'schemaTableColumn.OrdinalPosition != columnAttribute.Order' to check for a mismatch...
// if the type has foreign composite keys, the foreign keys will have an ordering attribute that matches the one on the
// foreign entity primary keys, not necessarily the ones that are in the sql database
if (foreignKeyPropertyInfosMapping.ContainsKey(columnName))
{
var foreignPropertyInfo = foreignKeyPropertyInfosMapping[columnName];
if (_conventions.HasCompositePrimaryKey(foreignPropertyInfo.PropertyType))
{
order = schemaTableColumn.OrdinalPosition - (schemaTableColumn.OrdinalPosition - columnAttribute.Order);
}
}
if (order != columnAttribute.Order)
{
error["OrdinalPosition_Mismatch"] = true;
}
}
}
// Property constraints
if (!error.Any())
{
var requiredAttribute = propertyInfo.GetCustomAttribute <RequiredAttribute>();
var canBeNull = !propertyInfo.PropertyType.IsValueType || Nullable.GetUnderlyingType(propertyInfo.PropertyType) != null;
if (canBeNull && schemaTableColumn.IsNullable == "NO")
{
if (requiredAttribute == null && !primaryKeyPropertiesMapping.ContainsKey(columnName))
{
error["IsNullable_Mismatch"] = true;
}
}
if (schemaTableColumnConstraintsMapping != null &&
schemaTableColumnConstraintsMapping.ContainsKey(columnName))
{
var constraint = schemaTableColumnConstraintsMapping[columnName];
switch (constraint)
{
case SchemaTableConstraintType.NotNull:
{
if (requiredAttribute == null)
{
error["IsNullable_Mismatch"] = true;
}
break;
}
case SchemaTableConstraintType.PrimaryKey:
{
if (!primaryKeyPropertiesMapping.ContainsKey(columnName))
{
error["PrimaryKey_Mismatch"] = true;
}
break;
}
}
}
}
// Property length
if (!error.Any())
{
var stringLengthAttribute = propertyInfo.GetCustomAttribute <StringLengthAttribute>();
if (stringLengthAttribute != null)
{
if (schemaTableColumn.CharacterMaximunLength != stringLengthAttribute.MaximumLength)
{
error["CharacterMaximunLength_Mismatch"] = true;
}
}
}
if (error.Any())
{
throw new InvalidOperationException(string.Format(Resources.SchemaTableColumnsMismatch, entityType.Name));
}
}
}