Linq using join and grouping

Question

I'm trying to do something very simple. I have two tables in my database that I would like to query using linq. Table of Books, and table of GenreTypes. The result of this query would go to my web Api.

Here is a code snippet:

public List<BooksChart> GetBooksChart()
{
    var results = from b in _dbcontext.Books
                  join g in _dbcontext.GenreTypes
                  on b.GenreTypeId equals g.Id
                  group g by g.Name into n
                  select (z => new BooksChart
                  {
                      category_name = n.Key,
                      value = n.Count()
                  }).ToList();

    return results;
}

public class BooksChart
{
    public string category_name;
    public int value;
}

The results of the grouping "n" I would like to store them in BooksChart class to construct the Api.

This code is not compiling.

Previously, I was querying only one table of Books which I have divided into Books and GenreTypes.

My previous working code for querying Books was :

var results = _dbcontext
    .Books
    .GroupBy(x => x.GenreType)
    .Select(z => new BooksPieChart
    {
        category_name = z.Key,
        value = z.Count()
        }).ToList();

        return results;

EDIT

What I want to achieve in SQL is the following:

select count(*), g.Name
from books b, GenreTypes g
where b.GenreTypeId = g.Id
group by g.Name;

Not compiling? What is the compilation error? Also why join table if not using data from that table? Sure it is what you want? — Gilad Green, Dec 03 '17 at 13:08
I see that @GiladGreen has answered you correctly. However on a side note. Why don't you have a relationship in your dbcontext between `Books` and `GenreTypes`? That would make things much simpler — Rabbi, Dec 03 '17 at 13:14

Gilad Green · Accepted Answer · 2017-12-03T13:34:59.437

You are mixing the two syntax options of query and method. For query syntax you need to do the projection (select) like this:

return (from b in _dbcontext.Books
        join g in _dbcontext.GenreTypes on b.GenreTypeId equals g.Id
        group g by g.Name into n
        select new BooksChart {
            category_name = n.Key,
            value = n.Count()
        }).ToList();

The format of (z =>....) is the declaration of the labmda passed to the Select method.

Site notes:

As @Rabbi commented, since you are using EF, consider properly defining navigation properties. It will make querying simpler.
Side note for the sql - consider using joins instead of multiple tables in the from: INNER JOIN ON vs WHERE clause

Ousmane D. · Answer 2 · 2017-12-03T13:14:41.113

The parentheses must surround the whole query, like so:

var results = (from b in _dbcontext.Books
               join g in _dbcontext.GenreTypes
               on b.GenreTypeId equals g.Id
               group g by g.Name into n
               select new BooksChart
               {
                   category_name = n.Key,
                   value = n.Count()
               }).ToList();

The compilation error is due to this (z => which is not needed at all.

Linq using join and grouping

2 Answers2