Join with order by in Entity - but need only last date for the Closed Balance-CodePudding

i am looking for help to join 3 tables but from the last table I only need the last entry by date for the balance. I tried OrderByDescending for the GLBalances table .

var list = await (from ba in _context.BankAccounts
            join bnk in _context.Banks on ba.BankId equals bnk.ID
            join glB in _context.GLBalances on ba.BankGL equals glB.GLAccountGuid
            select new BankAccountDto()
            {
                BankId = ba.BankId,
                AccountNumber = ba.AccountNumber,
                BankName = bnk.BankName,
                Notes = ba.Notes,
                Description = ba.Description,
                Balance = (decimal)glB.ClosingBalance // ***need the last entry by date

            }).ToListAsync();

CodePudding user response：

To get the last entry by date for the GLBalances table, you can use the OrderByDescending method to sort the GLBalances records by date in descending order. Am assuming here column name is Date

You can modify your query to achieve this:

var list = await (from ba in _context.BankAccounts
            join bnk in _context.Banks on ba.BankId equals bnk.ID
            join glB in _context.GLBalances
                .OrderByDescending(glb => glb.Date)
                .Where(glb => glb.GLAccountGuid == ba.BankGL)
                .Take(1) on ba.BankGL equals glB.GLAccountGuid
            select new BankAccountDto()
            {
                BankId = ba.BankId,
                AccountNumber = ba.AccountNumber,
                BankName = bnk.BankName,
                Notes = ba.Notes,
                Description = ba.Description,
                Balance = (decimal)glB.ClosingBalance
            }).ToListAsync();

Updated. try this,

            var list = await (from ba in _context.BankAccounts
            join bnk in _context.Banks on ba.BankId equals bnk.ID
            join glB in _context.GLBalances.OrderByDescending(glb => glb.EndDate).Take(1)
              on ba.BankGL equals glB.GLAccountGuid 
            where glB.GLAccountGuid == ba.BankGL
            select new BankAccountDto()
            {
                BankId = ba.BankId,
                AccountNumber = ba.AccountNumber,
                BankName = bnk.BankName,
                Notes = ba.Notes,
                Description = ba.Description,
                Balance = (decimal)glB.ClosingBalance
            }).ToListAsync();