How to use the group join result to join another tables using LINQ?

Question

I am not able to use the result of group join and then further applying the join with other tables.

My SQL query is :

SELECT *
FROM   
    [dummy_database].[dbo].[MA] M
INNER JOIN 
    SN.dbo.A A ON A.ApplicantMAId = M.MAId
INNER JOIN 
    SN.dbo.SP SP ON  SP.PropertyMAId = M.MAId
INNER JOIN 
    SN.dbo.MCC CC ON  CC.MAId = m.MAId
INNER JOIN 
    SN.dbo.MLSTN T ON  T.MAT_ID = M.MAId
INNER JOIN 
    (SELECT 
        MAX(MAT_DATE) AS MaxDate,
        MAT_ID
     FROM   
        [SN].[dbo].[MLSTN]
     GROUP BY
        MAT_ID) Q ON  Q.MAT_ID = M.MAId

and what I have done so far is:

 var q = (from ml in context.MLSTN
                 group ml by ml.MAT_ID into g
                 let maxdate = g.Max(date => date.MAT_DATE)
                 select new
                 {
                     MortgId = g.Key,
                     TrackingDate = g.FirstOrDefault(val => val.MAT_DATE == maxdate).MAT_DATE

                 }
                 );

but now this result is not at all further used by me to create a join with other tables. I want to ask how to join further? Any clue?

Answer 1

You need something like this:

var groups = context.MLSTN
    .GroupBy(x => x.MAT_ID)
    .Select(g => new 
    {
        MAT_ID = g.Key,
        MaxDate = g.Max(date => date.MT_DATE)
    });

var result = context.MA
    .Join(context.A,
        m => m.MA_ID,
        a => a.MA_ID,
        (m, a) => new 
        {
            MA = m,
            A = a
        })
    .Join(context.SP,
        x => x.MA.MAId,
        sp => sp.PropertyMAId,
        (x, sp) => new
        {
            MA = x.MA ,
            A = x.A,
            SP = sp
        })
    .Join(context.MCC,
        x => x.MA.MAId,
        cc => cc.MAId,
        (x, cc) => new
        {
            MA = x.MA ,
            A = x.A,
            SP = x.SP,
            MCC = cc
        })
    .Join(context.MLSTN,
        x => x.MA.MAId,
        t => t.MAT_ID,
        (x, t) => new
        {
            MA = x.MA ,
            A = x.A,
            SP = x.SP,
            MCC = x.MCC,
            MLSTN = t
        })
    .Join(groups,
        x => x.MA.MAId,
        g => g.MAId,
        (x, g) => new
        {
            MA = x.MA ,
            A = x.A,
            SP = x.SP,
            MCC = x.MCC,
            MLSTN = t,
            MLSG = g
        });