Question 86572 in ASP.NET Question posted in
The official documentation can be found here.

EF cannot translate query – Asp.net

I have query for getting data

await _dbContext.VwJobSupplierWithScores
                .Where(x => x.JobId == jobId && x.SupplierKey == supplierKey)
                .OrderBy(x => x.SpendCurrencyJob)
                .Select((x, i) => new {item = x, index = i})
                .FirstOrDefaultAsync())!,

But for some reasons EF cannot translate it and I get this error

The LINQ expression ‘DbSet()
.Where(x => (Guid?)x.JobId == __jobId_0 && x.SupplierKey == __supplierKey_1)
.OrderBy(x => x.PurchaseOrderValueCurrencyJob)
.Select((x, i) => new {
item = x,
index = i
})’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to ‘AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or ‘ToListAsync’.

How I can write it correctly, that LINQ can translate it?

Tags:

3

Answers


  1. 0

    Your Problem is, the select not matching the objects in VmJobSupplierQithScores. EF tries to map your Query to that type. Because you do a select with a different type, it can not translate that query.
    You can split your query into something like this.

    var item = _dbContext.VwJobSupplierWithScores
                .Where(x => x.JobId == jobId && x.SupplierKey == supplierKey)
                .OrderBy(x => x.SpendCurrencyJob).First();

var result = new {item = x, index = 0};
    Login or Signup to reply.
  2. 0

    One option, do as the error told you:

    await _dbContext.VwJobSupplierWithScores
                .Where(x => x.JobId == jobId && x.SupplierKey == supplierKey)
                .OrderBy(x => x.SpendCurrencyJob)
                .AsEnumerable()   
                .Select((x, i) => new {item = x, index = i})
                .FirstOrDefaultAsync())!

    Another option:

    var dbObject = await _dbContext.VwJobSupplierWithScores
                .Where(x => x.JobId == jobId && x.SupplierKey == supplierKey)
                .OrderBy(x => x.SpendCurrencyJob)
                .FirstOrDefaultAsync();

var yourmodel = new { item = dbObject, index = 0 };

    Please note that some parts of your original code are completely pointless. Using the "!" after calling FirstOrDefault yourself? If you know there will always be one, just call First. The saving of the index? What do you think the index of the first element will be? So… there is actually no point in having your anonymous object, because the data it holds is redundant. I have shown two ways to make your compiler happy, but you should think about why the code does these weird things in the first place.

    Login or Signup to reply.
  3. 0

    Such Select overload is not supported by EF and I actually do not see reason to use that. FirstOrDefaultAsync will return one entity and Index will be always ZERO:

    await _dbContext.VwJobSupplierWithScores
    .Where(x => x.JobId == jobId && x.SupplierKey == supplierKey)
    .OrderBy(x => x.SpendCurrencyJob)
    .Select(x => new {item = x, index = 0})
    .FirstOrDefaultAync();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search