"the linq expression [...] could not be translated"-CodePudding

I am trying to use LINQ methods to filte a list of client by cheking whether or not the client is in a table of my BDD

using(var db = new CrmXefi())
            {
                var activeAccount = db.AccountBase.Where(account => account.StateCode == 0);
                
                List<Client> toImport = clients.Where(client => activeAccount
                .Where(account => account.IsAccountSageNumberForAgency(client.CODECLIENT, new Guid("2CA81DBC-8261-EB11-B061-00155D53D276")))
                .Any()).ToList();
            }

LINQ cannot translate that expression. I've checked the type of my elements to make sure that none of the ones inside the queries are IEnumerable. Can't find the origin of the problem.

This expression works : var test = activeAccount.Where(account => account.IsAccountSageNumberForAgency("", new Guid("2CA81DBC-8261-EB11-B061-00155D53D276")));

is it because I get client.CODECLIENT in a Where() that is inside an other Where() and client is the in the first predicate?

CodePudding user response：

Your query cannot be executed on the database side, so you will have to do it client side, by loading all the objects in memory. You can use ToList() to achieve this. By example :

using(var db = new CrmXefi())
{
    var activeAccount = db.AccountBase.Where(account => account.StateCode == 0);
    List<Client> toImport = clients.ToList().Where(client => activeAccount
    .Where(account => account.IsAccountSageNumberForAgency(client.CODECLIENT, new Guid("2CA81DBC-8261-EB11-B061-00155D53D276")))
    .Any()).ToList();
}

CodePudding user response：

"Linq" is confusing here. On one side you have the "traditional" Linq, that works on IEnumerable and does all the processing in memory. On the other side you have Frameworks like Entity Framework that uses a Linq-like interface to compose queries. Ef Core is actually based on "Linq2Sql". EF converts your Linq to SQL and sends that to the server (it works on the IQueryable type. But not everything can be converted to SQL. In your case you're calling a specific method of a class, which is specific to your C# code. The SQL server doesn't know anything about it. And EF cannot convert it an expression.

Unless you can make something that can run on the server, you will need to pull the data to the local memory. You can do that with AsEnumerable.

E.g.

List<Client> toImport = clients
    .AsEnumerable()
    .Where(client => activeAccount
        .Where(account => account
            .IsAccountSageNumberForAgency(
                client.CODECLIENT,
                new Guid("2CA81DBC-8261-EB11-B061-00155D53D276")))
        .Any())
    .ToList();

Note, this will pull all data from the server to the local memory, thus costs memory and performance