从linq动态构建选择列表到实体查询 [英] Dynamically build select list from linq to entities query

问题描述

我正在寻找一种从iQueryable对象动态创建选择列表的方法.

I'm looking for a way to dynamically create a select list from a iQueryable object.

具体示例，我想做以下事情:

Concrete example, i want to do something like the following:

public void CreateSelectList(IQueryable(of EntityModel.Core.User entities), string[] columns)
{
    foreach(var columnID in columns)
    {
        switch(columnID)
        {
            case "Type":
                SelectList.add(e => e.UserType);
                break;
            case "Name":
                SelectList.add(e => e.Name);
                break;
            etc....
        }
    }
    var selectResult = (from u in entities select objSelectList);
}

所以所有属性都是已知的，但是我事先不知道要选择哪些属性.这将通过columns参数传递.

So all properties are known, i however don't know beforehand what properties are to be selected. That will be passed via the columns parameter.

我知道我要去与调用selectResult类型的类型遇到问题，因为在选择列表是动态的，编译器不知道是什么的匿名类型需要的属性是.

I know i'm going to run into issues with the type of the selectResult type, because when the select list is dynamic, the compiler doesn't know what the properties of the anonymous type needs to be.

如果上述操作不可行:我需要的方案如下:
我正在尝试创建一个可以实现以显示页面调度/已过滤数据列表的类.此数据可以是任何数据(取决于实现).使用的linq是实体的linq.因此它们直接链接到sql数据.现在，我只想选择我实际上在列表中显示的实体的列.因此，我希望选择是动态的.我的实体可能有一百个属性，但是如果列表中仅显示其中三个，则我不想生成一个查询，该查询选择所有100列的数据，然后仅使用其中的三个.如果有我未曾想到的其他方法，那么我愿意接受想法

If the above is not possible: The scenario I need it for is the following:
I'm trying to create a class that can be implemented to display a paged/filtered list of data. This data can be anything (depends on the implementations).The linq used is linq to entities. So they are directly linked to sql data. Now i want to only select the columns of the entities that i am actually showing in the list. Therefore i want the select to be dynamic. My entity might have a hundred properties, but if only 3 of them are shown in the list, i don't want to generate a query that selects the data of all 100 columns and then only uses 3 of them. If there is a different approach that I haven't thought of, I'm open to ideas

有关矛盾的一些说明:
-查询需要对实体使用linq(请参阅问题主题)
-一个实体可能包含100列，因此选择所有列"，然后仅读取我需要的列是不可行的.
-最终用户决定要显示的列，因此要在运行时确定要选择的列
-我需要创建一个单选，具有多个选择语句意味着对数据库进行多个查询，这是我不希望的

Some clarifications on the contraints:
- The query needs to work with linq to entities (see question subject)
- an entity might contain 100 columns, so selecting ALL columns and then only reading the ones i need is not an option.
- The end user decides what columns to show, so the columns to select are determined at run time
- i need to create a SINGLE select, having multiple select statements means having multiple queries on the database, which i don't want

推荐答案

可以使用 Expression.Bind 方法.

Dynamic select expression to a compile time known type can easily be build using Expression.MemberInit method with MemberBindings created using the Expression.Bind method.

这是一个自定义扩展方法，可以执行以下操作:

Here is a custom extension method that does that:

public static class QueryableExtensions
{
    public static IQueryable<TResult> Select<TResult>(this IQueryable source, string[] columns)
    {
        var sourceType = source.ElementType;
        var resultType = typeof(TResult);
        var parameter = Expression.Parameter(sourceType, "e");
        var bindings = columns.Select(column => Expression.Bind(
            resultType.GetProperty(column), Expression.PropertyOrField(parameter, column)));
        var body = Expression.MemberInit(Expression.New(resultType), bindings);
        var selector = Expression.Lambda(body, parameter);
        return source.Provider.CreateQuery<TResult>(
            Expression.Call(typeof(Queryable), "Select", new Type[] { sourceType, resultType },
                source.Expression, Expression.Quote(selector)));
    }
}

唯一的问题是TResult类型是什么.在 EF Core 中，您可以传递实体类型(例如您的示例中的EntityModel.Core.User)，它将起作用.在 EF 6 及更早版本中，您需要一个单独的非实体类型，因为否则会得到NotSupportedException-该实体或复杂类型无法在LINQ to Entities查询中构造.

The only problem is what is the TResult type. In EF Core you can pass the entity type (like EntityModel.Core.User in your example) and it will work. In EF 6 and earlier, you need a separate non entity type because otherwise you'll get NotSupportedException - The entity or complex type cannot be constructed in a LINQ to Entities query.

更新:如果您希望摆脱字符串列，建议您将扩展方法替换为以下类:

UPDATE: If you want a to get rid of the string columns, I can suggest you replacing the extension method with the following class:

public class SelectList<TSource>
{
    private List<MemberInfo> members = new List<MemberInfo>();
    public SelectList<TSource> Add<TValue>(Expression<Func<TSource, TValue>> selector)
    {
        var member = ((MemberExpression)selector.Body).Member;
        members.Add(member);
        return this;
    }
    public IQueryable<TResult> Select<TResult>(IQueryable<TSource> source)
    {
        var sourceType = typeof(TSource);
        var resultType = typeof(TResult);
        var parameter = Expression.Parameter(sourceType, "e");
        var bindings = members.Select(member => Expression.Bind(
            resultType.GetProperty(member.Name), Expression.MakeMemberAccess(parameter, member)));
        var body = Expression.MemberInit(Expression.New(resultType), bindings);
        var selector = Expression.Lambda<Func<TSource, TResult>>(body, parameter);
        return source.Select(selector);
    }
}

带有示例用法:

var selectList = new SelectList<EntityModel.Core.User>();
selectList.Add(e => e.UserType);
selectList.Add(e => e.Name);

var selectResult = selectList.Select<UserDto>(entities);

这篇关于从linq动态构建选择列表到实体查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！