C#•2y ago

✅ can't translate linq query

I have following query:

var devices = dbContext.Set<Model.Entities.Device>()
    .Where(device => categories
        .Any(device.Categories.Contains))
    .WhereCanRead(currentOperator.Scope)
    .ToList();

var devices = dbContext.Set<Model.Entities.Device>()
    .Where(device => categories
        .Any(device.Categories.Contains))
    .WhereCanRead(currentOperator.Scope)
    .ToList();

And got error:

System.InvalidOperationException: The LINQ expression 'category => EntityShaperExpression:
    Parsec.Segment.Model.Entities.Device
    ValueBufferExpression:
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.Categories
    .Contains(category)' could not be translated.

System.InvalidOperationException: The LINQ expression 'category => EntityShaperExpression:
    Parsec.Segment.Model.Entities.Device
    ValueBufferExpression:
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.Categories
    .Contains(category)' could not be translated.

I tried to add AsEnumerable after categories and device.Categories but it doesn't work

24 Replies

Keswiik•2y ago

What type is device.Categories?

SWEETPONYOP•2y ago

HashSet<string>

Keswiik•2y ago

I'm not sure if every provider for EF supports collections of primitive types. Without using a value converter that is

Keswiik•2y ago

https://learn.microsoft.com/en-us/ef/core/modeling/relationships/one-to-many#required-one-to-many this is a good example on what your models would look like when implementing a one-to-many relationship

One-to-many relationships - EF Core

How to configure one-to-many relationships between entity types when using Entity Framework Core

Florian Voß•2y ago

I'm not sure but I imagine your own custom method WhereCanRead() can't be translated to sql. I have two ideas in mind what you can try out: 1. Try your custom WhereCanRead method on IEnumerable rather than IQueryable after materialising query with ToList(), so EF won't try to translate your thingy to sql because you do in memory on resulting enumerable. 2. I wonder why this WhereCanRead exists in the first place. Why not use the existing Where() method as you wish before materialising query rather than own custom WhereSomething() method? Then you can do this before materialising without worrying about Translation. Take this with a grain of salt, I'm not sure if I understod the problem correctly can you share the definition of that method pls?

Keswiik•2y ago

Might wanna read the error message a second time. It says that EF is unable to translate his where clause.

Florian Voß•2y ago

yep, a where clause with category => ... which I don't see here I assume this might be the content of WhereCanRead oh no its actualy the Any(device.Categories.Contains), I misunderstood it.

SWEETPONYOP•2y ago

hm, WhereCanRead actually works good the problem is here: let's try not to use single query:

1. var allCategories = dbContext.Set<Model.Entities.Device>().ToList(); 
2. var result = allCategories.Where(device => device.Categories
       .Any(categories.Contains))
       .ToList();

1. var allCategories = dbContext.Set<Model.Entities.Device>().ToList(); 
2. var result = allCategories.Where(device => device.Categories
       .Any(categories.Contains))
       .ToList();

and we will get the result, everything is correct! so let's combine rows:

var devices = dbContext.Set<Model.Entities.Device>()
   .Where(device => device.Categories.Any(categories.Contains))
   .ToList();

var devices = dbContext.Set<Model.Entities.Device>()
   .Where(device => device.Categories.Any(categories.Contains))
   .ToList();

and now we got error:

System.ArgumentException: Expression of type 'System.Func`2[System.String,System.Boolean]' cannot be used for parameter of type 'System.Linq.Expressions.Expression`1[System.Func`2[System.String,System.Boolean]]' of method 'Boolean Any[String](System.Linq.IQueryable`1[System.String], System.Linq.Expressions.Expression`1[System.Func`2[System.String,System.Boolean]])' (Parameter 'arg1')

System.ArgumentException: Expression of type 'System.Func`2[System.String,System.Boolean]' cannot be used for parameter of type 'System.Linq.Expressions.Expression`1[System.Func`2[System.String,System.Boolean]]' of method 'Boolean Any[String](System.Linq.IQueryable`1[System.String], System.Linq.Expressions.Expression`1[System.Func`2[System.String,System.Boolean]])' (Parameter 'arg1')

also we can rewrite this a little bit:

var devices = dbContext.Set<Model.Entities.Device>()
   .Where(device => device.Categories.Any(x => categories.Contains(x)))
   .ToList();

