Subquery with linq

Question

I want to do a subquery with linq but it doesn't work. :-( I've searched google for an answer, but I don't know how to solve the problem.

This is my sql

string strSQL = @"SELECT a.ident, a.ben1 
                FROM pwdata a 
                WHERE a.iid = (SELECT max(b.iid) FROM pwdata b WHERE b.ident = a.ident)";

That's how I tried to do with Linq, but this is not the right way

var query = from i in maxxContext.pwdata
            where i.IID = (SELECT max(b.iid) FROM pwdata b WHERE b.ident = a.ident) 
            orderby i.ident

            select new CompareParts
            {
                PartNumber = i.ident,
                PartName = i.ben1
            };
            return query.ToList().Distinct();

Can anyone of you help me?

Answer 1

It's not clear why you've started intermingling LINQ with regular SQL. It doesn't work like that.

This should work though:

var query = from i in maxxContext.pwdata
            where i.IID == (pwdata.Where(b => b.ident == i.ident)
                                  .Max(b => b.iid))
            orderby i.ident
            select new CompareParts
            {
                PartNumber = i.ident,
                PartName = i.ben1
            };

Alternatively, you could do a join:

var query = from i in maxxContext.pwdata
            join b in pwdata on i.ident equals b.ident into bs
            where i.IID == bs.Max(b => b.iid)
            orderby i.ident
            select new CompareParts
            {
                PartNumber = i.ident,
                PartName = i.ben1
            };