CODEWITHSUNDEEP
c#linqlinq-to-sql
TechS
TechS

Reputation: 177

How to convert given lambda query to IQueryable

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

Upvotes: 0

Views: 431

Answers (1)

Jasper Kent
Jasper Kent

Reputation: 3676

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

Upvotes: 1

Related Questions