表达式树错误为 IQueryable，但工作为 IEnumerable

本文关键字：工作 IEnumerable IQueryable 错误 表达式 | 更新日期: 2023-09-27 18:33:29

我第一次尝试使用 LINQ 查询的表达式树让我陷入困境。 下面是在没有表达式树的情况下工作的查询：

IQueryable<SampleResult> samples = samples.Select(a => new
{
   a = a,
   innerQuery = _dc.RequestedTests
   .SelectMany(
       b => _dc.ResultData.Where(x => (x.TestNum == b.TestNum && b.SampleID == a.SampleID))
       .DefaultIfEmpty(),
       (b, c) => new RequestedTestsJoinedResultData
       {
            RequestedTests = b,
            ResultData = c
       }).Where(joinedTable => ((joinedTable.ResultData.ResultID == 1) &&
                                (joinedTable.RequestedTests.TestID == 38) &&
                                (joinedTable.ResultData.IntValue >= (Int32?) 90))
    ).Select(joinedTable => joinedTable.RequestedTests.SampleID)
}).Where(temp0 => temp0.innerQuery.Contains(temp0.a.SampleID)).Select(temp0 => temp0.a);

我的下一步是构造一个表达式树，以发送到中间Where()调用。 需要表达式树才能根据用户输入动态创建筛选器。 

var joinedTableParameter = Expression.Parameter(typeof(RequestedTestsJoinedResultData), 
    "joinedTable");
var left = Expression.Property(joinedTableParameter,
    typeof(RequestedTestsJoinedResultData).GetProperty("ResultData"));
left = Expression.Property(left,
    typeof(ResultData).GetProperty("ResultID"));
var rightConstant = Expression.Constant(resultFilter.ResultID);
Expression e1 = Expression.Equal(left, rightConstant);
left = Expression.Property(joinedTableParameter, typeof(RequestedTestsJoinedResultData)
    .GetProperty("RequestedTests"));
left = Expression.Property(left, typeof(RequestedTests)
    .GetProperty("TestID"));
rightConstant = Expression.Constant(resultFilter.TestID);
Expression e2 = Expression.Equal(left, rightConstant);
var preditcateBody = Expression.AndAlso(e1, e2);
left = Expression.Property(joinedTableParameter, typeof(RequestedTestsJoinedResultData)
    .GetProperty("ResultData"));
left = Expression.Property(left, typeof(ResultData)
    .GetProperty(comparisonColumn));
rightConstant = Expression.Constant(resultFilter.ResultValue, type);
Expression e3 = Expression.MakeBinary(resultFilter.ResultComparison, 
    left, rightConstant);
preditcateBody = Expression.AndAlso(preditcateBody, e3);
var lambda = Expression.Lambda<Func<RequestedTestsJoinedResultData, bool>>
    (preditcateBody, joinedTableParameter);

然后，第一个代码块从中更改：

