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

Question

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

Navigation

#1 by (1 votes)

1

    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 calling 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: Can not perform an aggregate function on an expression containing an aggregate or a subquery. "

c# linq asp.net-core entity-framework-core

asked by anonymous 01.12.2016 / 16:30

1 answer

Nested for thread conversion calling a function inside another function in c

score 1 · Answer 1

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

You have to write the query differently, I would make a query with group by of the subquery information, and then would join with the main query.

You can make a query like this to get the record for the longest time

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