EF cannot translate query-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Given you're only ever using the first one, index is always the same here. Try this (assuming JobId, SupplierKey, SpendCurrencyJob are all mapped columns)

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

CodePudding user response：

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