Refer the following code

public class Author
{
    public string Name { get; set; }
    public ICollection<Book> Books { get; set;}
}

public class Book
{
    public string Name { get; set; }
    public int AuthorId { get; set; }
    public virtual Author Author { get; set; }
}

[HttpGet]
public async Task<IActionResult> GetAuthors([FromQuery] GridifyQuery query)
{
    var authors = DbContext.Authors
        .AsNoTracking()
        .ApplyFiltering(query);

    var authorsDto = await ObjectMapper.ProjectTo<AuthorDto>(authors)
        .ToListAsync();

    return Ok(authorsDto);
}

Problem

I want to directly fetch list of authors from Authors table by applying filter on property Author.Books, which is not possible at the moment (i.e. filtering on ICollection<T>).

I am aware of the workaround where I can fetch the list of Authors by querying the Books table and then navigating back to Authors. However, I would like to avoid this approach and query the Authors table directly.

I tried making Custom filters, but I am having difficulty integrating LINQ queries into the filtering logic.

References

2. EF Core include an ICollection with a filter on the collection's referenced object

Refer the following code

public class Author
{
    public string Name { get; set; }
    public ICollection<Book> Books { get; set;}
}

public class Book
{
    public string Name { get; set; }
    public int AuthorId { get; set; }
    public virtual Author Author { get; set; }
}

[HttpGet]
public async Task<IActionResult> GetAuthors([FromQuery] GridifyQuery query)
{
    var authors = DbContext.Authors
        .AsNoTracking()
        .ApplyFiltering(query);

    var authorsDto = await ObjectMapper.ProjectTo<AuthorDto>(authors)
        .ToListAsync();

    return Ok(authorsDto);
}

Problem

I want to directly fetch list of authors from Authors table by applying filter on property Author.Books, which is not possible at the moment (i.e. filtering on ICollection<T>).

I am aware of the workaround where I can fetch the list of Authors by querying the Books table and then navigating back to Authors. However, I would like to avoid this approach and query the Authors table directly.

I tried making Custom filters, but I am having difficulty integrating LINQ queries into the filtering logic.

References

1. https://alirezanet.github.io/Gridify/guide/filtering#custom-operators
2. EF Core include an ICollection with a filter on the collection's referenced object

• c#
• sql-server
• entity-framework-core
• asp-core-webapi
• gridify

Share Improve this question Follow edited Nov 20, 2024 at 6:52 AkshatSparrow asked Nov 19, 2024 at 11:05 AkshatSparrowAkshatSparrow 4566 bronze badges 2

• Sorry I didn't get that. Gridify enables filtering data directly by passing filters in the query-params(of the URL) itself, eliminating the need to write specific queries in your action methods. However, it does have some limitations—one of them being that it cannot filter on ICollection<T>. I want to create a custom filter for this scenario. – AkshatSparrow Commented Nov 20, 2024 at 6:58
• Maybe you should show how you tried to make a custom filter. – Gert Arnold Commented Nov 20, 2024 at 9:44

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

A simple working demo you could follow:

[HttpGet]
public async Task<IActionResult> GetAuthors([FromQuery] GridifyQuery query)
{
    //query for example: "BookName=book1"
    var mapping = new GridifyMapper<Author>()
                 .AddMap("BookName", x => x.Books.Select(c => c.Name));
    var authors = _context.Author
        .AsNoTracking()
        .Include(a => a.Books)
        .ApplyFiltering(query, mapping).ToList();

    //do your stuff...
    return Ok(authors);
}

The request url should be:https://localhost:portNumber/home/getauthors?filter=BookName=book1, book1 is the name of the Book.