c# - 仅检索循环引用(或父)记录的 LINQ 语句？

Question

SQL 表列

CREATE TABLE [dbo].[CountrySubdivision]
(
    [CountrySubdivisionCode] [VARCHAR]( 10 ) NOT NULL,
        
    [CountryCode] AS
    (
        CONVERT( [CHAR]( 2 ), LEFT( [CountrySubdivisionCode], ( 2 )), ( 0 ))
    ) 
    PERSISTED,
        
    [SubdivisionCode] AS
    (
        CONVERT( [VARCHAR]( 7 ), SUBSTRING( [CountrySubdivisionCode], CHARINDEX( '-', [CountrySubdivisionCode] ) + ( 1 ), LEN( [CountrySubdivisionCode] ) - CHARINDEX( '-', [CountrySubdivisionCode] )), ( 0 ))
    ) 
    PERSISTED,

    [Name]                   [NVARCHAR]( 255 )  NOT NULL,
    [Category]               [VARCHAR]( 50 )    NOT NULL,
    [ParentSubdivisionCode]  [VARCHAR]( 7 )     NULL

    CONSTRAINT [PK_CountrySubdivision] PRIMARY KEY CLUSTERED 
    ( 
        [CountrySubdivisionCode] ASC 
    )
)

SQL 表示例数据

                                                                      Parent
      Country  Subdivision                                          Subdivision 
 Code   Code      Code     Name                Category                Code
---------------------------------------------------------------------------------
GB-ENG   GB       ENG      England             country                  NULL
GB-SCT   GB       SCT      Scotland            country                  NULL
GB-WLS   GB       WLS      Wales               country                  NULL
GB-NIR   GB       NIR      Northern Ireland    province                 NULL
GB-BKM   GB       BKM      Buckinghamshire     two-tier county          ENG
GB-CAM   GB       CAM      Cambridgeshire      two-tier county          ENG
GB-CMA   GB       CMA      Cumbria             two-tier county          ENG
GB-SWK   GB       SWK      Southwark           London borough           ENG
GB-STN   GB       STN      Sutton              London borough           ENG
GB-TWH   GB       TWH      Tower Hamlets       London borough           ENG
GB-LDS   GB       LDS      Leeds               metropolitan district    ENG
GB-LIV   GB       LIV      Liverpool           metropolitan district    ENG
GB-MAN   GB       MAN      Manchester          metropolitan district    ENG

要求

我需要一个只包含父记录的结果集。所以在 LINQ 中，我在想类似的事情:

// Get all Subdivisions.
var csData = GetSubdivisions() as ObservableCollection<CountrySubdivisionData>;

// Get all Parent Subdivisions.
var psData = csData.Where
(
    ps => 
    ps.ParentSubdivisionCode.IsNull() && 
    csData.Any
    (
        cs => 
        cs.ParentSubdivisionCode == ps.SubdivisionCode
    )
)
.OrderBy(ps => ps.CountrySubdivisionCode)
.ThenBy(ps => ps.Category)
.ThenBy(ps => ps.Name)
.ToObservableCollection();

仅供引用，IsNull() 是我使用的一种简单的扩展方法:

public static bool IsNull(this object obj)
{
    return obj == null;
}

问题

是否有比我必须满足的要求更好的 LINQ 语句？

Answer 1

如果我没理解错的话，应该从给定数据集中返回的唯一行是 GB-ENG .

您不必创建中间集合。您可以使用相同的集合，如下所示:

var data = items.Where(i1 =>
    i1.ParentSubdivisionCode == null &&
    items.Any(i2 => i2.ParentSubdivisionCode == i1.SubdivisionCode)
)
.OrderBy(i => i.CountrySubdivisionCode)
.ThenBy(i => i.Category)
.ThenBy(i => i.Name)
.ToList();

如果性能是一个问题(并且取决于您的支持数据集)，您可能想要检索 ParentSubdivisionCode 的集合值首先优化 Contains方法。这仅对 LINQ-to-objects 有效。

