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; }
    }

生成的查询语句如下图

总结 此方案用在接口，精确查询字段，需要强类型视图的地方相对比较方便

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; }
    }

生成的查询语句如下图

总结 此方案用在接口，精确查询字段，需要强类型视图的地方相对比较方便

原文出处：https://www.cnblogs.com/castyuan/p/10186619.html