Apply Global Filter Query on Comma Separated String from another List

Question

I am trying to apply a global filter in my ApplicationDbContext on records containing a comma separated list of roles to create a menu that is applicable to the user's roles.
Here is the model for the menu

public class MainMenu
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string? Roles { get; set; } 
}    

Example:

Id  Name    Roles
2   Test1   Admin
3   Test2   NULL
4   Test3   Admin,Employee

If Roles is defined, it should be filtered if the Role appears in myRole which return a List from a service. If Roles is null then should appear for all users.

myRoles returns "Admin,Other from the service.

Here is ApplicationDbContext

public List<string> myRoles { get; set; }

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    builder.Entity<MainMenu>().HasQueryFilter(p => p.Roles.Split(",").Any(x=>myRoles.Contains(x)));
}    

This gives error

An expression tree may not contain a call or invocation that uses optional arguments

By adding StringSplitOptions.None

builder.Entity<MainMenu>().HasQueryFilter(p => p.Roles.Split(",", StringSplitOptions.None).Any(x => myRoles.Contains(x)));    

I get error

InvalidOperationException: The LINQ expression 'DbSet() .Where(m => m.Roles.Split( separator: ",", options: None) .Any(x => __ef_filter__myRoles_0.Contains(x)))' could not be translated. Additional information: Translation of method 'string.Split' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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 also tried mapping Roles to a List by changing the model

public class MainMenu
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string? Roles { get; set; }
    [NotMapped]
    public List<string>? ListRoles { get { return Roles==null?null:Roles.Split(",").ToList(); } }
}

and then in my ApplicationDbContext

builder.Entity<MainMenu>().HasQueryFilter(p => p.ListRoles.AsEnumerable() == null?true:p.ListRoles.AsEnumerable().Any(x => myRoles.Contains(x)));

but this also gives error

InvalidOperationException: The LINQ expression 'DbSet()
.Where(m => m.ListRoles
.AsEnumerable() == null ? True : m.ListRoles
.Any(x => __ef_filter__myRoles_0.Contains(x)))' could not be translated. Additional information: Translation of member 'ListRoles' on entity type 'MainMenu' failed. This commonly occurs when the specified member is unmapped.
Translation of member 'ListRoles' on entity type 'MainMenu' 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'.

I know I can simply repeat the menu records with individual roles and then simply filter

builder.Entity<MainMenu>().HasQueryFilter(p =>  myRoles.Contains(p.Roles));

but that is not very desirable.

So how do I go about filtering the DBContext for a comma seperated list?

Answer 1

I ended up setting up a BaseController and used the BaseController in all controller where the Entity appears.

In the BaseController

                var menumodel = new List<Menu>();
                var myroles = db.myRoles;
                foreach (var menu in newmodel)
                {
                    if (myroles != null)
                    {
                        if (menu.Roles == null || menu.Roles.Split(",").ToList().Any(x => myroles.Contains(x)))
                        {
                            menumodel.Add(menu);
                        }
                    } 
                }
                ViewBag.Menu = menumodel;

That way I keep my database structure, and since a typical menu never reaches hundreds of items, I don't have to worry too much about performance issues.