EFCore中实体映射与使用sql语句的增删改查方法汇总
2020-04-02 本文已影响0人
林间树洞
EF Core 是一个ORM（对象关系映射），它使 .NET 开发人员可以使用 .NET对象操作数据库，避免了像ADO.NET访问数据库的代码，开发者只需要编写对象即可。使用对象通过EF Core提供的实体映射方法便可以简易实现增删改查。
实体映射方法：
public class LinqDao<T> : ILinqDao<T> where T : class
{
private DataContext db;
public LinqDao(DataContext _db)
{
_db.Database.SetCommandTimeout(10);
db = _db;
}
/// <summary>
/// 异步执行增加操作以及返回带有输出的参数
/// </summary>
/// <param name="Entity"></param>
/// <returns></returns>
public async Task<bool> Add(T Entity)
{
await db.Set<T>().AddAsync(Entity);
return await db.SaveChangesAsync() > 0;
}
/// <summary>
/// 异步执行删除操作以及返回带有输出的参数
/// </summary>
/// <param name="Entity"></param>
/// <returns></returns>
public async Task<bool> Delete(T Entity)
{
db.Set<T>().Remove(Entity);
return await db.SaveChangesAsync() > 0;
}
/// <summary>
/// 异步执行修改操作以及返回带有输出的参数
/// </summary>
/// <param name="Entity"></param>
/// <returns></returns>
public async Task<bool> Update(T Entity)
{
db.Set<T>().Update(Entity);
return await db.SaveChangesAsync() > 0;
}
/// <summary>
/// 执行查询操作以及返回实体集合
/// </summary>
/// <param name="exp"></param>
/// <returns></returns>
public IEnumerable<T> GetEntities(Expression<Func<T, bool>> exp)
{
return CompileQuery(exp);
}
/// <summary>
/// 执行查询操作以及按照查询条件返回对应的实体集合
/// </summary>
/// <param name="Page"></param>
/// <param name="pageSize"></param>
/// <param name="exp"></param>
/// <returns></returns>
public IEnumerable<T> GetEntitiesForPaging(int Page, int pageSize, Expression<Func<T, bool>> exp)
{
return CompileQuery(exp).Skip((Page - 1) * pageSize).Take(pageSize);
}
/// <summary>
/// 执行查询与排序操作以及返回单个实体
/// </summary>
/// <param name="exp"></param>
/// <returns></returns>
public T GetEntityOrderBy<IKey>(Expression<Func<T, bool>> exp, Expression<Func<T, IKey>> orderBy)
{
return CompileQuerySingle(exp, orderBy);
}
/// <summary>
/// 执行查询操作以及返回单个实体
/// </summary>
/// <param name="exp"></param>
/// <returns></returns>
public T GetEntity(Expression<Func<T, bool>> exp)
{
return CompileQuerySingle(exp);
}
/// <summary>
/// 执行查询操作以及按照组合表达式返回对应的实体集合
/// </summary>
/// <param name="exp"></param>
/// <returns></returns>
private IEnumerable<T> CompileQuery(Expression<Func<T, bool>> exp)
{
var func = EF.CompileQuery((DataContext context, Expression<Func<T, bool>> exps) => context.Set<T>().Where(exp));
return func(db, exp);
}
/// <summary>
/// 执行查询与排序操作以及按照组合表达式返回对应的单个实体
/// </summary>
/// <param name="exp"></param>
/// <returns></returns>
private T CompileQuerySingle<IKey>(Expression<Func<T, bool>> exp, Expression<Func<T, IKey>> orderBy)
{
var func = EF.CompileQuery((DataContext context, Expression<Func<T, bool>> exps) => context.Set<T>().OrderByDescending(orderBy).FirstOrDefault(exp));
return func(db, exp);
}
/// <summary>
/// 执行查询操作以及按照组合表达式返回对应的单个实体
/// </summary>
/// <param name="exp"></param>
/// <returns></returns>
private T CompileQuerySingle(Expression<Func<T, bool>> exp)
{
var func = EF.CompileQuery((DataContext context, Expression<Func<T, bool>> exps) => context.Set<T>().FirstOrDefault(exp));
return func(db, exp);
}
}
}
使用linq的组合表达式可以应付大部分数据库操作，为此把部分拼接条件的实现封装起来。
public static class LinqBuilder
{
/// <summary>
/// 默认True条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static Expression<Func<T, bool>> True<T>() { return f => true; }
/// <summary>
/// 默认False条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static Expression<Func<T, bool>> False<T>() { return f => false; }
/// <summary>
/// 拼接 OR 条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="exp"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> exp, Expression<Func<T, bool>> condition)
{
var inv = Expression.Invoke(condition, exp.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>(Expression.Or(exp.Body, inv), exp.Parameters);
}
/// <summary>
/// 拼接And条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="exp"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> exp, Expression<Func<T, bool>> condition)
{
var inv = Expression.Invoke(condition, exp.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>(Expression.And(exp.Body, inv), exp.Parameters);
}
}
使用实体映射方法虽简便，但不足以应付各种事务，还是需要使用存储过程，以下是调用存储过程的方法。
public class SqlDao<T> : ISqlDao<T> where T : class
{
private DataContext db;
public SqlDao(DataContext _db)
{
_db.Database.SetCommandTimeout(3);
db = _db;
}
/// <summary>
/// 执行存储过程方法 获取实体集合以及返回空值处理
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public async Task<List<T>> GetEntitiesAsync(string sql, MySqlParameter[] sqlParams = null)
{
var connection = db.Database.GetDbConnection();
using (var cmd = connection.CreateCommand())
{
await db.Database.OpenConnectionAsync();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
var dr = await cmd.ExecuteReaderAsync();
var columnSchema = dr.GetColumnSchema();
var data = new List<T>();
while (await dr.ReadAsync())
{
// T t = System.Activator.CreateInstance<T>();
Type type = typeof(T);
// 创建一个类T的实例
object entity = type.Assembly.CreateInstance(type.FullName);
// 得到类T的名称(不包含命名空间)
string name = type.Name;
// 获取类T的所有属性
PropertyInfo[] props = type.GetProperties();
for (int i = 0; i < columnSchema.Count; i++)
{
var kv = columnSchema[i];
if (kv.ColumnOrdinal.HasValue)
{
var itemVal = dr.GetValue(kv.ColumnOrdinal.Value);
var Value = itemVal.GetType() != typeof(DBNull) ? itemVal : null;
Type TypeDefinition = (props[i].PropertyType == typeof(int?)) ? typeof(int) : props[i].PropertyType;
//Type TypeDefinition = Nullable.GetUnderlyingType(props[i].PropertyType);
object o = (Value == null) ? null : Convert.ChangeType(Value, TypeDefinition, null);
props[i].SetValue(entity, o, null);
}
}
data.Add((T)entity);
}
dr.Dispose();
return data.Count < 1 ? null : data;
}
}
/// <summary>
/// 执行带有参数的存储过程方法 获取信息集合以及返回空值处理
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public async Task<T> GetEntityAsync(string sql, MySqlParameter[] sqlParams=null)
{
var connection = db.Database.GetDbConnection();
using (var cmd = connection.CreateCommand())
{
await db.Database.OpenConnectionAsync();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
var dr = await cmd.ExecuteReaderAsync();
var columnSchema = dr.GetColumnSchema();
var data = new List<T>();
while (await dr.ReadAsync())
{
// T t = System.Activator.CreateInstance<T>();
Type type = typeof(T);
// 创建一个类T的实例
object entity = type.Assembly.CreateInstance(type.FullName);
// 得到类T的名称(不包含命名空间)
string name = type.Name;
// 获取类T的所有属性
PropertyInfo[] props = type.GetProperties();
for (int i = 0; i < columnSchema.Count; i++)
{
var kv = columnSchema[i];
if (kv.ColumnOrdinal.HasValue)
{
var itemVal = dr.GetValue(kv.ColumnOrdinal.Value);
var Value = itemVal.GetType() != typeof(DBNull) ? itemVal : null;
Type TypeDefinition = (props[i].PropertyType == typeof(int?)) ? typeof(int) : props[i].PropertyType;
//Type TypeDefinition = Nullable.GetUnderlyingType(props[i].PropertyType);
object o = (Value == null) ? null : Convert.ChangeType(Value, TypeDefinition, null);
props[i].SetValue(entity, o, null);
}
}
data.Add((T)entity);
break;
}
dr.Dispose();
return data.Count < 1 ? null : data[0];
}
}
/// <summary>
/// 异步执行Sql语句 增删改操作以及返回带有输出的参数
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public async Task<int> ExecuteSqlRawAsync(string sql, MySqlParameter[] sqlParams=null)
{
var numint = -1;
if (sqlParams != null)
{
numint = await db.Database.ExecuteSqlRawAsync(sql, sqlParams);
}
else
{
numint = await db.Database.ExecuteSqlRawAsync(sql);
}
return numint;
}
/// <summary>
/// 执行存储过程方法 获取信息集合以及返回空值处理
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public async Task<object> GetObjectAsync(string sql, MySqlParameter[] sqlParams=null)
{
var connection = db.Database.GetDbConnection();
using (var cmd = connection.CreateCommand())
{
await db.Database.OpenConnectionAsync();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
var result = await cmd.ExecuteScalarAsync();
if (result == null)
{
return null;
}
return result;
}
}
}
