Entity Framework 4 INNER JOIN help

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.

Solution 1

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

Solution 2

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

Solution 3

var query = from p in B.Products
            join c in B.Categories  on p.CategoryID equals c.CategoryID 
            orderby c.CategoryName,p.ProductName 
            select new 
            {
                c.CategoryName ,
                p.ProductName,
                p.ReorderLevel  
            };

GridView1.DataSource = query.ToList();
GridView1.DataBind();