.net core version: .net 5 asp zero: 10.20
I have the simplest query with select statement for efficiency so instead of selecting all columns I want to limit the selection:
var productDto = await _productRepository.GetAll().Select(d=>new ProductDto{Name = d.Name}).SingleAsync(x=>x.Id ==input.Id);
This simple query throws
The LINQ expression 'DbSet()
.Where(c => new ProductDo{ Name= d.Name}
.Id == __input_Id_0)' 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'.
I have written a simple ef core application with ef core 5 with simple context and above query perfectly works fine in that.
Any idea why this selection is not allowed?
7 Answer(s)
-
0
Hi @adamphones
This seems very strange. The expression you are using for
Selectis placed intoWheresection of the genrated LINQ expression. Is it possible to share your query, entity and DTO classes ?Thanks,
-
0
Hi Ismail,
I find this weird too. It is just like all other Repository pattern.
private readonly IRepository<Product> _productRepository;DI injection injects the product repository as above.
Then in the method I just call
var product = await _productRepository.GetAll().Select(v=> new ProductDto(){}).SingleAsync(x => x.Id == input.Id);public class ProductDto: EntityDto{ public string Name {get;set;} }`This one fails.
But if I write the query as below it is fine:
var product = await _productRepository.GetAll().SingleAsync(x=>x.Id ==input>id); var productDto = ObjectMapper.Map<ProductDto>(product);`The problem with this is that this returns all columns from database for the product. I am trying to avoid this with select but it fails.
Anything you can think of would cause this problem?
Thanks,
-
0
Hi @adamphones
Can you please try following code:
var productDto = await _productRepository.GetAll() .Select(d => new ProductDto { + Id = d.Id, Name = d.Name }) .SingleAsync(x => x.Id == productId); -
0
Hi @musa.demir,
When Id is used in select the translation error is NOT getting thrown. However, when I checked profiler what query gets sent to database i can see it goes and grabs all columns. So it is not expected behaviour at all. So basically select statment is useless.
-
0
Hi @adamphones
Can you please check another implementations to see which result is better. For example filter with where first:
await _productRepository.GetAll() .Where(x => x.Id == productId) .Select(d => new ProductDto { Name = d.Name }) .FirstOrDefaultAsync();use first or default:
await _productRepository.GetAll() .Select(d => new ProductDto { Name = d.Name }) .FirstOrDefaultAsync(x => x.Id == productId);or
await _productRepository.GetAll() .Where(x => x.Id == productId) .Select(d => new ProductDto { Name = d.Name }) .SingleAsync();etc
-
0
Hi @musa,
I have tried all and none works as expected. All queries retrieve all columns from database. And it would be wrong actually to think that one way of writing the query would change the output where those should be translated into sql with the same script. This is the case in ef core.
Can you please do the test at your end to see what you get? My question still stands. How should I write queries that would only select the columns that I specify in the select dto? In ef core world any of those queries result in the same correct expected query.
I have created a ticket as wellhttps://github.com/aspnetzero/aspnet-zero-core/issues/4274
-
0
Thanks @adamphones
We will investigate this problem, please follow https://github.com/aspnetzero/aspnet-zero-core/issues/4274
