c# - 根据基础实体的公共(public)标识符过滤相关实体

我想通过实体基础标识符的公共标识符对实体列表进行分组，选择每个组的第一个实体并返回新的实体列表。涉及3个不同的实体：GenericObject，ObjectVersion和ObjectDependency。

public class GenericObject
{
    public int Id { get; set; }
}

public class ObjectVersion
{
    public int Id { get; set; }
    public GenericObject GenericObject { get; set; }
}

public class ObjectDependency
{
    public string Name { get; set; }
    public ObjectVersion ObjectVersion1 { get; set; }
    public ObjectVersion ObjectVersion2 { get; set; }
}

示例设置如下所示：

GenericObject go1 = new GenericObject { Id = 1 };
GenericObject go2 = new GenericObject { Id = 2 };
GenericObject go3 = new GenericObject { Id = 3 };

ObjectVersion ov1 = new ObjectVersion { Id = 1, GenericObject = go1 };
ObjectVersion ov2 = new ObjectVersion { Id = 2, GenericObject = go2 };
ObjectVersion ov3 = new ObjectVersion { Id = 3, GenericObject = go3 };
ObjectVersion ov4 = new ObjectVersion { Id = 4, GenericObject = go1 };

List<ObjectDependency> dependencies = new List<ObjectDependency>
{
     new ObjectDependency { Name = "d1", ObjectVersion1 = ov1, ObjectVersion2 = ov2 },
     new ObjectDependency { Name = "d2", ObjectVersion1 = ov2, ObjectVersion2 = ov3 },
     new ObjectDependency { Name = "d3", ObjectVersion1 = ov4, ObjectVersion2 = ov2 }
};

为了获得所有包含ov2的ObjectDependencies，我将进行如下过滤：

var ov2Dependencies = dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id
                                              || d.ObjectVersion2.Id == ov2.Id)
                                  .OrderBy(d => d.Name);
foreach (ObjectDependency dependency in ov2Dependencies)
{
    Console.WriteLine(dependency.Name);
}
// Output:
// d1
// d2
// d3

为了获得所有依赖于ov2的ObjectVersions：

var ov2AllDependentObjectVersions =
             dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id
                                     || d.ObjectVersion2.Id == ov2.Id)
                         .Select(d => d.ObjectVersion1)
                         .Union(dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id
                                                       || d.ObjectVersion2.Id == ov2.Id)
                         .Select(d => d.ObjectVersion2))
                         .Where(o => o.Id != ov2.Id)
                         .OrderBy(o => o.Id);
foreach (ObjectVersion ov in ov2AllDependentObjectVersions)
{
    Console.WriteLine(ov.Id);
}
// Output:
// 1
// 3
// 4

为了获得具有不同GenericObject的依赖于ov2的最新ObjectVersions：

var ov2LatestDependentObjectVersions =
         dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id
                                 || d.ObjectVersion2.Id == ov2.Id)
                     .Select(d => d.ObjectVersion1)
                     .Union(dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id
                                                    || d.ObjectVersion2.Id == ov2.Id)
                     .Select(d => d.ObjectVersion2))
                     .Where(o => o.Id != ov2.Id)
                     .GroupBy(o => o.GenericObject.Id)
                     .Select(g => g.OrderByDescending(o => o.Id).FirstOrDefault())
                     .OrderBy(o => o.Id);
foreach (ObjectVersion ov in ov2LatestDependentObjectVersions)
{
    Console.WriteLine(ov.Id);
}
// Output:
// 3
// 4

为了实现以下输出，过滤效果如何？基本上，我想获取包含带有不同GenericObject的ov2的最新ObjectDependencies。使用IQueryable可以将过滤直接转换为T-SQL。

var ov2LatestDependencies = dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id
                                                     || d.ObjectVersion2.Id == ov2.Id)
                                          // ???
                                         .OrderBy(d => d.Name);
foreach (ObjectDependency dependency in ov2LatestDependencies)
{
    Console.WriteLine(dependency.Name);
}
// Output:
// d2
// d3

我在这里创建了一个小提琴：https://dotnetfiddle.net/OZQlWO

任何帮助将非常感激！

编辑：

我最终根据杰森·博伊德（Jason Boyd）的回答，使用了以下支持LINQ的实体解决方案：https://dotnetfiddle.net/YSj8ki

var ov2LatestDependencies = dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id
                                                     || d.ObjectVersion2.Id == ov2.Id)
    .Where(x => x.ObjectVersion1.Id == ov2.Id)
    .Select(x => new
    {
        ObjectDependency = x,
        ObjectVersion = x.ObjectVersion2
    })
    .Union(
        dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id
                                                     || d.ObjectVersion2.Id == ov2.Id)
        .Where(x => x.ObjectVersion2.Id == ov2.Id)
        .Select(x => new
        {
            ObjectDependency = x,
            ObjectVersion = x.ObjectVersion1
        })
    )
    .GroupBy(x => x.ObjectVersion.GenericObject.Id)
    .Select(x => x.OrderByDescending(y => y.ObjectVersion.Id).FirstOrDefault())
    .Select(x => x.ObjectDependency)
    .OrderBy(d => d.Name);
foreach (ObjectDependency dependency in ov2LatestDependencies)
{
    Console.WriteLine(dependency.Name);
}
// Output:
// d2
// d3

最佳答案

因此，我自由地将您的LINQ查询重构为扩展方法-这样可以更轻松地了解正在发生的事情：

