Help with INNER JOIN in Entity Framework 4

entity entity-framework inner-join

Question

Can anyone tell me how to write the following query in Entity Framework 4.0? "Blogs" and "Categories" are my entities. The query basically returns me the list of categories and the number of blogs that are in that category.

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

Thanks in advance.

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

Accepted Answer

The following should work (LinqToEntities):

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

This will give you a list of category ids and values and for each category id you get the number of blogs in that category.

EDIT: To give an answer to the question in your comment: this isn't possible in LinqToEntities but you can do it in 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] };

In the Entity SQL query, you should replace MyEntities with the name of your context.

EDIT: As I just found out through a comment by Craig, grouping by year is possible in L2E so you can write your query like this:

    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 you have navigation properties in your entities, you can do that :

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

If you prefer to use an explicit join, you can do it like that :

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