Entity Framework Core - Member IN equivalent

Question

I wonder how to convert this SQL query into an entity structure query.

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

I am trying to get a list of posts with the specified category id.

The database is simplified:

Blog (post category):

BlogId
Title
..

Message:

PostId
Title
.. 

BlogPost is for joining two tables and allows you to have several categories per post:

PostId
BlogId

This is what I already have, but of course the request does not work:

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 );
}

Thanks in advance.

Answer 1

Here's how I would do it in one request 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's how I would do it in two queries to use Containsbased 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();

, EntityFramework, Post BlogPost.

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

, EntityFramework, , Posts from Blog, - BlogPost EntityFramework , #.

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

, EntityFramework - BlogPost, , .

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

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

, EntityFramework - SQL. , SQL, , , EntityFramework. , EntityFramework, EntityFramework #, EntityFramework, LINQ. . LINQ EntityFramework.

Answer 2

LINQ

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);

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

Answer 3

post to blog, where .

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 at the top of your code.

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

Answer 4

EDITED

you can use .Select()

 var blog = await _context.Blogs.FirstOrDefaultAsync(b => b.Id == blogId);
 var posts = blog ?? blog.BlogCategory.Select(bp => bp.Post);

I assume that you have a navigation property in Blog BlogCategory You can find this useful link