C#编写复杂LINQ查询过滤重复分类值的实现方法

给定数据源 #

数据源为门店集合，每个门店包含基础信息和在售书籍列表，结构如下：

[
    {
        "id": 1,
        "shopname": "seven up",
        "shopkeeper": "John",
        "salesbooks": [
            {
                "bookid": 11,
                "bookname": "Tom-story",
                "catagories" : 1,
                "soldout": false
            },
            {
                "bookid": 12,
                "bookname": "Iron-Man",
                "catagories" : 2,
                "soldout": true
            }
        ]
    },
    {
        "id": 2,
        "shopname": "Richmond Shop",
        "shopkeeper": "Jame",
        "salesbooks": [
            {
                "bookid": 11,
                "bookname": "Tom-story",
                "catagories" : 2,
                "soldout": false
            },
            {
                "bookid": 13,
                "bookname": "PeterPan",
                "catagories" : 2,
                "soldout": true
            }
        ]
    },
    {
        "id": 3,
        "shopname": "Worchester Shop",
        "shopkeeper": "Jame",
        "salesbooks": [
            {
                "bookid": 15,
                "bookname": "Jurias Park",
                "catagories" : 1,
                "soldout": false
            },
            {
                "bookid": 16,
                "bookname": "Champion",
                "catagories" : 1,
                "soldout": true
            }
        ]
    }
]

预期输出 #

仅返回id=1的seven up门店，过滤掉分类全重复的2、3号门店，预期结果如下：

[
    {
        "id": 1,
        "shopname": "seven up",
        "shopkeeper": "John",
        "salesbooks": [
            {
                "bookid": 11,
                "bookname": "Tom-story",
                "catagories" : 1,
                "soldout": false
            },
            {
                "bookid": 12,
                "bookname": "Iron-Man",
                "catagories" : 2,
                "soldout": true
            }
        ]
    }
]

需求规则 #

过滤需要同时满足两个条件：

• 门店存在至少一本未售罄（soldout = false）的书籍
• 门店在售书籍的分类（catagories）去重后数量大于1，即排除所有书籍分类完全相同的门店

原有写法问题 #

你之前写的代码存在3个问题，导致结果不符合预期：

1. 属性名拼写错误：集合中书籍列表属性名为salesbooks，你写的salebooks少了字母s
2. 条件逻辑写反：你写的GroupBy(g=>g.catagories).count ==1是保留分类全重复的门店，和你要排除这类门店的需求完全相反
3. 语法错误：LINQ标准方法为帕斯卡命名（首字母大写），且Count是需要加括号调用的方法，不是属性

正确实现代码 #

基础写法 #

var filteredShops = bookshops
    .Where(shop => 
        shop.salesbooks.Any(book => book.soldout == false)
        && shop.salesbooks.GroupBy(book => book.catagories).Count() > 1
    )
    .ToList();

性能更优写法 #

使用Distinct()去重分类后计数，相比GroupBy开销更小：

var filteredShops = bookshops
    .Where(shop => 
        // 判断存在未售罄书籍，和book.soldout == false等价
        shop.salesbooks.Any(book => !book.soldout)
        // 分类去重后计数大于1
        && shop.salesbooks.Select(book => book.catagories).Distinct().Count() > 1
    )
    .ToList();

运行以上代码即可得到预期结果，仅返回id=1的seven up门店。

内容的提问来源于stack exchange，提问作者Carrie Kaski