EFCore扩展Select方法(根据实体定制查询语句)
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; } }
生成的查询语句如下图
总结 此方案用在接口，精确查询字段，需要强类型视图的地方相对比较方便
作者：costyuan
GitHub地址：https://github.com/bieyuan/EFCoreSelectExtentions
地址：https://www.cnblogs.com/castyuan/p/10186619.html
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
如果文中有什么错误，欢迎指出，谢谢！