.Where(joinedTable => ((joinedTable.ResultData.ResultID == 1) &&
                       (joinedTable.RequestedTests.TestID == 38) &&
                       (joinedTable.ResultData.IntValue >= (Int32?)90))

对此：

.Where(lambda)

第一个代码块可以IQueryable工作，但是当使用表达式树(第二个代码块(时，它仅在我将其更改为IEnumerable时才有效。 使用IQueryable时出现此运行时错误：

类型为"系统.不支持的异常"发生在 System.Data.Linq.dll但未在用户代码中处理

其他信息：用于查询运算符的不受支持的重载 "哪里"。

堆栈跟踪：

在 System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc(''r' at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression 节点(''r' 在 System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression 节点(''r' 在 System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query， SqlNodeAnnotations annotations(''r' at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression 查询(''r' 在 System.Data.Linq.DataQuery 1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()'r'n at System.Linq.SystemCore_EnumerableDebugView 1.get_Items((">

编辑：

1：我的自定义数据模型类：

public class RequestedTestsJoinedResultData
{
    public virtual RequestedTests RequestedTests { get; set; }
    public virtual ResultData ResultData { get; set; }
}

2：lambda的调试视图：

其中从表达式树动态创建的子句 lambda：

.Lambda #Lambda1<System.Func`2[SoDak.Domain.Models.RequestedTestsJoinedResultData,System.Boolean]>
(SoDak.Domain.Models.RequestedTestsJoinedResultData $joinedTable) 
($joinedTable.ResultDataTable).ResultID == 1 &&  
($joinedTable.RequestedTestsTable).TestID == 38 &&  
($joinedTable.ResultDataTable).IntValue >=  
.Constant<System.Nullable`1[System.Int32]>(90)

具有硬编码结果过滤器的大 lambda：

.Call System.Linq.Queryable.Select(.Call System.Linq.Queryable.Where(
    .Call System.Linq.Queryable.Select(.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Where( .Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.SelectMany( .Call System.Linq.Queryable.Where(
    .Constant(Table(Samples)), '(.Lambda #Lambda1)), '(.Lambda #Lambda2),
    '(.Lambda #Lambda3)), '(.Lambda #Lambda4f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>)) ,
    '(.Lambda #Lambda5f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>)) ,
    '(.Lambda #Lambda6f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>)) ,
    '(.Lambda #Lambda7f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>)) ,
    '(.Lambda #Lambda8f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>)) ,
    '(.Lambda #Lambda9f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>))
    .Lambda #Lambda1(SoDak.Domain.Samples $a) {
    ($a.StatusID == (System.Nullable`1[System.Int32])3 ||
    $a.StatusID == (System.Nullable`1[System.Int32])4) &&
    $a.Lab == .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).labID }
    .Lambda #Lambda2(SoDak.Domain.Samples $a) {
    .Call System.Linq.Queryable.DefaultIfEmpty(
    .Call System.Linq.Queryable.Where( (.Constant(SoDak.Domain.Services.SampleService)._dc).CustomFields, '(.Lambda #Lambda10))) 
    }
    .Lambda #Lambda3( SoDak.Domain.Samples $a, SoDak.Domain.CustomFields $b) {
    .New SoDak.Domain.Models.SampleResult(){
    LabCount = (System.Int32)$a.LabCount, SampleID = $a.SampleID,
    AccountID = (System.Int32)$a.AccountID,Received = (System.Nullable`1[System.DateTime])(($a.Received).Value).Date,
    Completed = (System.Nullable`1[System.DateTime])(($a.Completed).Value).Date,
    SpeciesID = (System.Nullable`1[System.Int32])$a.SpeciesID,
    SpeciesCommonName = ($a.SeedNames).CommonName,StatusID = (System.Int32)$a.StatusID,
    StatusName = ($a.SampleStatus).StatusName,Variety = $a.Variety,Lot = $a.Lot,
    CarryOver = $a.CarryOver,Lab = (System.Int32)$a.Lab,LabID = (System.Int32)$a.Lab,
    TestList = $a.TestList,CustomFieldName = $b.CustomFieldName,CustomFieldNameValue = $b.CustomFieldNameValue
    }
    } .Lambda #Lambda4f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>(SoDak.Domain.Models.SampleResult $a) {
    .New <>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]](
    $a, .Call System.Linq.Queryable.Select( .Call System.Linq.Queryable.Where( (.Constant(SoDak.Domain.Services.SampleService)._dc).SubAccounts,
    '(.Lambda #Lambda11)), '(.Lambda #Lambda12))) }
    .Lambda #Lambda5f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],
    System.Boolean]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,
    System.Linq.IQueryable`1[System.Int32]] $<>h__TransparentIdentifier0) {
    ($<>h__TransparentIdentifier0.a).AccountID == .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).accountIDLoggedIn ||
    .Call System.Linq.Queryable.Contains( $<>h__TransparentIdentifier0.innerQuery,
    .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).accountIDLoggedIn) }
    .Lambda #Lambda6f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],
    SoDak.Domain.Models.SampleResult]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $<>h__TransparentIdentifier0) {
    $<>h__TransparentIdentifier0.a } .Lambda #Lambda7f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>(
    SoDak.Domain.Models.SampleResult $a) { .New <>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]](
    $a, .Call System.Linq.Queryable.Select( .Call System.Linq.Queryable.Where( .Call System.Linq.Queryable.SelectMany(
    (.Constant(SoDak.Domain.Services.SampleService)._dc).RequestedTests, '(.Lambda #Lambda13), '(.Lambda #Lambda14)) , '(.Lambda #Lambda15)), '(.Lambda #Lambda16))) }
    .Lambda #Lambda8f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $temp0) {
    .Call System.Linq.Queryable.Contains( $temp0.innerQuery, ($temp0.a).SampleID) }
    .Lambda #Lambda9f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $temp0) { $temp0.a }
    .Lambda #Lambda10(SoDak.Domain.CustomFields $x) {
    $x.SampleID == $a.SampleID && $x.OnReport == (System.Nullable`1[System.Boolean])True } .Lambda #Lambda11(SoDak.Domain.SubAccounts $b) {
    $b.SampleID == $a.SampleID } .Lambda #Lambda12(SoDak.Domain.SubAccounts $b) { $b.AccountID } .Lambda #Lambda13(SoDak.Domain.RequestedTests $b) {
    .Call System.Linq.Queryable.DefaultIfEmpty(.Call System.Linq.Queryable.Where( (.Constant(SoDak.Domain.Services.SampleService)._dc).ResultData, '(.Lambda #Lambda17))) }
    .Lambda #Lambda14( SoDak.Domain.RequestedTests $b, SoDak.Domain.ResultData $c) { .New SoDak.Domain.Models.RequestedTestsJoinedResultData(){
    RequestedTestsTable = $b, ResultDataTable = $c } } .Lambda #Lambda15(SoDak.Domain.Models.RequestedTestsJoinedResultData $joinedTable) {
    $joinedTable != null && ($joinedTable.ResultDataTable).ResultID == 1 && ($joinedTable.RequestedTestsTable).TestID == 38 && ($joinedTable.ResultDataTable).IntValue >= (System.Nullable`1[System.Int32])90 }
    .Lambda #Lambda16(SoDak.Domain.Models.RequestedTestsJoinedResultData $joinedTable) { ($joinedTable.RequestedTestsTable).SampleID } 
    .Lambda #Lambda17(SoDak.Domain.ResultData $x) { $x.TestNum == $b.TestNum && $b.SampleID == $a.SampleID }

通过表达式树构建动态创建的结果过滤器的大型 lambda。

.Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.Where(
.Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.Where(
.Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.SelectMany(
.Call System.Linq.Queryable.Where(
.Constant(Table(Samples)),
'(.Lambda #Lambda1)), '(.Lambda #Lambda2), '(.Lambda #Lambda3)),
'(.Lambda #Lambda4f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>)) ,
'(.Lambda #Lambda5f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>)) ,
'(.Lambda #Lambda6f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>)) ,
'(.Lambda #Lambda7f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>)) ,
'(.Lambda #Lambda8f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>)) ,
'(.Lambda #Lambda9f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>))
.Lambda #Lambda1(SoDak.Domain.Samples $a) { ($a.StatusID == (System.Nullable`1[System.Int32])3 || $a.StatusID == (System.Nullable`1[System.Int32])4) &&
$a.Lab == .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).labID }
.Lambda #Lambda2(SoDak.Domain.Samples $a) {
.Call System.Linq.Queryable.DefaultIfEmpty(.Call System.Linq.Queryable.Where( (.Constant(SoDak.Domain.Services.SampleService)._dc).CustomFields, '(.Lambda #Lambda10))) }
.Lambda #Lambda3( SoDak.Domain.Samples $a, SoDak.Domain.CustomFields $b) {
.New SoDak.Domain.Models.SampleResult(){
LabCount = (System.Int32)$a.LabCount,
SampleID = $a.SampleID, AccountID = (System.Int32)$a.AccountID,
Received = (System.Nullable`1[System.DateTime])(($a.Received).Value).Date,
Completed = (System.Nullable`1[System.DateTime])(($a.Completed).Value).Date,
SpeciesID = (System.Nullable`1[System.Int32])$a.SpeciesID,
SpeciesCommonName = ($a.SeedNames).CommonName,StatusID = (System.Int32)$a.StatusID,
StatusName = ($a.SampleStatus).StatusName,Variety = $a.Variety, Lot = $a.Lot,
CarryOver = $a.CarryOver,Lab = (System.Int32)$a.Lab,LabID = (System.Int32)$a.Lab,
TestList = $a.TestList,CustomFieldName = $b.CustomFieldName,CustomFieldNameValue = $b.CustomFieldNameValue } }
.Lambda #Lambda4f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>(SoDak.Domain.Models.SampleResult $a) {
.New <>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]](
$a,
.Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.Where( (.Constant(SoDak.Domain.Services.SampleService)._dc).SubAccounts,
'(.Lambda #Lambda11)), '(.Lambda #Lambda12))) }
.Lambda #Lambda5f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>
(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $<>h__TransparentIdentifier0) {
($<>h__TransparentIdentifier0.a).AccountID == .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).accountIDLoggedIn ||
.Call System.Linq.Queryable.Contains( $<>h__TransparentIdentifier0.innerQuery, .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).accountIDLoggedIn) }
.Lambda #Lambda6f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $<>h__TransparentIdentifier0) { $<>h__TransparentIdentifier0.a }
.Lambda #Lambda7f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>(SoDak.Domain.Models.SampleResult $a) { .New <>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]](
$a, .Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.Where(
.Call System.Linq.Queryable.SelectMany( (.Constant(SoDak.Domain.Services.SampleService)._dc).RequestedTests,
'(.Lambda #Lambda13), '(.Lambda #Lambda14)) ,
.Constantc__DisplayClass24_1>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_1).lambda) ,
'(.Lambda #Lambda15))) } .Lambda #Lambda8f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $temp0) {
.Call System.Linq.Queryable.Contains( $temp0.innerQuery, ($temp0.a).SampleID) }
.Lambda #Lambda9f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $temp0) {
$temp0.a }
.Lambda #Lambda10(SoDak.Domain.CustomFields $x) {
$x.SampleID == $a.SampleID && $x.OnReport == (System.Nullable`1[System.Boolean])True }
.Lambda #Lambda11(SoDak.Domain.SubAccounts $b) { $b.SampleID == $a.SampleID }
.Lambda #Lambda12(SoDak.Domain.SubAccounts $b) { $b.AccountID }
.Lambda #Lambda13(SoDak.Domain.RequestedTests $b) {
.Call System.Linq.Queryable.DefaultIfEmpty(.Call System.Linq.Queryable.Where( (.Constant(SoDak.Domain.Services.SampleService)._dc).ResultData, '(.Lambda #Lambda16))) }
.Lambda #Lambda14( SoDak.Domain.RequestedTests $b, SoDak.Domain.ResultData $c) {
.New SoDak.Domain.Models.RequestedTestsJoinedResultData(){ RequestedTestsTable = $b, ResultDataTable = $c } }
.Lambda #Lambda15(SoDak.Domain.Models.RequestedTestsJoinedResultData $joinedTable) {
($joinedTable.RequestedTestsTable).SampleID } 
.Lambda #Lambda16(SoDak.Domain.ResultData $x) { $x.TestNum == $b.TestNum && $b.SampleID == $a.SampleID }

3：我继续使用System.Linq.Dynamic尝试相同的查询，并得到另一个可能相关也可能不相关的错误。

使用动态 LINQ 的 where 子句：

.Where("joinedTable => " + 
"((joinedTable.ResultDataTable.ResultID == " + resultFilter.ResultID + ") && " +
"(joinedTable.RequestedTestsTable.TestID == " + resultFilter.TestID + ") && " +
"(joinedTable.ResultDataTable." + resultFilter.ResultType + " >= " + 
resultFilter.ResultValueString + "))")

错误：

成员访问 'SoDak.Domain.RequestTests RequestTestsTable' 的 'SoDak.Domain.Models.RequestTestsJoinedResultData' 在类型上不合法 'System.Linq.IQueryable'1[SoDak.Domain.Models.RequestTestsJoinedResultData].

4：硬编码结果筛选器时生成的 SQL：

SELECT [t4].[SampleID], [t4].[value] AS [LabCount], [t4].[value2] AS [Lab], [t4].[value3] AS [LabID], [t4].[value4] AS [AccountID], [t4].[value5] AS [Received], [t4].[value6] AS [Completed], [t4].[value7] AS [SpeciesID], [t4].[CommonName] AS [SpeciesCommonName], [t4].[value8] AS [StatusID], [t4].[StatusName], [t4].[Variety], [t4].[Lot], [t4].[CarryOver], [t4].[TestList], [t4].[value9] AS [CustomFieldName], [t4].[value10] AS [CustomFieldNameValue]
FROM (
    SELECT [t0].[SampleID], [t0].[LabCount] AS [value], [t0].[Lab] AS [value2], [t0].[Lab] AS [value3], [t0].[AccountID] AS [value4], CONVERT(DATE, [t0].[Received]) AS [value5], CONVERT(DATE, [t0].[Completed]) AS [value6], [t0].[SpeciesID] AS [value7], [t2].[CommonName], [t0].[StatusID] AS [value8], [t3].[StatusName], [t0].[Variety], [t0].[Lot], [t0].[CarryOver], [t0].[TestList], [t1].[CustomFieldName] AS [value9], [t1].[CustomFieldNameValue] AS [value10], [t0].[StatusID], [t0].[Lab]
    FROM [Samples].[Samples] AS [t0]
    LEFT OUTER JOIN [Web].[CustomFields] AS [t1] ON ([t1].[SampleID] = [t0].[SampleID]) AND ([t1].[OnReport] = @p0)
    INNER JOIN [Seeds].[SeedNames] AS [t2] ON [t2].[SpeciesID] = [t0].[SpeciesID]
    LEFT OUTER JOIN [Lookup].[SampleStatus] AS [t3] ON [t3].[StatusID] = [t0].[StatusID]
    ) AS [t4]
WHERE (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Samples].[RequestedTests] AS [t5]
    LEFT OUTER JOIN [Results].[ResultData] AS [t6] ON ([t6].[TestNum] = [t5].[TestNum]) AND ([t5].[SampleID] = [t4].[SampleID])
    WHERE ([t5].[SampleID] = [t4].[SampleID]) AND ([t6].[ResultID] = @p1) AND ([t5].[TestID] = @p2) AND ([t6].[IntValue] >= @p3)
    )) AND (([t4].[value4] = @p4) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Samples].[SubAccounts] AS [t7]
    WHERE ([t7].[AccountID] = @p5) AND ([t7].[SampleID] = [t4].[SampleID])
    ))) AND (([t4].[StatusID] = @p6) OR ([t4].[StatusID] = @p7)) AND ([t4].[Lab] = @p8)

是真的不支持还是我在构造表达式树时做错了什么？

表达式树错误为 IQueryable，但工作为 IEnumerable

简短回答：

我认为让你绊倒的是你对可为空的过滤器的处理。如果您将Expression.Constant包装在可为空的双精度(或整数(周围，我不确定它是否会很好地编译。

下面是 lambda 漂亮的打印的两个调试视图：

长答案

硬编码 

.Call System.Linq.Queryable.Select(
   .Call System.Linq.Queryable.Where(
      .Call System.Linq.Queryable.Select(
         .Call System.Linq.Queryable.Select(
            .Call System.Linq.Queryable.Where(
               .Call System.Linq.Queryable.Select(
                  .Call System.Linq.Queryable.SelectMany(
                     .Call System.Linq.Queryable.Where(
                        .Constant(Table(Samples)),
                        '(.Lambda #Lambda1)
                     ), 
                     '(.Lambda #Lambda2), 
                     '(.Lambda #Lambda3)
                  ),
                  '(.Lambda #Lambda4f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>)
               ),
               '(.Lambda #Lambda5f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>)
            ),
            '(.Lambda #Lambda6f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>)
         ),
         '(.Lambda #Lambda7f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>)
      ),
      '(.Lambda #Lambda8f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>)
   ),
   '(.Lambda #Lambda9f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>)
)
.Lambda #Lambda1(SoDak.Domain.Samples $a) {
   ($a.StatusID == (System.Nullable`1[System.Int32])3 || $a.StatusID == (System.Nullable`1[System.Int32])4) &&
      $a.Lab == .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).labID 
}
.Lambda #Lambda2(SoDak.Domain.Samples $a) {
   .Call System.Linq.Queryable.DefaultIfEmpty(
      .Call System.Linq.Queryable.Where( 
         (.Constant(SoDak.Domain.Services.SampleService)._dc).CustomFields, 
         '(.Lambda #Lambda10)
      )
   ) 
}
.Lambda #Lambda3( SoDak.Domain.Samples $a, SoDak.Domain.CustomFields $b) {
   .New SoDak.Domain.Models.SampleResult(){
      LabCount = (System.Int32)$a.LabCount,
      SampleID = $a.SampleID,
      AccountID = (System.Int32)$a.AccountID,
      Received = (System.Nullable`1[System.DateTime])(($a.Received).Value).Date,
      Completed = (System.Nullable`1[System.DateTime])(($a.Completed).Value).Date,
      SpeciesID = (System.Nullable`1[System.Int32])$a.SpeciesID,
      SpeciesCommonName = ($a.SeedNames).CommonName,
      StatusID = (System.Int32)$a.StatusID,
      StatusName = ($a.SampleStatus).StatusName,
      Variety = $a.Variety,
      Lot = $a.Lot,
      CarryOver = $a.CarryOver,
      Lab = (System.Int32)$a.Lab,
      LabID = (System.Int32)$a.Lab,
      TestList = $a.TestList,
      CustomFieldName = $b.CustomFieldName,
      CustomFieldNameValue = $b.CustomFieldNameValue
   }
}
.Lambda #Lambda4f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>(SoDak.Domain.Models.SampleResult $a) {
   .New <>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]](
      $a,
      .Call System.Linq.Queryable.Select(
         .Call System.Linq.Queryable.Where( 
            (.Constant(SoDak.Domain.Services.SampleService)._dc).SubAccounts,
            '(.Lambda #Lambda11)
         ), 
         '(.Lambda #Lambda12)
      )
   ) 
}
.Lambda #Lambda5f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $<>h__TransparentIdentifier0) {
   ($<>h__TransparentIdentifier0.a).AccountID == .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).accountIDLoggedIn ||
   .Call System.Linq.Queryable.Contains( 
      $<>h__TransparentIdentifier0.innerQuery,
      .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).accountIDLoggedIn
   ) 
}
.Lambda #Lambda6f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $<>h__TransparentIdentifier0) {
   $<>h__TransparentIdentifier0.a 
}
.Lambda #Lambda7f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>(
   SoDak.Domain.Models.SampleResult $a) { 
   .New <>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]](
      $a, 
      .Call System.Linq.Queryable.Select(
         .Call System.Linq.Queryable.Where(
            .Call System.Linq.Queryable.SelectMany(
               (.Constant(SoDak.Domain.Services.SampleService)._dc).RequestedTests, 
               '(.Lambda #Lambda13), 
               '(.Lambda #Lambda14)
            ), 
            '(.Lambda #Lambda15)
         ), 
         '(.Lambda #Lambda16)
      )
   ) 
}
.Lambda #Lambda8f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $temp0) {
   .Call System.Linq.Queryable.Contains( 
      $temp0.innerQuery, 
      ($temp0.a).SampleID
   )
}
.Lambda #Lambda9f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $temp0) { 
   $temp0.a 
}
.Lambda #Lambda10(SoDak.Domain.CustomFields $x) {
   $x.SampleID == $a.SampleID && 
      $x.OnReport == (System.Nullable`1[System.Boolean])True 
}
.Lambda #Lambda11(SoDak.Domain.SubAccounts $b) {
   $b.SampleID == $a.SampleID 
}
.Lambda #Lambda12(SoDak.Domain.SubAccounts $b) {
   $b.AccountID
}
.Lambda #Lambda13(SoDak.Domain.RequestedTests $b) {
   .Call System.Linq.Queryable.DefaultIfEmpty(
      .Call System.Linq.Queryable.Where( 
         (.Constant(SoDak.Domain.Services.SampleService)._dc).ResultData, 
         '(.Lambda #Lambda17)
      )
   )
}
.Lambda #Lambda14( SoDak.Domain.RequestedTests $b, SoDak.Domain.ResultData $c) {
   .New SoDak.Domain.Models.RequestedTestsJoinedResultData(){
      RequestedTestsTable = $b,
      ResultDataTable = $c
   }
}
.Lambda #Lambda16(SoDak.Domain.Models.RequestedTestsJoinedResultData $joinedTable) {
   ($joinedTable.RequestedTestsTable).SampleID
}
.Lambda #Lambda17(SoDak.Domain.ResultData $x) {
   $x.TestNum == $b.TestNum &&
      $b.SampleID == $a.SampleID
}
.Lambda #Lambda15(SoDak.Domain.Models.RequestedTestsJoinedResultData $joinedTable) {
   $joinedTable != null && 
      ($joinedTable.ResultDataTable).ResultID == 1 && 
      ($joinedTable.RequestedTestsTable).TestID == 38 && 
      ($joinedTable.ResultDataTable).IntValue >= (System.Nullable`1[System.Int32])90 
}

