Entity Framework Core - IN 子句等效 [英] Entity Framework Core - IN clause equivalent

问题描述

我想知道如何将这个 sql 查询转换为实体框架查询.

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

我正在尝试获取具有给定类别 ID 的帖子列表.

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

数据库简化:

博客(帖子的类别):

BlogId
Title
..

发布:

PostId
Title
.. 

BlogPost 用于组合两个表并让您每个帖子有多个类别:

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

先谢谢你.

推荐答案

根据您提供的信息，我将如何在一个查询中执行此操作.

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

以下是我将如何在两个查询中执行此操作，以便根据您提供的信息使用 Contains.

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

如果我使用有价值的 EntityFramework 功能，并且我在 BlogPost 上有一个名为 Post 的参考属性，那么我将如何在一个查询中执行此操作.

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

如果我使用有价值的 EntityFramework 功能，我将如何在一个查询中执行此操作，并且我有一个名为 Posts from Blog 的集合属性，并且许多 BlogPost 表被 EntityFramework 以一种您永远不会隐藏的方式隐藏实际上是从 C# 中与之交互的.

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

另一方面，如果 EntityFramework 公开了多对多的 BlogPost 表，那么您仍然可以从博客开始并使用正确配置的集合和引用属性来获取像这样的帖子.

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

或

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

Takeaway，EntityFramework 不是 SQL.您在 SQL 中所做的可能会也可能不会直接映射，甚至适用于您在 EntityFramework 中的处理方式.不仅如此，当您使用 EntityFramework 时，您使用的是 EntityFramework 和 C# 语言功能，这些功能本身不是 EntityFramework，LINQ.将问题分解为其组成部分可以帮助您解决问题，并使学习变得更容易，从而能够进行更复杂的操作.单独学习和练习 LINQ 将帮助您更好地使用 EntityFramework.

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.

这篇关于Entity Framework Core - IN 子句等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！