Question 21564 in Mysql Question posted in
The official MySQL documentation can be found here.

How to use Exist sql function in Linq – Mysql

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`;
Tags:

2

Answers


  1. 0

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

    Or

    .Where(x => _query.Any(q => q.AccountCode == x.AccountCode))
    Login or Signup to reply.
  2. 0

    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),
            });
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search