Base solution for your next web application
Open Closed

Why a simplest ef core query throws 'cannot be translated' exception? #10921

User avatar
0
adamphones created

.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?

Markdown is supported
Copy & paste or drag & drop images (max 30 MB per image)
Go to accepted answer
7 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @adamphones

    This seems very strange. The expression you are using for Select is placed into Where section of the genrated LINQ expression. Is it possible to share your query, entity and DTO classes ?

    Thanks,

    Markdown is supported
    Copy & paste or drag & drop images (max 30 MB per image)
  • User Avatar
    0
    adamphones created

    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,

    Markdown is supported
    Copy & paste or drag & drop images (max 30 MB per image)
  • User Avatar
    0
    musa.demir created

    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);
    Markdown is supported
    Copy & paste or drag & drop images (max 30 MB per image)
  • User Avatar
    0
    adamphones created

    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.

    Markdown is supported
    Copy & paste or drag & drop images (max 30 MB per image)
  • User Avatar
    0
    musa.demir created

    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

    Markdown is supported
    Copy & paste or drag & drop images (max 30 MB per image)
  • User Avatar
    0
    adamphones created

    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

    Markdown is supported
    Copy & paste or drag & drop images (max 30 MB per image)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Thanks @adamphones

    We will investigate this problem, please follow https://github.com/aspnetzero/aspnet-zero-core/issues/4274

    Markdown is supported
    Copy & paste or drag & drop images (max 30 MB per image)
Assignee
No one
Labels
None yet