CODEWITHSUNDEEP
linqef-code-first
Thomas
Thomas

Reputation: 5998

EF Code First selecting rows based on many to many relationship

I have the following code in my repository:

public PagedResult<Post> GetAllPublishedByTag(int tagId, int start, int max)
{
    var query = Database.Set<Post>().Where(p => p.IsPublished)
                                    .OrderByDescending(p => p.CreatedAt)
                                    .Skip(start)
                                    .Take(max);

    int total = query.Count();
    var result = query.ToList();

    return new PagedResult<Post>(result, total);
}

This will give me all published posts. But what I want is selecting all published posts for a certain tag. My model is setup in such a way that tags have a many to many relationship to posts. I tried to slightly modify the above code but this did not work:

public PagedResult<Post> GetAllPublishedByTag(Tag tag, int start, int max)
{
    var query = Database.Set<Post>().Where(p => p.Tags.Contains(tag) && p.IsPublished)
                                    .OrderByDescending(p => p.CreatedAt)
                                    .Skip(start)
                                    .Take(max);

    int total = query.Count();
    var result = query.ToList();

    return new PagedResult<Post>(result, total);
}

I would prefer to pass in the tagId (as per the first code example) as opposed to the tag object but not sure how to correctly write the LINQ statement.

Upvotes: 0

Views: 245

Answers (1)

Betty
Betty

Reputation: 9189

var query = Database.Set<Post>().Where(p => p.Tags.Any(t => t.Id == tagId) && p.IsPublished)
.OrderByDescending(p => p.CreatedAt)
.Skip(start)
.Take(max);

Side Note: I believe you may have issues with your pagination, as the variable total is calculated after skip/take are called.

Upvotes: 1

Related Questions