Filtering with Many to Many case

[khoavn]

I have 2 EF Entities like this

public class User
{
    public List<Group> Groups { get; set;}
}

public class Group
{
    public string Name { get; set; }
    public List<User> Users { get; set; }
}

And here is my mapper

var mapper = new GridifyMapper<User>()
            .GenerateMappings()
            .AddMap("groupName", u => u.Groups.First().Name);

It can run but the result is incorrect, here's the generated SQL

SELECT [t].[Id], [t].[UserName], [t0].[Id], [t0].[Name], [t0].[GroupId], [t0].[UserId]
      FROM (
          SELECT [u].[Id], [u].[UserName]
          FROM [Users] AS [u]
          WHERE (
              SELECT TOP(1) [g].[Name]
              FROM [UserGroup] AS [u0]
              INNER JOIN [Groups] AS [g] ON [u0].[GroupId] = [g].[Id]
              WHERE [u].[Id] = [u0].[UserId]) = 'foo'
      ) AS [t]
      LEFT JOIN (
          SELECT [g0].[Id], [g0].[Name], [u1].[GroupId], [u1].[UserId]
          FROM [UserGroup] AS [u1]
          INNER JOIN [Groups] AS [g0] ON [u1].[GroupId] = [g0].[Id]
      ) AS [t0] ON [t].[Id] = [t0].[UserId]

So please help me to create the correct mapper.

Thank you!

[alirezanet]

Hi @khoavn,
I believe this solution will resolve your issue:

   .AddMap("groupName", q => q.Groups.Select(g => g.Name));

more info here

[khoavn]

Hi @alirezanet ,

Thanks for your response, I tried this solution before and got this message

Error message: The LINQ expression 'DbSet<User>()
          .Where(u => MaterializeCollectionNavigation(
              Navigation: User.Groups,
              subquery: DbSet<UserGroup>("UserGroup")
                  .Where(u0 => EF.Property<long?>(u, "Id") != null && object.Equals(
                      objA: (object)EF.Property<long?>(u, "Id"),
                      objB: (object)EF.Property<long?>(u0, "UserId")))
                  .Join(
                      inner: DbSet<Group>(),
                      outerKeySelector: u0 => (object)EF.Property<long?>(u0, "GroupId"),
                      innerKeySelector: g => (object)EF.Property<long?>(g, "Id"),
                      resultSelector: (u0, g) => new TransparentIdentifier<UserGroup, Group>(
                          Outer = u0,
                          Inner = g
                      ))
                  .Select(ti => ti.Inner)) != null && DbSet<UserGroup>("UserGroup")
              .Where(u1 => EF.Property<long?>(u, "Id") != null && object.Equals(
                  objA: (object)EF.Property<long?>(u, "Id"),
                  objB: (object)EF.Property<long?>(u1, "UserId")))
              .Join(
                  inner: DbSet<Group>(),
                  outerKeySelector: u1 => (object)EF.Property<long?>(u1, "GroupId"),
                  innerKeySelector: g0 => (object)EF.Property<long?>(g0, "Id"),
                  resultSelector: (u1, g0) => new TransparentIdentifier<UserGroup, Group>(
                      Outer = u1,
                      Inner = g0
                  ))
              .Any(ti0 => ti0.Inner.Name == __Value_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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

There are 3 tables: Users - UserGroup - Groups. The User entity doesn't have navigation property to UserGroup table, the same for Group entity.

[alirezanet]

apparently, this query is not supported by EntityFramework:

 _dbContext.Users.Where(u => u.Groups != null && u.Groups.Any(g => g.Name == "test")).ToQueryString();

but this is supported,

 _dbContext.Users.Where(u =>  u.Groups.Any(g => g.Name == "test")).ToQueryString();

In the next version of the Gridify.EntityFramework package, this issue will be addressed. (please follow issue #173)

---

As a workaround for now, I would suggest disabling the null checks in your global configurations

   GridifyGlobalConfiguration.DisableNullChecks = true;

[alirezanet]

The issue has been resolved in version 2.14.2 of the Gridify.EntityFramework package when the compatibility layer is enabled.

[khoavn]

thank you!