LINQ to SQL - Exception when giving sum() in a query that contains another subquery using LINQ

Question

    private decimal GetBankAccountCashierTotal()
    {
        var company = _context.Company.FirstOrDefault();

        return _context.PersonBankAgencyAccount
               .Where(p => p.PersonID.Equals(company.PersonID))
               .Where(c => c.BankAgencyAccountBalance
                    .Any(b => b.Reference <= DateTime.Now))
               .Select(x => x.BankAgencyAccountBalance
                    .Where(d => d.Reference.Date <= DateTime.Now)
                    .OrderByDescending(d => d.Reference)
                    .FirstOrDefault()
                    .CurrentBalance)
                    .sum();

    }

this is my complete method, in the call of this method I have an Exception

"An Exception of type 'System.Data.Sqlclient.Sqlexception' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code"

and in the output

"Microsoft.EntityFrameworkCore.Query.Internal.Querycompiler:Error: An Exception occurred in the database while iterating the Results of a query. System.Data.Sqlclient.Sqlexception: Cannot perform an Aggregate Function on an Expression containing an Aggregate or a subquery."

Answer 1

It is the database that does not accept this type of command even, if you write a select giving sum in a subquery, will give this message.

You have to write the different query, I would do a group by query of the subquery information, and then I would do Join with the main query.

can make a query like this to pick up the record by the longest date

var consultaPorData = from a in precos
                    where a.IdFilial == 53
                    group a by new { a.IdProduto, a.PrecoVendaProduto } into g                                      
                    select new
                    {
                        Produto = g.Key.IdProduto,
                        valor = g.Key.PrecoVendaProduto,
                        data = g.Max(e=>e.DataPreco)
                    };