Linq Lambda expression for below sql in vb.net

Question

I have this existing Sql statement:

Select Count(ordid),isnull(prcsts,'NOT STARTED')
from lwp
where lwp in( Select max(Id) from lwp group by ordid)
group by prcsts

I want to convert to use linq-to-sql, but I'm can't figure out how to handle the group by expression in the sub query. How can I do this?

I am using Entity Framework where I have a method to get the list of lwp. I did only part of it.

Entitydb.lwpmethod
        .GetList
        .Where(Function(F) F.ID = **Max(Function(O)  O.ordid**)
        .GroupBy(Function(F) F.prcsts)
        .Select(Function(F) New With {.A = F.Count, .B = F.Key})
        .ToList

I am unable to write the group by subquery in the max function.

`lwp in (max(id))`? Are you sure it's gonna work? – MarcinJuraszek Nov 22 '13 at 18:28 — MarcinJuraszek, Nov 22 '13 at 18:28

score 0 · Accepted Answer · answered Nov 22 '13 at 18:27

First off, that's not an in, that's an = since max() returns a single element. Also your sql query has lwp in the where clause, you probably typo'd id. With that in mind, what you want is something like:

.Where(row=>row.ID=Entitydb.lwpmethod.GetList()
                                     .Where(r=>r.ordid=row.ordid)
                                     .Max(r=>r.ID))

C# code, but you get the idea.

By the way this looks like it's selecting the last row. Why not just sort by id descendently and take the first element?

Thank You it worked, I was confused regarding in and "=". I think the best approach is to use the first ID. I will try that. Thanks for the advise. — tinku99, Nov 22 '13 at 20:00

Linq Lambda expression for below sql in vb.net

1 Answers1