Count child record and show zero if empty

Question

I'm fine with both C# and VB.NET

I have two tables. Authors and Books. It's a one to many relationship, Authors to Books. I'm writing a query to show how many books that each author has.

I wrote the following query:

Dim query = From oa In db.Authors _
         Group oa By oa.Book Into grouping = Group _
         Select Author = Book, Count = grouping.Count(Function(s) s.AuthorId)

This query will give the following result:

             - Author A : 2 books
             - Author B : 3 books
             - Author C: 1 book

But in the Authors table, there are some authors without any books yet. For example, there two authors, Author D and Author E that have no books yet.

I want to write query that includes all authors and number of ther books, even though they don't have any book yet, no record in the Books table yet.

I want to get something like like this:

             - Author A : 2 books
             - Author B : 3 books
             - Author C: 1 book
             - Author D: 0 book
             - Author E: 0 book

Thank you.

Answer 1

Is there a reason for the grouping? Doesn't this work?

db.Authors.Select(a => new { Author, BookCount = a.Books.Count });