EFCore Extended Select Method (Customize Query Statements to Entities)
Often when working with databases, queries return fields that are inconsistent with what we define as entities, so it is cumbersome to create a large number of models for UI or interface layers and to manually correspond to the fields.This article addresses these repetitive processes through an expression tree.(
Paste implementation code first
The extension method Select <TSource, TResult> (this IQueryable <TSource> source, Expression <Func <TSource, TResult > selector) in the Queryable class requires the parameter Expression <Func <TSource, TResult > selector to implement a custom mapping simply by constructing the corresponding expression tree
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)//Find entity names when there is no corresponding attribute+attribute { var complexSourceItemProperty = GetCombinationExpression(parameter, sourceType, targetItem); if (complexSourceItemProperty != null) memberBindings.Add(Bind(targetItem, complexSourceItemProperty)); continue; } //Determine read and write permissions for entities if (sourceItem == null || !sourceItem.CanRead) continue; //Tagging NotMapped Attributes of attributes ignore conversions if (sourceItem.GetCustomAttribute<NotMappedAttribute>() != null) continue; var sourceProperty = Property(parameter, sourceItem); //When non-value types and types are not the same if (!sourceItem.PropertyType.IsValueType && sourceItem.PropertyType != targetItem.PropertyType && targetItem.PropertyType != targetType) { //All judgements are(Non-generic, non-array)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> /// according to FromEntityAttribute Get the path of the property by its value /// </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> /// Get its attribute path from a combination field /// </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> /// Used to label which column of which table the field comes from(Only in related tables) /// </summary> public class FromEntityAttribute : Attribute { /// <summary> /// Class name(Table Name) /// </summary> public string[] EntityNames { get; } /// <summary> /// field(Column Name) /// </summary> public string EntityColuum { get; } /// <summary> /// Column Name + Table name of the column + The name of the table above the column /// </summary> /// <param name="entityColuum"></param> /// <param name="entityNames"></param> public FromEntityAttribute(string entityColuum, params string[] entityNames) { EntityNames = entityNames; EntityColuum = entityColuum; } }
Call the method as follows, first construct the test class
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; } }
The user table, role table, and department table are constructed as above.Querying a user's role and department name requires an associated role and department table
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; } }
The query code is as follows
static void Main(string[] args) { using (var context = new TestContext()) { var list = context.User.Select<UserModel>().ToList(); } Console.WriteLine($"------------End--------------------"); Console.ReadLine(); }
The generated sql statement is shown below
DepartmentName in an entity requires attribute labeling because it relates the role table through the user table and then the Department table through the role table
Of course, result entities can also be multilevel related
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; } }
The generated query statement is shown below
It is relatively convenient to summarize this scheme for interface, precise query of fields, and where a strongly typed view is required
Author: costyuan
GitHub address: https://github.com/bieyuan/EFCoreSelectExtentions
Address: https://www.cnblogs.com/castyuan/p/10186619.html
Copyright of this article belongs to both the author and the blog park. Reproduction is welcomed, but this statement must be retained without the author's consent, and the original text link must be provided in an obvious place on the article page, otherwise the right to be prosecuted for legal responsibility is reserved.(
If there are any errors in this article, please point out, thank you!(
Origin: https://www.cnblogs.com/castyuan/p/10186619.html