首页 文章

表达式树错误为IQueryable但可用作IEnumerable

提问于
浏览 次
0

我第一次使用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.NotSupportedException”类型的异常但未在用户代码中处理其他信息：用于查询运算符“Where”的不支持的重载 .

堆栈跟踪：

在System.Data.Linq.SqlClient的System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall（MethodCallExpression mc）\ r \ n，在System.Data.Linq.SqlClient的System.Data.Linq.SqlClient.QueryConverter.VisitInner（Expression node）\ r \ n中 . System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq上的System.Data.Linq.SqlClient.SqlProvider.BuildQuery（表达式查询，SqlNodeAnnotations注释）\ r \ n的QueryConverter.ConvertOuter（表达式节点）\ r \ n System.Linq.SystemCore_EnumerableDebugView1.get_Items上的System.Data.Linq.DataQuery1.System.Collections.Generic.IEnumerable <T> .GetEnumerator（）\ r \ n的.Provider.IProvider.Execute（表达式查询）\ r \ n（ ）”

EDITS:

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

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

2：lambdas的DebugView：

从表达式树创建的where子句lambda dynamically ：

.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)

带有 hard coded 结果过滤器的大型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 }

带有 dynamically created 结果的大型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.Models.RequestedTestsJoinedResultData'的'SoDak.Domain.RequestedTests RequestedTestsTable'在类型'System.Linq.IQueryable`1 [SoDak.Domain.Models.RequestedTestsJoinedResultData]上不合法 .

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)

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

c# linq linq-to-sql expression-trees

2 回答

  • 1

    Short answer:

    我认为绊倒你的是你对可空过滤器的处理 . 如果你围绕一个可以为空的double（或int）包装一个 Expression.Constant ，我不确定它会很好地编译 .

    这是lambdas漂亮打印的两个DebugView：

    Long answer

    Hard coded

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

    Dynamic with the lambda at the end

    .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的编号变化无关紧要 . 有几件事突然冒出来：

    • 在动态方面，lambda包含在一个闭包中 . 最好确保不重复使用变量 lambda . 如果是，那将影响你的表达 .

    • 硬编码侧有空检查，动态侧没有 .

    • 看起来对于最后的比较，硬编码方将比较 .IntValue 转换为 int? ，动态方传递常量 int? . 我认为这是你问题的根源 .

    回复于
  • 2

    我最终打破了复杂的内部查询，这解决了我的问题 . 使用 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));
    回复于

相关问题