问题描述

我对具有多个链接表的数据库进行 LINQ 查询，并且需要根据输入返回 (SELECT) 不同的字段.

I have a LINQ query to a database, with multiple linked tables, and need to return (SELECT) different fields depending on inputs.

ClassA has ParamA, ParamB, and ICollection<ClassB>ClassBs
ClassB has ParamD, ParamE

Linq 查询部分:

.Select(c => new ClassA()
    {
    ParamA = c.ParamA,
    ParamB = c.ParamB,
    ClassBs = c.ClassBs.Select(p => new ClassB()
        {
        ParamD = p.ParamD,
        ParamE = p.ParamE
        }).ToList()
    }).ToList();

在某些调用中，我只需要 ParamA 和 ParamE.在其他调用中，可能是 ParamB 和 ParamE.我已经能够使用表达式完成这项工作，但不能用于 ICollection.我尝试使用 Dynamic.Linq.Core，但在 SELECT 中找不到任何新的示例.我更愿意用 MemberExpressions 来做这件事...

On some calls, I'll want ParamA and ParamE only. On other calls, perhaps ParamB and ParamE. I've been able to make this work using Expressions but not for the ICollection. I attempted using Dynamic.Linq.Core, but couldn't find any examples for newing up in the SELECT. I would prefer to do this with MemberExpressions...

[更新]更多上下文:ClassA 和 ClassB 本质上是指向 SQL 表的 EF 模型.ClassA 与 ClassB 具有一对多的关系，因此，我以这种方式查询它们.ClassA 将是学生记录(姓名、地址、家庭等)，ClassB 将是测试记录，其中学生可能有多个测试，每个测试都有参加日期、等级、highest_score_of_class、lowest_score_of_class 等等.

[UPDATE] A bit more context: ClassA and ClassB are essentially EF Models pointing to SQL tables. ClassA has a one-to-many relationship to ClassB, thus, why I query them in this fashion. ClassA would be a Student record (name, address, home, etc), ClassB would be a Test record, where student may have more than one test, each test has date taken, grade, highest_score_of_class, lowest_score_of_class, and many more.

我并不总是想要两个表的所有字段，因为可能有 100 万条记录，因此，为什么我更喜欢只选择特定查询和请求的操作所需的内容.

I don't always want all the fields of both tables, as there may be 1 million records, thus, why I prefer to SELECT only what is needed for the specific query and operation requested.

推荐答案

这可能有点冗长，但表达式可以做到这一点，例如:

This may be a bit verbose, but Expressions can get that way, for example:

var typeClassA = typeof(ClassA);
var typeClassB = typeof(ClassB);

var parameterExpressionP = Expression.Parameter(typeClassB, "p");
var newExpression = Expression.New(typeClassB);

var paramDPropertyExpression = Expression.Property(parameterExpressionP, "ParamD");
var paramDMemberBinding = Expression.Bind(typeClassB.GetProperty("ParamD"), paramDPropertyExpression);

var paramEPropertyExpression = Expression.Property(parameterExpressionP, "ParamE");
var paramEMemberBinding = Expression.Bind(typeClassB.GetProperty("ParamE"), paramEPropertyExpression);

var memberInitExpression = Expression.MemberInit(
    newExpression,
    paramDMemberBinding, paramEMemberBinding);

var projectionExpression = Expression.Lambda<Func<ClassB, ClassB>>(memberInitExpression, parameterExpressionP);

var parameterExpressionC = Expression.Parameter(typeClassA, "c");
var selectParamExpression = Expression.Property(parameterExpressionC, "ClassBs");

var selectExpression = Expression.Call(
    typeof(Enumerable),
    nameof(Enumerable.Select),
    new[] { typeClassB, typeClassB },
    selectParamExpression, projectionExpression);

var toListExpression = Expression.Call(
    typeof(Enumerable),
    nameof(Enumerable.ToList),
    new[] { typeClassB },
    selectExpression);

这将创建一个类似的表达式:

This will create an expression something like:

c.ClassBs.Select(p => new ClassB() {ParamD = p.ParamD, ParamE = p.ParamE}).ToList()

这篇关于在运行时动态选择 LINQ SELECT 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！