时间戳

首页 > 解决方案 > 如何在 LINQ 和 Lambda 中使用 LEFT JOIN 获得平均值

c# - 如何在 LINQ 和 Lambda 中使用 LEFT JOIN 获得平均值

问题描述

我想使用 LINQ 和 Lambda 向其出版商显示每本书的平均评分。

这是我的书单

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

首先，我使用此查询加入图书列表和出版商列表

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

然后，我尝试使用左连接和 BookTransaction 列表来获取每本书的详细评级。这是我的 BookTransaction 列表。

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

这是我的查询

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

在那个查询中，我收到错误消息，说“'对象引用未设置为对象的实例。' bt 为空。”

我尝试使用不同的方法，但结果却是同样的错误。

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

有人能告诉我哪里错了吗？当我删除 DefaultIfEmpty 函数时，它可以显示数据，但只有在两个列表都包含 bookId 值的情况下，像这样

| 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    |

我希望查询的结果就像这样

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

标签： c#sqllinqlambdaleft-join

解决方案

Linq 有一个Average方法。您可以在一组数值上调用它，或者对于一组对象，您可以将属性名称传递给它以进行平均。您也可以调用GroupBy创建密钥<->集合对。

因此，您可以按 对数据进行分组BookId并找到平均评分。

假设以下

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

然后 

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

在 C# 交互式控制台中提供以下输出： 

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

推荐阅读