How to use Exist sql function in Linq-CodePudding

var _query = _dbContext.CashVoucherDetails
             .Where(x => x.CreationDate.Date >= From.Date && x.CreationDate.Date <= To.Date && (x.Type == Common.TransactionType.CPV || x.Type == Common.TransactionType.BPV) && x.CompanyCode == BranchCode && x.DebitAmount > 0)
             .GroupBy(v => new { v.AccountCode})
             .Select(g => new
              {
                 AccountCode = g.Key.AccountCode,
              });

var balances = _dbContext.CashVoucherDetails
               .Where(x => x.CreationDate.Date <= To.Date && x.CompanyCode == BranchCode)
             //.Exist(_query.Account) (I want only account which exist in upper query)
               .GroupBy(v => new { v.AccountCode})
               .Select(g => new
               {
                   AccountCode = g.Key.AccountCode,
                   Balance = g.Sum(x => x.DebitAmount - x.CreditAmount),
                });

I want to use something like EXIST here in first query I have some specific accounts and in 2nd query I want to calculate balances of those accounts. Can you please tell how I can use Exist function in LINQ. Thank you.

I want this query to be implemented in LINQ:

SELECT `c`.`AccountCode`, `c`.`CompanyCode` AS `LocationCode`, COALESCE(SUM(`c`.`DebitAmount` - `c`.`CreditAmount`), 0) AS `Balance`
    FROM `CashVoucherDetails` AS `c`
    WHERE (CONVERT(`c`.`CreationDate`, date) <= '2022-12-20') AND (`c`.`CompanyCode` = '002') and `c`.`AccountCode` IN 
    (
        SELECT `c`.`AccountCode`
        FROM `CashVoucherDetails` AS `c`
        WHERE ((((CONVERT(`c`.`CreationDate`, date) >= '2022-12-20') AND (CONVERT(`c`.`CreationDate`, date) <= '2022-12-20')) AND `c`.`Type`     IN ('CPV', 'BPV')) AND (`c`.`CompanyCode` = '002')) AND (`c`.`DebitAmount` > 0)
        GROUP BY `c`.`AccountCode` 
    )
    GROUP BY `c`.`AccountCode`, `c`.`CompanyCode`;

CodePudding user response：

you can use Contains or Any.

_dbContext.CashVoucherDetails
    .Where(x => x.CreationDate.Date <= To.Date && x.CompanyCode == BranchCode)
    .Where(x => _query.Select(q => q.AccountCode).Contains(x.AccountCode))

.Where(x => _query.Any(q => q.AccountCode == x.AccountCode))

CodePudding user response：

Maybe something like that

var balances = _dbContext.CashVoucherDetails
           .Where(x => x.CreationDate.Date <= To.Date && x.CompanyCode == BranchCode && _query.Any(q => q.AccountCode == x.AccountCode))
           .GroupBy(v => new { v.AccountCode})
           .Select(g => new
           {
               AccountCode = g.Key.AccountCode,
               Balance = g.Sum(x => x.DebitAmount - x.CreditAmount),
            });