动态与最后的λ

.Call System.Linq.Queryable.Select(
   .Call System.Linq.Queryable.Where(
      .Call System.Linq.Queryable.Select(
         .Call System.Linq.Queryable.Select(
            .Call System.Linq.Queryable.Where(
               .Call System.Linq.Queryable.Select(
                  .Call System.Linq.Queryable.SelectMany(
                     .Call System.Linq.Queryable.Where(
                        .Constant(Table(Samples)),
                        '(.Lambda #Lambda1)
                     ), 
                     '(.Lambda #Lambda2), 
                     '(.Lambda #Lambda3)
                  ),
                  '(.Lambda #Lambda4f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>)
               ),
               '(.Lambda #Lambda5f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>)
            ),
            '(.Lambda #Lambda6f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>)
         ),
         '(.Lambda #Lambda7f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>)
      ),
      '(.Lambda #Lambda8f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>)
   ),
   '(.Lambda #Lambda9f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>)
)
.Lambda #Lambda1(SoDak.Domain.Samples $a) {
   ($a.StatusID == (System.Nullable`1[System.Int32])3 || $a.StatusID == (System.Nullable`1[System.Int32])4) &&
      $a.Lab == .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).labID 
}
.Lambda #Lambda2(SoDak.Domain.Samples $a) {
   .Call System.Linq.Queryable.DefaultIfEmpty(
      .Call System.Linq.Queryable.Where( 
         (.Constant(SoDak.Domain.Services.SampleService)._dc).CustomFields, 
         '(.Lambda #Lambda10)
      )
   ) 
}
.Lambda #Lambda3( SoDak.Domain.Samples $a, SoDak.Domain.CustomFields $b) {
   .New SoDak.Domain.Models.SampleResult(){
      LabCount = (System.Int32)$a.LabCount,
      SampleID = $a.SampleID,
      AccountID = (System.Int32)$a.AccountID,
      Received = (System.Nullable`1[System.DateTime])(($a.Received).Value).Date,
      Completed = (System.Nullable`1[System.DateTime])(($a.Completed).Value).Date,
      SpeciesID = (System.Nullable`1[System.Int32])$a.SpeciesID,
      SpeciesCommonName = ($a.SeedNames).CommonName,
      StatusID = (System.Int32)$a.StatusID,
      StatusName = ($a.SampleStatus).StatusName,
      Variety = $a.Variety,
      Lot = $a.Lot,
      CarryOver = $a.CarryOver,
      Lab = (System.Int32)$a.Lab,
      LabID = (System.Int32)$a.Lab,
      TestList = $a.TestList,
      CustomFieldName = $b.CustomFieldName,
      CustomFieldNameValue = $b.CustomFieldNameValue
   }
}
.Lambda #Lambda4f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>(SoDak.Domain.Models.SampleResult $a) {
   .New <>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]](
      $a,
      .Call System.Linq.Queryable.Select(
         .Call System.Linq.Queryable.Where( 
            (.Constant(SoDak.Domain.Services.SampleService)._dc).SubAccounts,
            '(.Lambda #Lambda11)
         ), 
         '(.Lambda #Lambda12)
      )
   ) 
}
.Lambda #Lambda5f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $<>h__TransparentIdentifier0) {
   ($<>h__TransparentIdentifier0.a).AccountID == .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).accountIDLoggedIn ||
   .Call System.Linq.Queryable.Contains( 
      $<>h__TransparentIdentifier0.innerQuery,
      .Constantc__DisplayClass24_0>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_0).accountIDLoggedIn
   ) 
}
.Lambda #Lambda6f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $<>h__TransparentIdentifier0) {
   $<>h__TransparentIdentifier0.a 
}
.Lambda #Lambda7f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]]]>(SoDak.Domain.Models.SampleResult $a) { 
   .New <>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]](
      $a, 
      .Call System.Linq.Queryable.Select(
         .Call System.Linq.Queryable.Where(
            .Call System.Linq.Queryable.SelectMany(
               (.Constant(SoDak.Domain.Services.SampleService)._dc).RequestedTests,
               '(.Lambda #Lambda13), 
               '(.Lambda #Lambda14)
            ),
            .Constantc__DisplayClass24_1>(SoDak.Domain.Services.SampleService+<>c__DisplayClass24_1).lambda
         ),
         '(.Lambda #Lambda15)
      )
   ) 
}
.Lambda #Lambda8f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],System.Boolean]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $temp0) {
   .Call System.Linq.Queryable.Contains( 
      $temp0.innerQuery, 
      ($temp0.a).SampleID
   )
}
.Lambda #Lambda9f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]],SoDak.Domain.Models.SampleResult]>(<>f__AnonymousType1`2[SoDak.Domain.Models.SampleResult,System.Linq.IQueryable`1[System.Int32]] $temp0) {
   $temp0.a 
}
.Lambda #Lambda10(SoDak.Domain.CustomFields $x) {
   $x.SampleID == $a.SampleID && 
      $x.OnReport == (System.Nullable`1[System.Boolean])True 
}
.Lambda #Lambda11(SoDak.Domain.SubAccounts $b) {
   $b.SampleID == $a.SampleID 
}
.Lambda #Lambda12(SoDak.Domain.SubAccounts $b) {
   $b.AccountID
}
.Lambda #Lambda13(SoDak.Domain.RequestedTests $b) {
   .Call System.Linq.Queryable.DefaultIfEmpty(
      .Call System.Linq.Queryable.Where( 
         (.Constant(SoDak.Domain.Services.SampleService)._dc).ResultData, 
         '(.Lambda #Lambda16)
      )
   )
}
.Lambda #Lambda14( SoDak.Domain.RequestedTests $b, SoDak.Domain.ResultData $c) {
   .New SoDak.Domain.Models.RequestedTestsJoinedResultData(){
      RequestedTestsTable = $b,
      ResultDataTable = $c
   }
}
.Lambda #Lambda15(SoDak.Domain.Models.RequestedTestsJoinedResultData $joinedTable) {
   ($joinedTable.RequestedTestsTable).SampleID
}
.Lambda #Lambda16(SoDak.Domain.ResultData $x) {
   $x.TestNum == $b.TestNum &&
      $b.SampleID == $a.SampleID
}
.Lambda #Lambda1<System.Func`2[SoDak.Domain.Models.RequestedTestsJoinedResultData,System.Boolean]>(SoDak.Domain.Models.RequestedTestsJoinedResultData $joinedTable) 
   ($joinedTable.ResultDataTable).ResultID == 1 &&  
      ($joinedTable.RequestedTestsTable).TestID == 38 &&  
      ($joinedTable.ResultDataTable).IntValue >=  .Constant<System.Nullable`1[System.Int32]>(90)

如果你比较这两个文本墙，你会发现它们几乎相同。lambda 的编号会无足轻重地更改。有几件事突然出现在我面前：

  1. 在动态方面，lambda 包含在闭包中。最好确保变量lambda不会被重复使用。如果是，那将影响您的表达。
  2. 硬编码端具有空检查，动态端没有。
    3. 看起来在最后一次比较中
  3. ，硬编码端将比较.IntValue转换为int?，动态端传递常量int?。我认为这是你问题的根源。

我最终分解了复杂的内部查询，解决了我的问题。 使用IEnumerable并对整个ResultData表进行筛选需要 90 多秒。 让IQueryable工作的时间缩短到不到 0.5 秒。

您需要破译我上面原始帖子的顶部，以查看我从那里到这里更改了什么。 

var innerQuery = (from a in _dc.RequestedTests
    from b in _dc.ResultData
        .Where(c => a.TestNum == c.TestNum)
        .DefaultIfEmpty()
    select new RequestedTestsJoinedResultData
    {
        RequestedTestsTable = a,
        ResultDataTable = b
    }).Where(lambda).Select(a => a.RequestedTestsTable.SampleID);
samples = samples.Where(a => innerQuery.Contains(a.SampleID));
相关文章：