How to get average value using LEFT JOIN in LINQ and Lambda

I want to display each book average rating with its publisher using LINQ and Lambda.

Here’s my book list

private List<Book> Books = new List<Book>(){
new Book {
Id: 1,
Name: Book A,
PublisherId: 1
}, new Book {
Id: 2,
Name: Book B,
PublisherId: 2
}, new Book {
Id: 3,
Name: Book C,
PublisherId: 1
}
};

First, i Join Book list with Publisher list using this query

 var bookPublisher = Books.SelectMany( book => Publishers.Where(publisher => book.PublisherId == publisher.Id),
            (book, publisher) => new { book, publisher });

Then, i try to use left join with BookTransaction list to get detail rating for each book. Here’s my BookTransaction list.

private List<Book_Transaction> BookTransactions = new List<Book_Transaction>() {
        new Book_Transaction {
            Id = 1,
            BookId = 1,
            CustomerId = 1,
            RatingStar = 4.5
        }, new Book_Transaction {
            Id = 2,
            BookId = 2,
            CustomerId = 1,
            RatingStar = 4
        }, new Book_Transaction {
            Id = 3,
            BookId = 1,
            CustomerId = 2,
            RatingStar = 5
        },new Book_Transaction {
            Id = 4,
            BookId = 2,
            CustomerId = 2,
            RatingStar = 3.5
        },new Book_Transaction {
            Id = 5,
            BookId = 1,
            CustomerId = 3,
            RatingStar = 4
        }};

and here’s my query

var bookPublisherRating = bookPublisher.SelectMany(bp => BookTransactions.Where(bt => bp.book.Id == bt.BookId).DefaultIfEmpty(),
                (bp, bt) => new { BookPublish = bp, BookRating = bt.RatingStar });

in that query, i got the error saying that “‘Object reference not set to an instance of an object.’ bt was null.”

I tried using different approach, but the result it just the same error.

var bookPublisherRating = bookPublisher.GroupJoin(BookTransactions,
            bp => bp.book.Id,
            bt => bt.BookId,
            (bp, bt) => new { BookPublish = bp, BookTrans = bt })
                .SelectMany(temp => temp.BookTrans.DefaultIfEmpty(),
                (temp, y) => new { BookPublish = temp.BookPublish, bookRating = y.RatingStar });

Can someone tell me where i was wrong? When i deleted the DefaultIfEmpty function, it can display the data but only where both list have the bookId value included, like this

| BookName  | PublisherName | Rating |
| Book A    | Publisher A   | 4.5    |
| Book A    | Publisher A   | 5      |
| Book A    | Publisher A   | 4      |
| Book B    | Publisher B   | 4      |
| Book B    | Publisher B   | 3.5    |

I expect the result of the query just like this

| BookName  | PublisherName | Rating |
| Book A    | Publisher A   | 4.5    |
| Book B    | Publisher B   | 3.75   |
| Book C    | Publisher A   | 0      |

Answer

Linq has an Average Method. You can call it on a collection of numeric values, or for a collection of objects you can pass it the property name to average. You can also call GroupBy to create key<->collection pairs.

So you can group your data by the BookId and find the average rating.

Assuming the following

public class Book_Transaction
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public int CustomerId { get; set; }
    public double RatingStar { get; set; }
}

var bookTransactions = new List<Book_Transaction>() {
    new Book_Transaction {
        Id = 1,
        BookId = 1,
        CustomerId = 1,
        RatingStar = 4.5
    }, new Book_Transaction {
        Id = 2,
        BookId = 2,
        CustomerId = 1,
        RatingStar = 4
    }, new Book_Transaction {
        Id = 3,
        BookId = 1,
        CustomerId = 2,
        RatingStar = 5
    },new Book_Transaction {
        Id = 4,
        BookId = 2,
        CustomerId = 2,
        RatingStar = 3.5
    },new Book_Transaction {
        Id = 5,
        BookId = 1,
        CustomerId = 3,
        RatingStar = 4
    }};

Then

bookTransactions.GroupBy(
    bt => bt.BookId,
    (bookId, collection) => new {
        BookId = bookId,
        AverageRating = collection.Average(x => x.RatingStar)
    }).ToList()  

Gives the following output in the C# interactive console:

List<<>f__AnonymousType0#5<int, double>>(2) { 
    { BookId = 1, AverageRating = 4.5 }, 
    { BookId = 2, AverageRating = 3.75 }
}