Help with INNER JOIN in Entity Framework 4

entity entity-framework inner-join

Question

I need help writing the following query in Entity Framework 4.0. Can anybody help? My entities are "Categories" and "Blogs." In essence, the query gives me a list of categories and the number of blogs that fall within each group.

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

I appreciate it.

1
2
8/14/2010 7:08:08 PM

Accepted Answer

(LinqToEntities) The following should function:

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

You will get a list of category ids and values along with the quantity of blogs in each category for each category id.

EDIT: To address the query in your remark, you can accomplish this using Entity SQL but not with LinqToEntities.

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

Replace the following in the Entity SQL query:MyEntities using the context's name.

EDIT: I recently learned that grouping by year Possible is in L2E allows you to formulate your query as follows:

    var nrBlogsPerYear = from b in oc.Blogs
                         group b by b.CreatedDate.Year into g
                         select new { Year = g.Key, NrBlogs = g.Count() };
3
8/16/2010 2:09:08 PM

Popular Answer

If your entities contain navigation attributes, you may accomplish the following:

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

You may accomplish it in the following way if you wish to use an explicit join:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow