Use LINQ Query Operations

Question

Please need assistance to convert this SQl Query to LINQ Query

SELECT EconCode.EconCodeId, 
       EconCode.EconCodeName, 
       EconCode.EconIncomeAmt, 
       EconCode.EconRecExpAmt, 
       EconCode.EconCapExpAmt, 
       FundCode.FundCodeId
FROM EconCode 
LEFT OUTER JOIN FundCode 
   ON EconCode.FundCodeId = FundCode.FundCodeId
GROUP BY EconCode.EconCodeId, 
         EconCode.EconCodeName, 
         EconCode.EconIncomeAmt, 
         EconCode.EconRecExpAmt, 
         EconCode.EconCapExpAmt, 
         FundCode.FundCodeId

I have tried the following codes before but but in my report display nothing.

var query = from econ in _context.EconCode
         join fund in _context.FundCode on econ.FundCodeId equals fund.FundCodeId
         group new { econ, fund.FundCodeId } by new { fund.FundCodeName, econ.FundCodeId, econ.EconCodeId, econ.EconCodeName, econ.EconIncomeAmt, econ.EconRecExpAmt, econ.EconCapExpAmt  } into g
          select new
             {
              fundcodeId = g.Key.FundCodeId,
              fundCodeName = g.Key.FundCodeName,
              econCodeId = g.Key.EconCodeId,
              econCodeName = g.Key.EconCodeName,
              econIncomeAmt = g.Key.EconIncomeAmt,
              econRecExpAmt = g.Key.EconRecExpAmt,
              econCapExpAmt = g.Key.EconCapExpAmt,
          };

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Feb 28 '23 at 00:16
Please show us what you have tried... this is not a free coding service. — Dale K, Feb 28 '23 at 01:14

score 0 · Accepted Answer · answered Feb 28 '23 at 16:56

Suppose that there are two corresponding entity classes named EconCode and FundCode, and the context variable is dbContext, The equivalent LINQ query to the SQL query provided would be:

var query = from econ in dbContext.EconCode
            join fund in dbContext.FundCode on econ.FundCodeId equals fund.FundCodeId into joinedGroup
            from fund in joinedGroup.DefaultIfEmpty()
            group new { econ, fund } by new { econ.EconCodeId, econ.EconCodeName, econ.EconIncomeAmt, econ.EconRecExpAmt, econ.EconCapExpAmt, FundCodeId = (int?)fund.FundCodeId } into g
            select new {
                g.Key.EconCodeId,
                g.Key.EconCodeName,
                g.Key.EconIncomeAmt,
                g.Key.EconRecExpAmt,
                g.Key.EconCapExpAmt,
                FundCodeId = g.Key.FundCodeId ?? 0
            };

Use LINQ Query Operations

1 Answers1