Write a child entry and show zero if it is empty

Question

I am well versed in C # and VB.NET

I have two tables. Authors and books. This is a one to many relationship, the authors of books. I am writing a request to show how many books 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 produce the following result:

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

But in the table of authors there are some authors who do not have books. For example, there are two authors: author D and author E, there are no books yet.

I want to write a query that includes all authors and the number of these books, even if they don’t have a book yet, there is no record in the “Books” table.

I want to get something like this:

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

Thanks.

Answer 1

Is there a reason for grouping? Doesn't that work?

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