CODEWITHSUNDEEP
c#entity-frameworklinq
jimebe
jimebe

Reputation: 113

Filter on nested property

I have the following models:

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post 
{
    public int Id { get; set; }
    public string Description { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

I have an IQueryable like:

var results = Blog.Include(x => x.Posts);

Everything works great until I want to filter on a property of the Post class. I need something like this:

var filteredResults = results.Where(x => x.Posts.Where(y => y.Description == "Test"));

This works If I append Any() to the second .Where(). This would not be right though because I only want to return the matching Posts, not all. Any suggestions on how to approach this?

Upvotes: 1

Views: 900

Answers (2)

Coco
Coco

Reputation: 174

You could approach this from bottom up.

var blogIds = Posts.Where(x => x.Description == "Test").Select(x => x.BlogId);
var result = Blog.Where(x => blogIds.Contains(x.Id))

Note that you might want to do:

x => x.Description.Contains("Test")

instead of:

x => x.Description == "Test"

in first query

You'll still have to map corresponding posts to each blog though

Update

Steve's answer is correct. I'll just add that it may translate in a lot of nested select queries. You can check the output in SQL Server profiler, or in the output window in Visual Sudio. So here's everything including mapping:

var posts = Posts.Where(x => x.Description == "test").ToList();
var blogIds = posts.Select(x => x.BlogId).ToList();
var blogs = Blog.Where(x => blogIds.Contains(x.Id)).ToList();

foreach(var blog in blogs)
    blog.Posts = posts.Where(x => x.BlogId == x.Id).ToList()

Upvotes: 0

Steve Py
Steve Py

Reputation: 34653

Entities don't filter like this. A Blog entity will, and should refer to ALL Posts it is associated with. EF can apply global filters to data to accommodate things like soft-delete (IsActive) or tenancy (ClientId) scenarios, but not filtered children like this.

This is a view/consumer concern, not a domain one so you should be looking to separate those concerns using Projection to return the data you want:

string postFilter = "Test";

var filteredResults = context.Blogs
    .Where(x => x.Posts.Any(p => p.Description == postFilter))
    .Select(x => new BlogViewModel
    {
        BlogId = x.BlogId,
        Title = x.Title,
        FilteredPosts = x.Posts.Where(p => p.Description == postFilter)
            .Select(p => new PostViewModel
            {
               PostId = p.PostId,
               Description = p.Description,
               Text = p.Text,
               // ...
           {).ToList()
    }).ToList();

Upvotes: 1

Related Questions