Using max and join together in linq using c#

Question

I have a master-detail tables and I want to get list of master join with detial where detail is max in some filed. for example I have a table named Document and also a child table named Revision .I want to get list of document join Revision where Revision filed is max ? One solution is:

using ( ProcurementDataContext dc = new ProcurementDataContext() )
{

    var temp = from ddr in dc.E_DesignDocumentRevisions
               group ddr by ddr.DesignDocumentID into g
               select new { MaxRevision = g.Max(x => x.Revision), g.Key };

    var result = from t in temp
            join ddr in dc.E_DesignDocumentRevisions
            on new { DesignDocumentID = t.Key, Revision = t.MaxRevision } equals new { ddr.DesignDocumentID, ddr.Revision }
            join dd in dc.E_DesignDocuments
            on ddr.DesignDocumentID equals dd.ID
            where dd.DesignDocumentTypeID == DesignDocumentTypes.MR
            select new { ddr.ID, dd.DocumentNumber };

    foreach (var item in result )
    {
        dic.Add(item.ID, item.DocumentNumber.ToString());
    }

}

How can I do that in just one query?

Answer 1

Well in theory until you demand the result of the enumeration it is still one query due to deferred execution. The query will be executed when you iterate over the result, so theres very few impact performance-wise compared to separating queries into smaller blocks.

However, if youre looking to just merge both blocks, you can just imbricate the first one into the second:

var result = from t in from 
                    ddr in dc.E_DesignDocumentRevisions
                    group ddr by ddr.DesignDocumentID into g
                    select new { MaxRevision = g.Max(x => x.Revision), g.Key }
             join ddr in dc.E_DesignDocumentRevisions
             on new { DesignDocumentID = t.Key, Revision = t.MaxRevision } equals new { ddr.DesignDocumentID, ddr.Revision }
             join dd in dc.E_DesignDocuments
             on ddr.DesignDocumentID equals dd.ID
             where dd.DesignDocumentTypeID == DesignDocumentTypes.MR
             select new { ddr.ID, dd.DocumentNumber };

Answer 2

Tanks for your help .You are right ,but I found a great way with very smaller query:

var result = from ddr in db.E_DesignDocumentRevisions group ddr by new {       ddr.DesignDocumentID, ddr.E_DesignDocument.DocumentNumber } into g select new { MaxRevision = g.Max(x => x.Revision), g.Key.DesignDocumentID, g.Key.DocumentNumber }; 

When we use fileds from some tables(more than one) in Group by clause linq will join them in generated query . Tanks again