Entity Framework Core - IN clause equivalent

Question

I wonder how to convert this sql query to an entity framework query.

SELECT * 
FROM Post
WHERE PostId IN (
SELECT PostId FROM BlogPost
WHERE BlogId = &blogId);

I am trying to get a list of Posts with a given category id.

Database simplified:

Blog (the category for the post):

BlogId
Title
..

Post:

PostId
Title
.. 

BlogPost for combining the two tables and letting you have multiple categories per post:

PostId
BlogId

This is what I already have, but ofcourse the query is not working:

public async Task<IActionResult> Category(int? id)
{
     int blogId = id;

     if (blogId == null)
     {
         return NotFound();
     }
     ICollection<Post> posts = await _context.Post.Where(pid => pid.PostId.Contains(_context.BlogPost.Where(i => i.PostId == blogId).ToListAsync())).ToListAsync();

     if (posts == null)
     {
         return NotFound();
     }

     return View(posts );
}

Thank you in advance.

Answer 1

Here is how I would do it in one query based on the information you provided.

var posts = await _context.Post     .Where(post =>         _context.BlogPost.Any(bp => bp.BlogId == blogId && bp.PostId == post.PostId)     )     .ToListAsync(); 

Here is how I would do it in two queries in order to use Contains based on the information you provided.

var postIds = await _context.BlogPost     .Where(bp => bp.BlogId = blogId)     .Select(bp => bp.PostId)     .ToArrayAsync(); var posts = await _context.Post     .Where(p => postIds.Contains(p.PostId))     .ToListAsync(); 

Here is how I would do it in one query if I were using valuable EntityFramework features, and I had a reference property named Post on BlogPost.

var posts = await _context.BlogPost     .Where(bp => bp.BlogId == blogId)     .Select(bp => bp.Post)     .ToListAsync(); 

Here is how I would do it in one query if I were using valuable EntityFramework features, and I had a collection property named Posts from Blog, and the many-many BlogPost table was hidden by EntityFramework in such a way that you never actually interact with it from C#.

var posts = await _context.Blog     .Where(b => b.BlogId == blogId)     .SelectMany(b => b.Posts)     .ToListAsync(); 

On the other hand if the many-many BlogPost table is exposed by EntityFramework then you could still start at the Blog and use properly configured collection and reference properties to get to the posts like so.

var posts = await _context.Blog     .Where(b => b.BlogId == blogId)     .SelectMany(b => b.BlogPosts)     .Select(bp => bp.Post)     .ToListAsync(); 

Or

var posts = await _context.Blog     .Where(b => b.BlogId == blogId)     .SelectMany(b => b.BlogPosts.Select(bp => bp.Post))     .ToListAsync(); 

Takeaway, EntityFramework is not SQL. What you do in SQL may or may not map directly, or even apply to how you would go about it in EntityFramework. Not only that but when you use EntityFramework you are using EntityFramework and C# language features that are not EntityFramework per se, LINQ for example. Breaking down the problem into its constituent parts can help you solve problems, and make it easier to study up to being able to do more complex operations. Studying and practicing LINQ in isolation will help you be better with EntityFramework.

Answer 2

LINQ Query

from p in _context.Post
where _context.BlogCategory.Any
                ( bc => bc.PostId == p.PostId 
                  && bc.BlogId == &id
                )
select p;

SQL

SELECT * 
FROM Post
WHERE PostId IN (
SELECT PostId FROM BlogCategory
WHERE BlogId = &id);

OR

SELECT * 
FROM Post p
WHERE EXISTS
    (
      SELECT 1 FROM BlogCategory
      WHERE PostId = p.PostID AND BlogId = &id
    );

Answer 3

You can use a join for the post to blog relationship and then a where clause as the blog filter.

var query = from post in _context.Post
  join blogCat in _context.BlogPost on post.PostId equals blogCat.PostId
  where blogCat.BlogId == blogId
  select post;

var result = await query.ToListAsync();

This is based on your Sql in the top of your code

SELECT * 
FROM Post
WHERE PostId IN (
  SELECT PostId FROM BlogPost WHERE BlogId = &id);