EFCore扩展Select方法(自动映射自定义实体)
通常用操作数据库的时候查询返回的字段是跟 我们的定义的实体是不一致的，所以往往针对UI或者接口层创建大量的Model, 而且需要手动对应字段，非常繁琐。 本文将通过表达式树解决这些重复的过程。
先贴上实现代码
Queryable 类中 的扩展方法 Select<TSource, TResult>(this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector) 需要参数 Expression<Func<TSource, TResult>> selector 只要构造相应的表达式树即可实现自定义映射
using System.Collections; using System.ComponentModel.DataAnnotations.Schema; using System.Linq.Expressions; using System.Reflection; using static System.Linq.Expressions.Expression; public static class QueryableExtentions
{ public static IQueryable<TTarget> Select<TTarget>(this IQueryable<object> query)
{ return Queryable.Select(query, GetLamda<object, TTarget>(query.GetType().GetGenericArguments()[0]));
} public static IQueryable<TTarget> Select<TSource, TTarget>(this IQueryable<TSource> query)
{ return Queryable.Select(query, GetLamda<TSource, TTarget>());
} public static Expression<Func<TSource, TTarget>> GetLamda<TSource, TTarget>(Type type = null)
{ var sourceType = typeof(TSource); var targetType = typeof(TTarget); var parameter = Parameter(sourceType);
Expression propertyParameter; if (type != null)
{
propertyParameter = Convert(parameter, type);
sourceType = type;
} else
propertyParameter = parameter; return Lambda<Func<TSource, TTarget>>(GetExpression(propertyParameter, sourceType, targetType), parameter);
} public static MemberInitExpression GetExpression(Expression parameter, Type sourceType, Type targetType)
{ var memberBindings = new List<MemberBinding>(); foreach (var targetItem in targetType.GetProperties().Where(x => x.CanWrite))
{ var fromEntityAttr = targetItem.GetCustomAttribute<FromEntityAttribute>(); if (fromEntityAttr != null)
{ var property = GetFromEntityExpression(parameter, sourceType, fromEntityAttr); if (property != null)
memberBindings.Add(Bind(targetItem, property)); continue;
} var sourceItem = sourceType.GetProperty(targetItem.Name); if (sourceItem == null)//当没有对应的属性时，查找 实体名+属性 { var complexSourceItemProperty = GetCombinationExpression(parameter, sourceType, targetItem); if (complexSourceItemProperty != null)
memberBindings.Add(Bind(targetItem, complexSourceItemProperty)); continue;
} //判断实体的读写权限
if (sourceItem == null || !sourceItem.CanRead) continue; //标注NotMapped特性的属性忽略转换
if (sourceItem.GetCustomAttribute<NotMappedAttribute>() != null) continue; var sourceProperty = Property(parameter, sourceItem); //当非值类型且类型不相同时
if (!sourceItem.PropertyType.IsValueType && sourceItem.PropertyType != targetItem.PropertyType && targetItem.PropertyType != targetType)
{ //判断都是(非泛型、非数组)class
if (sourceItem.PropertyType.IsClass && targetItem.PropertyType.IsClass && !sourceItem.PropertyType.IsArray && !targetItem.PropertyType.IsArray && !sourceItem.PropertyType.IsGenericType && !targetItem.PropertyType.IsGenericType)
{ var expression = GetExpression(sourceProperty, sourceItem.PropertyType, targetItem.PropertyType);
memberBindings.Add(Bind(targetItem, expression));
} continue;
} if (targetItem.PropertyType != sourceItem.PropertyType) continue;
memberBindings.Add(Bind(targetItem, sourceProperty));
} return MemberInit(New(targetType), memberBindings);
} /// <summary>
/// 根据FromEntityAttribute 的值获取属性对应的路径 /// </summary>
/// <param name="sourceProperty"></param>
/// <param name="sourceType"></param>
/// <param name="fromEntityAttribute"></param>
/// <returns></returns>
private static Expression GetFromEntityExpression(Expression sourceProperty, Type sourceType, FromEntityAttribute fromEntityAttribute)
{ var findType = sourceType; var resultProperty = sourceProperty; var tableNames = fromEntityAttribute.EntityNames; if (tableNames == null)
{ var columnProperty = findType.GetProperty(fromEntityAttribute.EntityColuum); if (columnProperty == null) return null; else
return Property(resultProperty, columnProperty);
} for (int i = tableNames.Length - 1; i >= 0; i--)
{ var tableProperty = findType.GetProperty(tableNames[i]); if (tableProperty == null) return null;
findType = tableProperty.PropertyType;
resultProperty = Property(resultProperty, tableProperty);
} var property = findType.GetProperty(fromEntityAttribute.EntityColuum); if (property == null) return null; else
return Property(resultProperty, property);
} /// <summary>
/// 根据组合字段获取其属性路径 /// </summary>
/// <param name="sourceProperty"></param>
/// <param name="sourcePropertys"></param>
/// <param name="targetItem"></param>
/// <returns></returns>
private static Expression GetCombinationExpression(Expression sourceProperty, Type sourceType, PropertyInfo targetItem)
{ foreach (var item in sourceType.GetProperties().Where(x => x.CanRead))
{ if (targetItem.Name.StartsWith(item.Name))
{ if (item != null && item.CanRead && item.PropertyType.IsClass && !item.PropertyType.IsGenericType)
{ var rightName = targetItem.Name.Substring(item.Name.Length); var complexSourceItem = item.PropertyType.GetProperty(rightName); if (complexSourceItem != null && complexSourceItem.CanRead) return Property(Property(sourceProperty, item), complexSourceItem);
}
}
} return null;
}
} /// <summary>
/// 用于标注字段 来自哪个表的的哪一列(仅限于有关联的表中) /// </summary>
public class FromEntityAttribute : Attribute
{ /// <summary>
/// 类名(表名) /// </summary>
public string[] EntityNames { get; } /// <summary>
/// 字段(列名) /// </summary>
public string EntityColuum { get; } /// <summary>
/// 列名 + 该列的表名 + 该列的表的上一级表名 /// </summary>
/// <param name="entityColuum"></param>
/// <param name="entityNames"></param>
public FromEntityAttribute(string entityColuum, params string[] entityNames)
{
EntityNames = entityNames;
EntityColuum = entityColuum;
}
}
调用方法如下，先构造测试类
public partial class User
{ public int Id { get; set; }
[Required]
[StringLength(50)] public string Name { get; set; } public int RoleId { get; set; }
[ForeignKey(nameof(RoleId))] public virtual Role Role { get; set; }
}
public partial class Role
{ public int Id { get; set; } public string Name { get; set; } public int DepartmentId { get; set; }
[ForeignKey(nameof(DepartmentId))] public virtual Department Department { get; set; }
} public partial class Department
{ public int Id { get; set; }
[Required]
[StringLength(50)] public string Name { get; set; }
}如上所以构造了，用户表，角色表，和部门表。 查询某个用户 的角色名和部门名 则需要关联 角色表和部门表
public partial class UserModel
{ public string Name { get; set; } public string RoleName { get; set; } //[FromEntity("Name","Role")] //public string RoleName1 { get; set; }
[FromEntity("Name", "Department", "Role")] public string DepartmentName { get; set; } //public virtual RoleModel Role { get; set; } //[FromEntity("Department", "Role")] //public virtual Department Department { get; set; }
}查询代码如下
static void Main(string[] args)
{ using (var context = new TestContext())
{ var list = context.User.Select<UserModel>().ToList();
}
Console.WriteLine($"------------结束--------------------");
Console.ReadLine();
}生成的sql语句 如下图
实体中的 DepartmentName 由于通过用户表关联角色表，再通过角色表关联 部门表得到故 需要通过特性标注
当然结果实体也可以多级关联
public partial class UserModel
{ public string Name { get; set; } public string RoleName { get; set; }
[FromEntity("Name","Role")] public string RoleName1 { get; set; }
[FromEntity("Name", "Department", "Role")] public string DepartmentName { get; set; } public virtual RoleModel Role { get; set; }
[FromEntity("Department", "Role")] public virtual Department Department { get; set; }
} public partial class RoleModel
{ public string Name { get; set; } public string DepartmentName { get; set; } public virtual DepartmentModel Department { get; set; }
} public partial class DepartmentModel
{ public string Name { get; set; }
}生成的查询语句如下图
总结 此方案用在接口，精确查询字段，需要强类型视图的地方相对比较方便
EFCore扩展Select方法(自动映射自定义实体)
通常用操作数据库的时候查询返回的字段是跟 我们的定义的实体是不一致的，所以往往针对UI或者接口层创建大量的Model, 而且需要手动对应字段，非常繁琐。 本文将通过表达式树解决这些重复的过程。
先贴上实现代码
Queryable 类中 的扩展方法 Select<TSource, TResult>(this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector) 需要参数 Expression<Func<TSource, TResult>> selector 只要构造相应的表达式树即可实现自定义映射
using System.Collections; using System.ComponentModel.DataAnnotations.Schema; using System.Linq.Expressions; using System.Reflection; using static System.Linq.Expressions.Expression; public static class QueryableExtentions
{ public static IQueryable<TTarget> Select<TTarget>(this IQueryable<object> query)
{ return Queryable.Select(query, GetLamda<object, TTarget>(query.GetType().GetGenericArguments()[0]));
} public static IQueryable<TTarget> Select<TSource, TTarget>(this IQueryable<TSource> query)
{ return Queryable.Select(query, GetLamda<TSource, TTarget>());
} public static Expression<Func<TSource, TTarget>> GetLamda<TSource, TTarget>(Type type = null)
{ var sourceType = typeof(TSource); var targetType = typeof(TTarget); var parameter = Parameter(sourceType);
Expression propertyParameter; if (type != null)
{
propertyParameter = Convert(parameter, type);
sourceType = type;
} else
propertyParameter = parameter; return Lambda<Func<TSource, TTarget>>(GetExpression(propertyParameter, sourceType, targetType), parameter);
} public static MemberInitExpression GetExpression(Expression parameter, Type sourceType, Type targetType)
{ var memberBindings = new List<MemberBinding>(); foreach (var targetItem in targetType.GetProperties().Where(x => x.CanWrite))
{ var fromEntityAttr = targetItem.GetCustomAttribute<FromEntityAttribute>(); if (fromEntityAttr != null)
{ var property = GetFromEntityExpression(parameter, sourceType, fromEntityAttr); if (property != null)
memberBindings.Add(Bind(targetItem, property)); continue;
} var sourceItem = sourceType.GetProperty(targetItem.Name); if (sourceItem == null)//当没有对应的属性时，查找 实体名+属性 { var complexSourceItemProperty = GetCombinationExpression(parameter, sourceType, targetItem); if (complexSourceItemProperty != null)
memberBindings.Add(Bind(targetItem, complexSourceItemProperty)); continue;
} //判断实体的读写权限
if (sourceItem == null || !sourceItem.CanRead) continue; //标注NotMapped特性的属性忽略转换
if (sourceItem.GetCustomAttribute<NotMappedAttribute>() != null) continue; var sourceProperty = Property(parameter, sourceItem); //当非值类型且类型不相同时
if (!sourceItem.PropertyType.IsValueType && sourceItem.PropertyType != targetItem.PropertyType && targetItem.PropertyType != targetType)
{ //判断都是(非泛型、非数组)class
if (sourceItem.PropertyType.IsClass && targetItem.PropertyType.IsClass && !sourceItem.PropertyType.IsArray && !targetItem.PropertyType.IsArray && !sourceItem.PropertyType.IsGenericType && !targetItem.PropertyType.IsGenericType)
{ var expression = GetExpression(sourceProperty, sourceItem.PropertyType, targetItem.PropertyType);
memberBindings.Add(Bind(targetItem, expression));
} continue;
} if (targetItem.PropertyType != sourceItem.PropertyType) continue;
memberBindings.Add(Bind(targetItem, sourceProperty));
} return MemberInit(New(targetType), memberBindings);
} /// <summary>
/// 根据FromEntityAttribute 的值获取属性对应的路径 /// </summary>
/// <param name="sourceProperty"></param>
/// <param name="sourceType"></param>
/// <param name="fromEntityAttribute"></param>
/// <returns></returns>
private static Expression GetFromEntityExpression(Expression sourceProperty, Type sourceType, FromEntityAttribute fromEntityAttribute)
{ var findType = sourceType; var resultProperty = sourceProperty; var tableNames = fromEntityAttribute.EntityNames; if (tableNames == null)
{ var columnProperty = findType.GetProperty(fromEntityAttribute.EntityColuum); if (columnProperty == null) return null; else
return Property(resultProperty, columnProperty);
} for (int i = tableNames.Length - 1; i >= 0; i--)
{ var tableProperty = findType.GetProperty(tableNames[i]); if (tableProperty == null) return null;
findType = tableProperty.PropertyType;
resultProperty = Property(resultProperty, tableProperty);
} var property = findType.GetProperty(fromEntityAttribute.EntityColuum); if (property == null) return null; else
return Property(resultProperty, property);
} /// <summary>
/// 根据组合字段获取其属性路径 /// </summary>
/// <param name="sourceProperty"></param>
/// <param name="sourcePropertys"></param>
/// <param name="targetItem"></param>
/// <returns></returns>
private static Expression GetCombinationExpression(Expression sourceProperty, Type sourceType, PropertyInfo targetItem)
{ foreach (var item in sourceType.GetProperties().Where(x => x.CanRead))
{ if (targetItem.Name.StartsWith(item.Name))
{ if (item != null && item.CanRead && item.PropertyType.IsClass && !item.PropertyType.IsGenericType)
{ var rightName = targetItem.Name.Substring(item.Name.Length); var complexSourceItem = item.PropertyType.GetProperty(rightName); if (complexSourceItem != null && complexSourceItem.CanRead) return Property(Property(sourceProperty, item), complexSourceItem);
}
}
} return null;
}
} /// <summary>
/// 用于标注字段 来自哪个表的的哪一列(仅限于有关联的表中) /// </summary>
public class FromEntityAttribute : Attribute
{ /// <summary>
/// 类名(表名) /// </summary>
public string[] EntityNames { get; } /// <summary>
/// 字段(列名) /// </summary>
public string EntityColuum { get; } /// <summary>
/// 列名 + 该列的表名 + 该列的表的上一级表名 /// </summary>
/// <param name="entityColuum"></param>
/// <param name="entityNames"></param>
public FromEntityAttribute(string entityColuum, params string[] entityNames)
{
EntityNames = entityNames;
EntityColuum = entityColuum;
}
}
调用方法如下，先构造测试类
public partial class User
{ public int Id { get; set; }
[Required]
[StringLength(50)] public string Name { get; set; } public int RoleId { get; set; }
[ForeignKey(nameof(RoleId))] public virtual Role Role { get; set; }
}
public partial class Role
{ public int Id { get; set; } public string Name { get; set; } public int DepartmentId { get; set; }
[ForeignKey(nameof(DepartmentId))] public virtual Department Department { get; set; }
} public partial class Department
{ public int Id { get; set; }
[Required]
[StringLength(50)] public string Name { get; set; }
}如上所以构造了，用户表，角色表，和部门表。 查询某个用户 的角色名和部门名 则需要关联 角色表和部门表
public partial class UserModel
{ public string Name { get; set; } public string RoleName { get; set; } //[FromEntity("Name","Role")] //public string RoleName1 { get; set; }
[FromEntity("Name", "Department", "Role")] public string DepartmentName { get; set; } //public virtual RoleModel Role { get; set; } //[FromEntity("Department", "Role")] //public virtual Department Department { get; set; }
}查询代码如下
static void Main(string[] args)
{ using (var context = new TestContext())
{ var list = context.User.Select<UserModel>().ToList();
}
Console.WriteLine($"------------结束--------------------");
Console.ReadLine();
}生成的sql语句 如下图
实体中的 DepartmentName 由于通过用户表关联角色表，再通过角色表关联 部门表得到故 需要通过特性标注
当然结果实体也可以多级关联
public partial class UserModel
{ public string Name { get; set; } public string RoleName { get; set; }
[FromEntity("Name","Role")] public string RoleName1 { get; set; }
[FromEntity("Name", "Department", "Role")] public string DepartmentName { get; set; } public virtual RoleModel Role { get; set; }
[FromEntity("Department", "Role")] public virtual Department Department { get; set; }
} public partial class RoleModel
{ public string Name { get; set; } public string DepartmentName { get; set; } public virtual DepartmentModel Department { get; set; }
} public partial class DepartmentModel
{ public string Name { get; set; }
}生成的查询语句如下图
总结 此方案用在接口，精确查询字段，需要强类型视图的地方相对比较方便