var parentSubdivisionCodes = new HashSet<string>(items.Select(i => i.ParentSubdivisionCode));
var data = items.Where(i => 
    i.ParentSubdivisionCode == null &&
    parentSubdivisionCodes.Contains(i.SubdivisionCode)
)
.OrderBy(ps => ps.CountrySubdivisionCode)
.ThenBy(ps => ps.Category)
.ThenBy(ps => ps.Name)
.ToList();

通过替换 items.Any(i2 => i2.ParentSubdivisionCode == i1.SubdivisionCode)与 parentSubdivisionCodes.Contains(i.SubdivisionCode) ，我们删除了嵌套迭代并利用了 HashSet<T>.Contains 的速度.

这是我用来测试的整个程序:

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApplication1 {

    internal class Program {

        static void Main(string[] args) {

            #region
            var store = @"
                GB-ENG,   GB,       ENG,      England,             country,                  NULL
                GB-SCT,   GB,       SCT,      Scotland,            country,                  NULL
                GB-WLS,   GB,       WLS,      Wales,               country,                  NULL
                GB-NIR,   GB,       NIR,      Northern Ireland,    province,                 NULL
                GB-BKM,   GB,       BKM,      Buckinghamshire,     two-tier county,          ENG
                GB-CAM,   GB,       CAM,      Cambridgeshire,      two-tier county,          ENG
                GB-CMA,   GB,       CMA,      Cumbria,             two-tier county,          ENG
                GB-SWK,   GB,       SWK,      Southwark,           London borough,           ENG
                GB-STN,   GB,       STN,      Sutton,              London borough,           ENG
                GB-TWH,   GB,       TWH,      Tower Hamlets,       London borough,           ENG
                GB-LDS,   GB,       LDS,      Leeds,               metropolitan district,    ENG
                GB-LIV,   GB,       LIV,      Liverpool,          metropolitan district,     ENG
                GB-MAN,   GB,       MAN,      Manchester,          metropolitan district,    ENG
            ";

            var items = store
                .Split(new[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries)
                .Select(line => line.Trim())
                .Where(line => line != string.Empty)
                .Select(line => line
                    .Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries)
                    .Select(item => item.Trim())
                    .Where(item => item != string.Empty)
                    .ToArray()
                )
                .Select(item => new {
                    Code = item[0],
                    CountrySubdivisionCode = item[1],
                    SubdivisionCode = item[2],
                    Name = item[3],
                    Category = item[4],
                    ParentSubdivisionCode = item[5] == "NULL" ? default(string) : item[5]
                })
                .ToList();
            #endregion

            #region
            Console.WriteLine();
            items.Where(i1 =>
                i1.ParentSubdivisionCode == null &&
                items.Any(i2 => i2.ParentSubdivisionCode == i1.SubdivisionCode)
            )
            .OrderBy(i => i.CountrySubdivisionCode)
            .ThenBy(i => i.Category)
            .ThenBy(i => i.Name)
            .ToList()
            .ForEach(d => Console.WriteLine(d.Code));
            #endregion

            #region
            Console.WriteLine();
            var parentSubdivisionCodes = new HashSet<string>(items.Select(i => i.ParentSubdivisionCode));
            items.Where(i => 
                i.ParentSubdivisionCode == null &&
                parentSubdivisionCodes.Contains(i.SubdivisionCode)
            )
            .OrderBy(ps => ps.CountrySubdivisionCode)
            .ThenBy(ps => ps.Category)
            .ThenBy(ps => ps.Name)
            .ToList()
            .ForEach(d => Console.WriteLine(d.Code));
            #endregion

            #region
            Console.WriteLine();
            var csData = items;
            csData.Where(ps =>
                ps.ParentSubdivisionCode == null &&
                csData.Any(cs =>
                    cs.ParentSubdivisionCode == ps.SubdivisionCode
                )
            )
            .OrderBy(ps => ps.CountrySubdivisionCode)
            .ThenBy(ps => ps.Category)
            .ThenBy(ps => ps.Name)
            .ToList()
            .ForEach(d => Console.WriteLine(d.Code));
            #endregion

            Console.ReadLine();
        }
    }
}