Search code examples
c#linqlinq-to-sqlexpression-trees

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?

Solution

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