c# - 在实体框架中选择不同的行

问题描述

我正在尝试使用实体框架查询 SQL Server 视图，并且仅返回基于多列不同的行。

我已经尝试了基于这个答案的解决方案（GroupBy然后Select(g => g.FirstOrDefault())，但我仍然得到重复的行。

表结构（这是真实数据库中相当复杂的视图，但最终输出的结构与本示例相似）：

CREATE TABLE Example (
    ID_A BIGINT,
    ID_B BIGINT,
    ID_C BIGINT,

    Type_A NVARCHAR(50),
    Type_B NVARCHAR(50),

    ID_Z BIGINT,

    Foo NVARCHAR(200),
    Bar NVARCHAR(200)
)

示例数据：

INSERT INTO Example (ID_A, ID_B, ID_C, Type_A, Type_B,  ID_Z,  Foo, Bar)
VALUES (1, 1, 1, 'TypeA1', 'TypeB1',  1,  'foo1', 'bar1'), -- This row and the next one represent the same main record (1) joined to different "Z" records (1 and 2)
       (1, 1, 1, 'TypeA1', 'TypeB1',  2,  'foo1', 'bar1'),
       (2, 1, 2, 'TypeA2', 'TypeA2',  1,  'foo2', 'bar2'), -- This row and the next two represent the same main record (2) joined to different "Z" records (1, 2 and 3)
       (2, 1, 2, 'TypeA2', 'TypeA2',  2,  'foo2', 'bar2'),
       (2, 1, 2, 'TypeA2', 'TypeA2',  3,  'foo2', 'bar2')

实体类：

public class ExampleEntity
{
    [Key]
    public long ID_A { get; set; }
    public long ID_B { get; set; }
    public long ID_C { get; set; }

    public string Type_A { get; set; }
    public string Type_B { get; set; }

    public long? ID_Z { get; set; }

    public string Foo { get; set; }
    public string Bar { get; set; }

实体配置：

public class ExampleEntityConfiguration : EntityTypeConfiguration<ExampleEntity>
{
    public ExampleEntityConfiguration()
    {
        // Properties
        this.Property(t => t.ID_A)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        // Table & Column Mappings
        this.ToTable("Example");
        this.Property(t => t.ID_A).HasColumnName("ID_A");
        this.Property(t => t.ID_B).HasColumnName("ID_B");
        this.Property(t => t.ID_C).HasColumnName("ID_C");

        this.Property(t => t.Type_A).HasColumnName("Type_A");
        this.Property(t => t.Type_B).HasColumnName("Type_B");

        this.Property(t => t.ID_Z).HasColumnName("ID_Z");

        this.Property(t => t.Foo).HasColumnName("Foo");
        this.Property(t => t.Bar).HasColumnName("Bar");
    }
}

ID_A, ID_B, ID_C,Type_A并Type_B标识“主”实体并ID_Z标识连接的“Z”实体。Foo并且Bar是需要包含在最终结果中的非唯一数据列。

对于主要 ID/Type 值的每个组合，可以有多个ID_Z值。我需要按ID_Z值过滤，然后返回不同的主要实体值（基于 ID/Type 值）。

我尝试了如下查询：

// The `ID_Z` values to filter on
var zIDs = new List<long> { 1, 2 };

var result = context.Set<ExampleEntity>()
       .Where(e => zIDs.Contains(e.ID_Z))
       .GroupBy(e => new { e.ID_A, e.ID_B, e.ID_C, e.Type_A, e.Type_B })
       .Select(g => g.FirstOrDefault())
       .ToList();

这导致了这个 SQL：

SELECT 
    [Limit1].[ID_A] AS [ID_A], 
    [Limit1].[ID_B] AS [ID_B], 
    [Limit1].[ID_C] AS [ID_C], 
    [Limit1].[Type_A] AS [Type_A], 
    [Limit1].[Type_B] AS [Type_B], 
    [Limit1].[ID_Z] AS [ID_Z], 
    [Limit1].[Foo] AS [Foo], 
    [Limit1].[Bar] AS [Bar]
FROM   (
    SELECT [Extent1].[ID_A] AS [ID_A], [Extent1].[ID_B] AS [ID_B], [Extent1].[ID_C] AS [ID_C], [Extent1].[Type_A] AS [Type_A], [Extent1].[Type_B] AS [Type_B]
    FROM [dbo].[Example] AS [Extent1] WITH (NOLOCK)
    WHERE 
        ([Extent1].[ID_Z] IN (cast(1 as bigint), cast(2 as bigint))) AND ([Extent1].[ID_Z] IS NOT NULL)
) AS [Filter1]
OUTER APPLY  (
    SELECT TOP (1) 
        [Extent2].[ID_A] AS [ID_A], 
        [Extent2].[ID_B] AS [ID_B], 
        [Extent2].[ID_C] AS [ID_C], 
        [Extent2].[Type_A] AS [Type_A], 
        [Extent2].[Type_B] AS [Type_B], 
        [Extent2].[ID_Z] AS [ID_Z], 
        [Extent2].[Foo] AS [Foo], 
        [Extent2].[Bar] AS [Bar]
     FROM [dbo].[Example] AS [Extent2] WITH (NOLOCK)
     WHERE 
        ([Extent2].[ID_Z] IN (cast(1 as bigint), cast(2 as bigint))) AND ([Extent2].[ID_Z] IS NOT NULL) AND 
        ([Filter1].[ID_A] = [Extent2].[ID_A]) AND 
        ([Filter1].[ID_B] = [Extent2].[ID_B]) AND 
        ([Filter1].[ID_C] = [Extent2].[ID_C]) AND 
        (([Filter1].[Type_A] = [Extent2].[Type_A]) OR (([Filter1].[Type_A] IS NULL) AND ([Extent2].[Type_A] IS NULL))) AND 
        (([Filter1].[Type_B] = [Extent2].[Type_B]) OR (([Filter1].[Type_B] IS NULL) AND ([Extent2].[Type_B] IS NULL))) 
) AS [Limit1]

但这似乎返回了与Z_ID过滤器匹配的所有行（导致重复的“主”值），而不是只返回每组“主”ID/类型值的第一行。

如果我ToList在之后直接实现（）查询GroupBy，我似乎得到了正确的分组；但我想在数据库中运行这一切，并避免使用 LINQ to Objects 查询。

如何创建此查询？

标签： c#sql-serverentity-framework

c# - 在实体框架中选择不同的行

问题描述

解决方案

推荐阅读