Filtering on a nested collection's field, with surrounding parentheses and exactly 2 passed filter values for the field, generates an incorrect query

[sebdm]

Version

2.8.1

Details

I'm filtering on a nested collection, mapped like so:

_gridifyMapper = new GridifyMapper<Model>(true);
_gridifyMapper.AddMap("AppId", a => a.Applications.Select(app => app.AppId));

If I pass it a filter such as appId=1,appId=2, I get something like this (which I think is correct):

WHERE (EXISTS (
        SELECT 1
        FROM [UserApplications] AS [d0]
        WHERE ([u].[UserId] = [d0].[UserId]) AND ([d0].[AppId] = @__Value_2)) AND EXISTS (
        SELECT 1
        FROM [UserApplications] AS [d1]
        WHERE ([u].[UserId] = [d1].[UserId]) AND ([d1].[AppId] = @__Value_3))

However, if I surround the filter string with parentheses, it instead becomes this (which seems incorrect):

WHERE (EXISTS (
        SELECT 1
        FROM [UserApplications] AS [d0]
        WHERE ([u].[UserId] = [d0].[UserId]) AND (([d0].[AppId] = @__Value_2) AND ([d0].[AppId] = @__Value_3))

If I then add a third appId in the filter string, still surrounded with parentheses, it generates the correct query, again.

Seems like a bug to me?

I'm using EF Core 6, if that makes any difference.

Steps to reproduce

• Create a mapper and map a field to a nested collection.
• Query with a filter string such as (field=value1,field=value2) (parentheses are important, and it has to be exactly 2 values passed (1 or (3 or more) will generate the correct query).

[alirezanet]

Hi @sebdm,

I think this is not a bug, but rather a design choice; in this case, your parenthesis is redundant.

when you don't use any parenthesis by default you get something like the below query:

var query = list.Where(q => q.subList.Any(w=> w.field == value)  && q.subList.Any(w=> w.field2 == value2)  ); 

but sometimes we want both conditions inside the Any function. like:

var query = list.Where(q => q.subList.Any(w=> w.field == value && w.field2 == value2) ); 

So we can use a pair of parenthesis to group two conditions in one expression. (field=value1,field2=value2).

if you want to have separate expressions with parenthesis you have to add another set of parenthesis around each part. (field=value1),(field2=value2)

PS. I didn't test this fully yet,
Let me know if you solved the problem or not.

[sebdm]

First of all, thanks for your quick response (and for a great library :))

These do what I want:
field=value1,field=value2
(field=value1),(field=value2)

These don't:
(field=value1,field=value2)
((field=value1),(field=value2))

So basically this is limiting me a bit when it comes to some edge cases.

Maybe it would be more clear with a different operator for "IN", e g:
field IN value1,value2

Or perhaps even cleaner if I didn't need to map the nested field to the top-level at all, e g:
child.field IN value1,value2

Or for multiple where's on the same IN:
child.field as F IN f>lower,f<upper

These are just ideas off the top of my head. I realize that implementing this in expression trees is far from trivial (not my expertise anyway :/)

I can, for now, in this particular case, just remove the parentheses altogether, but I can see other potential cases when that wouldn't be a viable solution due to this ambiguity.

[alirezanet]

These are just ideas off the top of my head. I realize that implementing this in expression trees is far from trivial (not my expertise anyway :/)
I can, for now, in this particular case, just remove the parentheses altogether, but I can see other potential cases when that wouldn't be a viable solution due to this ambiguity.

To make the edge cases clear it would be great if you can provide a LINQ version of the query that is not supported by gridify,
because after all, I'm trying to convert a small query language to a LINQ expression, So far I managed to pass the tests of all possible scenarios that came to my mind but because it's the complexity I won't be surprised if still there are other edge cases that I missed.

Also, I should admit that parenthesis in sublists behaves differently, probably I should've documented it better.

In the end, If you found any edge case that is not supported yet, I'll be happy to add it to the library.

Thanks

[alirezanet]

Maybe it would be more clear with a different operator for "IN", e g:
field IN value1,value2

I forget this one. This is a good idea to somehow support parenthesis only for values ( filed=(value1,value2) ) but I haven't done that because this way you have to deal with a lot of escape characters.

although with the custom operators feature, you can implement your own IN if you need it.