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));
        }

score 1 · Answer 1 · answered Oct 27 '12 at 01:12

1

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.

answered Oct 27 '12 at 01:12

Steve Konves

2,648
3
25
44

Thanks for the tip. :) That works, but it doesn't take into account the Max aggregation (found in the second line of the SQL query). It returns a list of all client ID's and no groups. I eventually stumbled on the answer through this post: http://stackoverflow.com/a/157919/1689144 – Kyle Oct 27 '12 at 01:57

score 0 · Accepted Answer · edited May 23 '17 at 12:03

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();

Joining two tables in linq method syntax, MVC EntityFramework

2 Answers2