var devices = dbContext.Set<Model.Entities.Device>()
   .Where(device => device.Categories.Any(x => categories.Contains(x)))
   .ToList();

new error:

The LINQ expression 'DbSet<Device>()
    .Where(d => d.Categories
        .Any(cat => __categories_0
            .Contains(cat)))' could not be translated. Additional information: Translation of member 'Categories' on entity type 'Device' failed. This commonly occurs when the specified member is unmapped. 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The LINQ expression 'DbSet<Device>()
    .Where(d => d.Categories
        .Any(cat => __categories_0
            .Contains(cat)))' could not be translated. Additional information: Translation of member 'Categories' on entity type 'Device' failed. This commonly occurs when the specified member is unmapped. 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Keswiik•2y ago

Your top query works because the second Where clause never touches the DB. var allCategories = dbContext.Set<Model.Entities.Device>().ToList(); fetches every device from the DB, then using normal linq to filter categories below it.

SWEETPONYOP•2y ago

ok I want this I have IEnumerable<string> of categories all I want is to take from the database those objects whose fields contain the necessary categories

Keswiik•2y ago

And what does your schema look like? Do you have a proper one-to-many relationship configured for device => categories? Because what I've seen so far leads me to think you're storing all categories in a column on the device table as a single string

SWEETPONYOP•2y ago

one moment I can show Entity

public class Device : Entity
{
     public string Title { get; set; }
      ... blah blah
      public HashSet<string> Categories
}

public class Device : Entity
{
     public string Title { get; set; }
      ... blah blah
      public HashSet<string> Categories
}

Keswiik•2y ago

I'm talking about your actual database schema, not the models you have in EF

SWEETPONYOP•2y ago

jsonb

Keswiik•2y ago

This a postgres db? not that it makes much difference but you should really move all categories out of that json blob and into their own table if you want them to be queryable. which takes us back to my original suggestion on configuring a proper one-to-many relationship in your database

SWEETPONYOP•2y ago

I don't think so one moment

var result = dbContext.Set<Model.Entities.Device>()
    .Where(x => signatures.Contains( x.DeviceSignature.ToString()))
    .ToList();

var result = dbContext.Set<Model.Entities.Device>()
    .Where(x => signatures.Contains( x.DeviceSignature.ToString()))
    .ToList();

I know it is string but I don't even need another table for searching by DeviceSignature so why do I need another table just for categories I don't understand ..

Keswiik•2y ago

Well, I don't even see a column named DeviceSignature in the schema you posted, so I have no clue what data type you're comparing against

SWEETPONYOP•2y ago

it is EndpointSignature in database schema and it is text

Keswiik•2y ago

Which effectively gets translated to WHERE EndpointSignature IN (...) Not the same as what you're doing with the categories. Storing all categories in a json blob just feels like a poorly designed database, and trying to run complex queries on that isn't going to be as performant as a schema with properly configured relationships (and keys). Considering you're also checking for categories by name, you'd probably want the category names indexed as well, which isn't possible to do with a json blob.

gerard•2y ago

Maybe you can use EF.Functions.JsonContains?

var result = dbContext.Set<Model.Entities.Device>()
    .Where(x => EF.Functions.JsonContains( x.DeviceSignature, "Value" ))
    .ToList();

var result = dbContext.Set<Model.Entities.Device>()
    .Where(x => EF.Functions.JsonContains( x.DeviceSignature, "Value" ))
    .ToList();

The problem is to use multiple values, a hacky way would Sweaty

tested, fyi.

Keswiik•2y ago

I'd really encourage you to move Categories to its own table though. It will be a much better (and scalable) solution long term. And then you can use normal EF queries instead of finding hacky workarounds.

gerard•2y ago

I've been playing around. I've created an extension: https://paste.mod.gg/gdnzgfzdlwea/0 Here you can do the following:

var devices = context.Device
    .WhereJsonContains(d => d.Categories, new[] { "B" })
    .ToList();

var devices = context.Device
    .WhereJsonContains(d => d.Categories, new[] { "B" })
    .ToList();

But yeah, of course a new table would be better. shrug

SWEETPONYOP•2y ago

thanks a lot for helping

Mayor McCheese•2y ago

Gaming

Programming

✅ can't translate linq query