Need Linq translation for the following SQL Query

Question

select  colId,
        colTaskType,
        MaxID
from    tblTaskType
join    (
         select tblCheckList.colTaskTypeID,
                max(colItemNumber) MaxID
         from   tblCheckList
         group by colTaskTypeID
        ) x on coltaskTypeID = tblTaskType.colID

Answer 1

Assuming you are using linq-to-sql and have the two tables in a datacontext.

The more or less exact translation would be:

var maxChecks = from checks in DataContext.tblChecklist
                group checks by checks.colTaskTypeID into g
                select new { colTaskTypeID, max = g.Group.Max(x => x.colItemNumber) };

var result = from t in DataContext.tblTaskType
             join c in maxChecks on t.colTaskTypeID equals c.colTaskTypeID
             select new { t.colId, t.colTaskTypeID, c.max };

But you could try:

var result = from t in DataContext.tblTaskType
             select new {
                     t.colId,
                     t.colTaskTypeID,
                     Max = (from c in DataContext.tblChecklist
                            where c.colTaskTypeID == t.colTaskTypeID
                            select c.colItemNumber).Max() };