/// <summary>
/// Creates a new instance of <see cref="DbQuery{TEntity, TKey}"/>
/// </summary>
/// <param name="database">The target <see cref="Database"/> to query to</param>
/// <param name="sqlDialectProvider">The <see cref="IDbDialectProvider"/> to generate the sql commands</param>
public DbQuery(Database database, IDbDialectProvider sqlDialectProvider)
{
Database = database;
Schema = DbTableSchema.Create(typeof(TEntity));
Provider = sqlDialectProvider;
PrimaryKey = Schema.Columns.Single(i => i.IsPrimaryKey);
}
private IEnumerable <object> GetEntityValues(DbTableSchema schema, IDataRecord record, Func <DbColumnSchema, bool> predicate)
{
var res = new List <object>();
GetColumns(schema).Where(predicate).ToList().ForEach(i => res.Add(record[i.ColumnName]));
return(res);
}
private IEnumerable <DbColumnSchema> GetColumns(DbTableSchema schema)
{
var parentReferences = schema.References.Where(i => !i.IsChild).ToList();
if (parentReferences.Count <= 0)
{
return(schema.Columns);
}
foreach (var parentRef in parentReferences)
{
if (string.IsNullOrWhiteSpace(parentRef.ReferenceTableKey))
{
parentRef.ReferenceTableKey = parentRef.ReferenceTable.TableName.Name + "Id";
}
if (!schema.Columns.Any(i => i.ColumnName == parentRef.ReferenceTableKey))
{
schema.Columns.Add(new DbColumnSchema()
{
ColumnName = parentRef.ReferenceTableKey,
IsIdentity = false,
IsPrimaryKey = false
});
}
}
return(schema.Columns);
}
private IEnumerable <string> GetKeyWhereStatement(DbTableSchema schema, IDataRecord record)
{
if (!GetColumns(schema).Any(i => i.IsPrimaryKey))
{
throw new InvalidDataException("At least one primary key column is required");
}
return(GetColumnValuePair(schema, record, i => i.IsPrimaryKey));
}
private List <object> GetChildReference(TableReference tableRef, DbTableSchema parentTable, IDataRecord record)
{
var sql = string.Format("SELECT * FROM {0} WHERE {1} = {2}",
tableRef.ReferenceTable.TableName.GetFullTableName(),
tableRef.ReferenceTableKey,
record[parentTable.Columns.Single(i => i.IsPrimaryKey).ColumnName].ToSql());
return(Database.ExecuteToEntityList(sql, CommandType.Text, null, tableRef.EntityType));
}
/// <summary>
/// Validates the references and provide the default values if required
/// </summary>
/// <param name="tableReference">The <see cref="TableReference"/> to validate</param>
/// <param name="parent">The reference parent <see cref="DbTableSchema"/></param>
public static void ValidateTableRef(TableReference tableReference, DbTableSchema parent)
{
if (!string.IsNullOrWhiteSpace(tableReference.ReferenceTableKey))
{
return;
}
tableReference.ReferenceTableKey = tableReference.IsChild ?
string.Format("{0}Id", parent.TableName.Name) :
string.Format("{0}Id", tableReference.ReferenceTable.TableName.Name);
}
/// <summary>
/// Creates a new instance of <see cref="TableReference"/>
/// </summary>
/// <param name="prop">The <see cref="PropertyInfo"/> to extract the metadata to be used to create the <see cref="TableReference"/> object</param>
/// <returns>A new instance of <see cref="TableReference"/></returns>
public static TableReference Create(PropertyInfo prop)
{
var entityType = GetReferenceTableEntityType(prop);
return(new TableReference()
{
ReferenceTableKey = prop.GetCustomAttribute <TableReferenceAttribute>().ReferenceTableKey,
EntityType = entityType,
IsChild = typeof(IEnumerable).IsAssignableFrom(prop.PropertyType),
ReferenceTable = DbTableSchema.Create(entityType),
SourceColumn = DbColumnSchema.Create(prop)
});
}
private IEnumerable <string> GetColumnValuePair(DbTableSchema schema, IDataRecord record, Func <DbColumnSchema, bool> predicate)
{
return(GetColumns(schema).Where(predicate)
.Select(i =>
{
var val = record[i.ColumnName];
var res = string.Format("{0} = {1}", GetSqlFormattedColumnName(i), val.ToSql());
if (val.IsNullOrDbNull())
{
res = string.Format("{0} IS NULL", GetSqlFormattedColumnName(i));
}
return res;
}).ToList());
}
private string GetCreateCommand(DbTableSchema schema, IDataRecord record, bool includeChildren)
{
var sw = new StringWriter();
sw.WriteLine("INSERT INTO {1} ({0}) VALUES ({2});",
string.Join(", ", GetSqlFormattedColumnNames(schema, (i) => !i.IsIdentity)),
GetSqlFormattedTableName(schema),
string.Join(", ", GetSqlInserValues(schema, record)));
if (includeChildren)
{
foreach (var refTable in schema.References.Where(i => i.IsChild))
{
GetCreateCommandForChildren(sw, refTable, record);
}
}
return(sw.ToString());
}
/// <summary>
/// Creates a new instance of <see cref="DbTableSchema"/>
/// </summary>
/// <param name="type">The entity <see cref="Type"/> to use to create the schema metadata</param>
/// <returns>A new instance of <see cref="DbTableSchema"/></returns>
public static DbTableSchema Create(Type type)
{
if (_cache.ContainsKey(type))
{
return(_cache[type]);
}
var columns = new List <DbColumnSchema>();
var refs = new List <TableReference>();
var res = new DbTableSchema()
{
TableName = GetTableName(type), Columns = columns, References = refs, EntityType = type
};
_cache[type] = res;
foreach (var prop in type.GetProperties())
{
if (prop.GetCustomAttribute <NotMappedAttribute>() != null)
{
continue;
}
if (prop.GetCustomAttribute <TableReferenceAttribute>() != null)
{
var tableRef = TableReference.Create(prop);
ValidateTableRef(tableRef, res);
refs.Add(tableRef);
continue;
}
columns.Add(DbColumnSchema.Create(prop));
}
if (!res.Columns.Any(i => i.IsPrimaryKey) && res.Columns.Any(i => i.ColumnName == "Id"))
{
res.Columns.Single(i => i.ColumnName == "Id").IsPrimaryKey = true;
}
return(res);
}
/// <summary>
/// Creates a new instance of <see cref="IDbDialectProvider"/>
/// </summary>
/// <param name="entityType">The <see cref="Type"/> for the entity to use for metadata extraction</param>
/// <returns>A new instance of <see cref="IDbDialectProvider"/></returns>
public virtual IDbDialectProvider Create(Type entityType)
{
return(Create(DbTableSchema.Create(entityType)));
}
/// <inheritdoc/>
public IUnitOfWork <TEntity, TKey> Create <TEntity, TKey>()
{
return(new DbUnitOfWork <TEntity, TKey>(Database, ProviderFactory.Create(DbTableSchema.Create(typeof(TEntity)))));
}
/// <summary>
/// Creates a new instance of <see cref="SqlExpressionProvider"/>
/// </summary>
/// <param name="schema">The <see cref="DbTableSchema"/> to use in the implementation</param>
public SqlExpressionProvider(DbTableSchema schema)
{
_schema = schema;
}
public abstract IDbDialectProvider Create(DbTableSchema schema);
/// <inheritdoc/>
public override IDbDialectProvider Create(DbTableSchema schema)
{
return(new SqlServerDialectProvider(schema));
}
private IEnumerable <string> GetSqlInserValues(DbTableSchema schema, IDataRecord record)
{
return(GetEntityValues(schema, record, i => !i.IsIdentity).Select(i => i.ToSql()));
}
private string GetSqlFormattedTableName(DbTableSchema schema)
{
return(string.Format("{0}", schema.TableName.GetFullTableName()));
}
private IEnumerable <string> GetSqlFormattedColumnNames(DbTableSchema schema, Func <DbColumnSchema, bool> predicate)
{
return(GetColumns(schema).Where(predicate).Select(GetSqlFormattedColumnName));
}
/// <summary>
/// Creates a new instance of <see cref="SqlServerDialectProvider"/>
/// </summary>
/// <param name="schema">The <see cref="DbTableSchema"/> to use for the creation of the commands</param>
public SqlServerDialectProvider(DbTableSchema schema)
{
Schema = schema;
}
