CODEWITHSUNDEEP
c#linq
James
James

Reputation: 1945

convert group by query to linq

I am trying to convert below query in linq

 select ta.id, ta.name, min(tb.id) from tableA ta
 left join tableB tb on tb.fkid=ta.id
 group by ta.id, ta.name

I tried below

 var query = (from tableA in tableARepository.AsQueryable()
            join tableB in tableBRepository.AsQueryable() on
                tableA.Id equals tableB.fkid

                     group grp by new {tableA.Id, tableA.Name } into groupedCol
                     select new
                     {
                         Id = groupedCol.Key.Id,
                         Name = groupedCol.Key.Name,
                         fkId = grouppedCol.Min // cant get column fkid from tableB

                     }

            );

I am not able to get Min of column fkid of tableB

What is missing here or how can i fix this?

Upvotes: 2

Views: 141

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727047

You should be able to get the results that you are looking for without using Join or GroupBy:

var restrictTo = new[] {1, 2};
var query = tableARepository.AsQueryable()
    // I want to add where clause like tableA.Id in (1,2)
    .Where(a => restrictTo.Contains(a.Id))
    .Select(a => new {
        Id = a.Id
    ,   Name = a.Name
    ,   FkId = tableBRepository.AsQueryable().Min(b => b.fkid=a.id)
    });

Upvotes: 1

Selman Genç
Selman Genç

Reputation: 101731

I think you are looking for GroupJoin

from tableA in tableARepository.AsQueryable()
join tableB in tableBRepository.AsQueryable() on
tableA.Id equals tableB.fkid into tb
select new 
       {
           Id = tableA.Id,
           Name = tableA.Name,
           fkId = tb.Min(x => x.id)
       }

Upvotes: 1

Related Questions