实体框架4 INNER JOIN帮助

entity entity-framework inner-join

谁能告诉我如何在Entity Framework 4.0中编写以下查询? “博客”和“类别”是我的实体。该查询基本上返回了类别列表和该类别中的博客数量。

SELECT b.CategoryId, c.Value, Count(b.Id) AS [Count] FROM dbo.Blogs b
INNER JOIN dbo.Categories c ON b.CategoryId = c.Id
GROUP BY b.CategoryId, c.Value

提前致谢。

一般承认的答案

以下应该有效(LinqToEntities):

var categories = from c in oc.Categories
                 select new
                 {
                     CategoryId = c.Id,
                     c.Value,
                     Count = c.Blogs.Count()
                 }

这将为您提供类别ID和值的列表,并为每个类别ID提供该类别中的博客数量。

编辑:要在评论中回答问题:在LinqToEntities中这是不可能的,但您可以在Entity SQL中执行此操作。

var results = new ObjectQuery<DbDataRecord>(
    @"SELECT y, COUNT(y)
      FROM MyEntities.Blogs AS b
      GROUP BY YEAR(b.CreatedDate) AS y", entities).ToList();
var nrBlogsPerYear = from r in results
                     select new { Year = r[0], NrBlogs = r[1] };

在Entity SQL查询中,您应该使用上下文的名称替换MyEntities

编辑:正如我刚才通过克雷格评论发现,在今年的分组可能在L2E所以你可以写你的查询是这样的:

    var nrBlogsPerYear = from b in oc.Blogs
                         group b by b.CreatedDate.Year into g
                         select new { Year = g.Key, NrBlogs = g.Count() };

热门答案

如果您的实体中有导航属性,则可以执行以下操作:

var cats = from c in db.Categories
           let count = c.Blogs.Count()
           where count > 0
           select new
           {
               CategoryId = c.Id,
               Value = c.Value,
               Count = count
           };

如果您更喜欢使用显式连接,可以这样做:

var cats = from c in db.Categories
           join b in db.Blogs on c.Id equals b.CategoryId into g
           select new
           {
               CategoryId = c.Id,
               Value = c.Value,
               Count = g.Count()
           };


许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因