public static class Extensions
{
    public static IQueryable<ObjectDependency> WhereContainsObjectVersion(this IQueryable<ObjectDependency> source, int objectVersionId)
    {
        return
            source
            .Where(x => x.ObjectVersion1.Id == objectVersionId || x.ObjectVersion2.Id == objectVersionId);
    }

    public static IQueryable<ObjectVersion> SelectDependentObjectVersions(this IQueryable<ObjectDependency> source, int objectVersionId)
    {
        return
            source
            .WhereContainsObjectVersion(objectVersionId)
            .Select(x => x.ObjectVersion1.Id == objectVersionId ? x.ObjectVersion2 : x.ObjectVersion1);
    }

    public static IQueryable<TResult> SelectDependentObjectVersions<TResult>(this IQueryable<ObjectDependency> source, int objectVersionId, Func<ObjectDependency, ObjectVersion, TResult> selector)
    {
        return
            source
            .WhereContainsObjectVersion(objectVersionId)
            .Select(x => x.ObjectVersion1.Id == objectVersionId ? selector(x, x.ObjectVersion2) : selector(x, x.ObjectVersion1));
    }

    public static IQueryable<ObjectVersion> SelectByLatestDistinctGenericObject(this IQueryable<ObjectVersion> source)
    {
        return
            source
            .GroupBy(x => x.GenericObject.Id)
            .Select(x => x.OrderByDescending(y => y.Id).FirstOrDefault());
    }

    public static IQueryable<ObjectDependency> SelectByLatestDistinctGenericObject(this IQueryable<ObjectDependency> source, int objectVersionId)
    {
        return
            source
            .SelectDependentObjectVersions(objectVersionId, (x, y) => new { ObjectDependency = x, ObjectVersion = y })
            .GroupBy(x => x.ObjectVersion.GenericObject.Id)
            .Select(x => x.OrderByDescending(y => y.ObjectVersion.Id).FirstOrDefault())
            .Select(x => x.ObjectDependency);
    }
}

然后，您可以通过以下方式调用它们（我注释掉了LINQ查询的一部分，并插入了扩展方法，以便您可以看到扩展方法将替换每个查询的哪一部分）：

// Get all ObjectDependencies containing ov2
// Output:
// d1
// d2
// d3
Console.WriteLine("Get all ObjectDependencies containing ov2");
IEnumerable<ObjectDependency> ov2Dependencies =
    dependencies
    //.Where(d => d.ObjectVersion1.Id == ov2.Id || d.ObjectVersion2.Id == ov2.Id)
    .WhereContainsObjectVersion(ov2.Id)
    .OrderBy(d => d.Name);
foreach (ObjectDependency dependency in ov2Dependencies)
{
    Console.WriteLine(dependency.Name);
}

// Get all ObjectVersions dependent on ov2
// Output:
// 1
// 3
// 4
Console.WriteLine("Get all ObjectVersions dependent on ov2");
IEnumerable<ObjectVersion> ov2AllDependentObjectVersions =
    dependencies
    //.Where(d => d.ObjectVersion1.Id == ov2.Id || d.ObjectVersion2.Id == ov2.Id)
    //.Select(d => d.ObjectVersion1)
    //.Union(dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id || d.ObjectVersion2.Id == ov2.Id)
    //.Select(d => d.ObjectVersion2))
    //.Where(o => o.Id != ov2.Id)
    .SelectDependentObjectVersions(ov2.Id)
    .OrderBy(o => o.Id);
foreach (ObjectVersion ov in ov2AllDependentObjectVersions)
{
    Console.WriteLine(ov.Id);
}

// Get newest ObjectVersions dependent on ov2 with different GenericObject
// Output:
// 3
// 4
Console.WriteLine("Get newest ObjectVersions dependent on ov2 with different GenericObject");
IEnumerable<ObjectVersion> ov2NewestDependentObjectVersions =
    dependencies
    //.Where(d => d.ObjectVersion1.Id == ov2.Id || d.ObjectVersion2.Id == ov2.Id)
    //.Select(d => d.ObjectVersion1)
    //.Union(dependencies.Where(d => d.ObjectVersion1.Id == ov2.Id || d.ObjectVersion2.Id == ov2.Id)
    //.Select(d => d.ObjectVersion2))
    //.Where(o => o.Id != ov2.Id)
    //.GroupBy(o => o.GenericObject.Id)
    //.Select(g => g.OrderByDescending(o => o.Id).FirstOrDefault())
    .SelectDependentObjectVersions(ov2.Id)
    .SelectByLatestDistinctGenericObject()
    .OrderBy(o => o.Id);
foreach (ObjectVersion ov in ov2NewestDependentObjectVersions)
{
    Console.WriteLine(ov.Id);
}

// Get newest ObjectDependencies containing ov2 with different GenericObject
// Output:
// d2
// d3
Console.WriteLine("Get newest ObjectDependencies containing ov2 with different GenericObject");
IEnumerable<ObjectDependency> ov2NewestDependencies =
    dependencies
    //.Where(d => d.ObjectVersion1.Id == ov2.Id || d.ObjectVersion2.Id == ov2.Id)
    // ???
    .SelectByLatestDistinctGenericObject(ov2.Id)
    .OrderBy(d => d.Name);
foreach (ObjectDependency dependency in ov2NewestDependencies)
{
    Console.WriteLine(dependency.Name);
}

关于c# - 根据基础实体的公共(public)标识符过滤相关实体，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32377240/