CODEWITHSUNDEEP
c#linqlinq-to-sqlexpression-trees
jaredbaszler
jaredbaszler

Reputation: 4809

Expression Tree Errors as IQueryable but works as IEnumerable

My first foray into expression trees with a LINQ query has got me stuck. Here is the query that works without an expression tree:

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

My next move was to construct an expression tree to send to the the middle Where() call. The expression tree is needed to dynamically create a filter based on user input. 

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

The first code block then changes from this:

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

To This:

.Where(lambda)

The first code block works as IQueryable but when using the expression tree, the 2nd code block, it only works if I change it to IEnumerable. I get this run-time error when using IQueryable:

An exception of type 'System.NotSupportedException' occurred in System.Data.Linq.dll but was not handled in user code

Additional information: Unsupported overload used for query operator 'Where'.

Stacktrace:

at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)\r\n at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)\r\n at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node)\r\n at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)\r\n at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)\r\n at System.Data.Linq.DataQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()\r\n at System.Linq.SystemCore_EnumerableDebugView1.get_Items()"

EDITS:

1: My custom data model class:

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

2: DebugView of the lambdas:

Where clause lambda dynamically created from expression tree:

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

Large lambda with hard coded result filters:

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

Large lambda with dynamically created result filters by expression tree building.

.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: I went ahead and tried the same query using System.Linq.Dynamic and got another error which may or may not be related.

The where clause using dynamic LINQ:

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

Error:

Member access 'SoDak.Domain.RequestedTests RequestedTestsTable' of 'SoDak.Domain.Models.RequestedTestsJoinedResultData' not legal on type 'System.Linq.IQueryable`1[SoDak.Domain.Models.RequestedTestsJoinedResultData].

4: Generated SQL when result filters are hard coded:

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)

Is it truly not supported or am I doing something wrong when I construct my expression tree?

Upvotes: 0

Views: 285

Answers (2)

jaredbaszler
jaredbaszler

Reputation: 4809

I ended up breaking up the complex inner query and that solved my issue. Using IEnumerable and filtering on our entire ResultData table was taking over 90 seconds. Getting IQueryable to work dropped that down to less than .5 seconds.

You will need to decipher the top part of my original post above to see what I changed from there to here. 

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

Upvotes: 1

Shlomo
Shlomo

Reputation: 14350

Short answer:

I think what was tripping you up was your handling of nullable filters. If you wrap an Expression.Constant around a nullable double (or int), I'm not sure it will compile nicely.

Here's the two DebugViews of the lambdas pretty-printed:

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)

If you diff these two text-walls, you'll see they're almost identical. The numbering of the lambdas changes inconsequentially. There are a couple things popping out at me:

  1. On the dynamic side, the lambda is contained in a closure. Best to make sure that the variable lambda isn't being re-used. If it is, that will affect your expression.
  2. The hard-coded side has a null-check, the dynamic side doesn't.
  3. It looks like for the last comparison, the hard-coded side is casting the comparing .IntValue to int? and the dynamic side is passing a constant int?. I think this is the source of your problems.

Upvotes: 2

Related Questions