How to convert given lambda query to IQueryable

Question

I prepared this Linq-to-SQL query and it needs to be executed as IQueryable but it's failing.

When I convert filteredResult by calling ToList, then it works well but I need to use filteredResult as IQueryable and get select result as below. But I get an error

A lambda expression with a statement body cannot be converted to an expression tree

Went through link below, but, how the below query can be converted to IQueryable. As per the link, do I need to write up Func<object,object> or any example to convert query would really helpful.

"A lambda expression with a statement body cannot be converted to an expression tree"

var result = filteredResult.Select(g => {
    var type1 = g.FirstOrDefault(x => x.CategoryId == (int)CategoryEnum.Typ1);
    var type2 = g.FirstOrDefault(x => x.CategoryId == (int)CategoryEnum.Typ2);

return new AutoDetailDto
    {
        MilestoneId = g.Key.MilestoneId,
        MilestoneName = g.Key.MilestoneName,
        PGrade = type1?.GDR,
        PGradeChange = type1?.HighestGDR
        QGrade = type2.GDR,
        QGradeChange = type2?.HighestGDR    
    };
});

Jasper Kent · Accepted Answer · 2020-03-22T18:10:12.333

A separate function won't help. In either case, the system cannot work out how to generate SQL from the complex C# code.

Try this:

// This is converted to SQl because the lambda, though compe, is a single statement
var dataResult = filteredResult.Select(g => new {
    type1 = g.FirstOrDefault(x => x.CategoryId == (int)CategoryEnum.Typ1),
    type2 = g.FirstOrDefault(x => x.CategoryId == (int)CategoryEnum.Typ2),
    MilestoneId  = g.Key.MilestoneId,
    MilestoneName = g.Key.MilestoneName
}).ToArray(); // ToArray() forces execution of the query  

// This select is done entirely in memory
var result = dataResult.Select (e =>
       new AutoDetailDto
       {
           MilestoneId = e.MilestoneId,
           MilestoneName = e.MilestoneName,
           PGrade = e.type1?.GDR,
           PGradeChange = e.type1?.HighestGDR
           QGrade = e.type2.GDR,
           QGradeChange = e.type2?.HighestGDR    
       });

I think that should work. Let me know.

EDIT:

The odd thing is, although the null propagating operator is not allowed, the old conditional-if is, so try:

var dataResult = filteredResult.Select(g => new {
        type1 = g.FirstOrDefault(x => x.CategoryId == (int)CategoryEnum.Typ1),
        type2 = g.FirstOrDefault(x => x.CategoryId == (int)CategoryEnum.Typ2),
        MilestoneId  = g.Key.MilestoneId,
        MilestoneName = g.Key.MilestoneName
    }); // No forcing of execution here

var result = dataResult.Select (e =>
       new AutoDetailDto
       {
           MilestoneId = e.MilestoneId,
           MilestoneName = e.MilestoneName,
           PGrade = e.type1 == null ? null : e.type1.GDR,
           PGradeChange = e.type1 == null ? null :e.type1.HighestGDR
           QGrade = e.type2 == null ? null : e.type2.GDR,
           QGradeChange = e.type2 == null ? null : e.type2.HighestGDR    
       });

It works but, I need to get executed as IQueryable and not to use force execution. — TechS, Mar 22 '20 at 15:06
Its large result set and get executed as OData endpoint that response as IQuerable. — TechS, Mar 22 '20 at 15:55
The main reason I split it was to allow the intermediate variable type1 and type2, which save code duplication. However, even if you're not worried about that, I don't think your '?.' operators can be converted to an expression tree either. — Jasper Kent, Mar 22 '20 at 16:01
this will not work without `ToArray` and then follow by second query. I mean, it gives same message for conversion failed on expression tree. — TechS, Mar 22 '20 at 16:05
If it works without ToArray() then fine - I'm mistaken about the '?.' not being allowed. — Jasper Kent, Mar 22 '20 at 16:07

How to convert given lambda query to IQueryable

1 Answers1