Joining two tables in linq method syntax, MVC EntityFramework

Question

I'm working with two tables: CI_CLIENTRISK (SCD type 2)... and QB_INVOICES_HEADER (edmx screenshot).

They can be joined via ClientID. I want to essentially replicate this query:

SELECT a.ClientID,
    MAX(b.InvoiceDt) AS MaxInvoiceDt
    (omitted for brevity)
FROM CI_CLIENTRISKADJS a
INNER JOIN QB_INVOICES_HEADER b
ON a.ClientID = b.ClientID
WHERE a.IsActive = 1
GROUP BY a.ClientID
ORDER BY MaxInvoiceDt DESC

Here's what I have so far. It's not returning any records.

using (var db = new PLOGITENS01Entities())
        {
        var rvClientRiskAdjs = db.CI_CLIENTRISKADJS
            .Take(50)
            .Join(db.QB_INVOICES_HEADER,
                a => a.ClientID,
                b => b.ClientID,
                (a, b) => new { Risk = a, Invoices = b })
            .Where(a => a.Risk.IsActive == 1)
            .OrderByDescending(o => o.Invoices.InvoiceDt)
            .Select(c => new ClientRiskModel()
            {
                ClientRiskId = c.Risk.ClientRiskID,
                ClientName = c.Risk.CI_CLIENTLIST.ClientName,
                ClientId = c.Risk.ClientID,
                ClientRiskAdjs = c.Risk.ClientRiskAdjs,
                RecordValidStartDt = c.Risk.RecordValidStartDt,
                RecordValidEnddt = c.Risk.RecordValidEnddt,
                IsActive = c.Risk.IsActive
            })
            .ToList();

        return View(new GridModel(rvClientRiskAdjs));
        }

Answer 1

I stumbled across this solution from reading this post: https://stackoverflow.com/a/157919/1689144

var rvClientRiskAdjs = (from ri in db.CI_CLIENTRISKADJS
                        join qb in
                            (from qb in db.QB_INVOICES_HEADER
                             orderby qb.InvoiceDt ascending
                             group qb by qb.ClientID into grp
                             select new
                             {
                                 InvoiceDt = grp.Max(s => s.InvoiceDt),
                                 ClientID = grp.Key
                             })
                        on ri.ClientID equals qb.ClientID
                        orderby qb.InvoiceDt descending
                        where ri.IsActive == 1
                        select new ClientRiskModel()
                        {
                            ClientRiskId = ri.ClientRiskID,
                            ClientName = ri.CI_CLIENTLIST.ClientName,
                            ClientId = ri.ClientID,
                            ClientRiskAdjs = ri.ClientRiskAdjs,
                            RecordValidEnddt = ri.RecordValidEnddt,
                            RecordValidStartDt = ri.RecordValidStartDt
                        })
                        .ToList();

Answer 2

Try putting your .Take(50) method after your final .Select and before .ToList(). As it is, you are only taking the first 50 records of the first table and then joining from there. I'm assuming that there are no joins to the second table in the first 50 records of the first table; therefore, your result will have 0 records.