如何在EF6中用Count和Join实现指定SQL并在WPF DataGrid展示结果

要实现你需要的SQL查询并把结果展示在WPF DataGrid里，我们可以通过强类型DTO承载结果 + LINQ to Entities/原生SQL查询 + DataGrid绑定这几步来完成，下面是具体实现方案：

1. 创建DTO类 #

因为我们需要返回Clients的所有属性加上符合条件的Optics数量，直接用实体类无法承载额外的统计字段，所以先定义一个DTO：

public class ClientWithOpticsCount
{
    // 复制Clients类的所有属性
    public long Id { get; set; }
    public string Name { get; set; }
    public string Phone { get; set; }
    public DateTime? DateOfBirth { get; set; }
    // 这里补充Clients类的其他属性...

    // 新增统计字段
    public int OpticsCount { get; set; }
}

2. 实现查询（两种方式可选） #

方式一：LINQ to Entities 查询 #

这种方式更符合EF的ORM风格，不需要写原生SQL：

// 你的变量值
int targetRSph = 12;

using (var dbContext = new YourDbContext()) // 替换成你的DbContext类名
{
    // 使用GroupJoin实现左连接+过滤+分组统计
    var clientCounts = dbContext.Clients
        .GroupJoin(
            // 先过滤出r_sph符合条件的Optics记录
            dbContext.Optics.Where(o => o.r_sph == targetRSph),
            client => client.id,
            optic => optic.client_id,
            (client, matchingOptics) => new ClientWithOpticsCount
            {
                Id = client.id,
                Name = client.name,
                Phone = client.phone,
                DateOfBirth = client.date_of_birth,
                // 映射其他Clients属性...
                OpticsCount = matchingOptics.Count()
            })
        .ToList();

    // 绑定到DataGrid
    yourDataGrid.ItemsSource = clientCounts;
}

方式二：执行原生SQL查询 #

如果更习惯写原生SQL，可以直接执行你的原始查询，映射到DTO：

int targetRSph = 12;

using (var dbContext = new YourDbContext())
{
    string sqlQuery = @"
        SELECT c.*, COUNT(o.id) AS OpticsCount
        FROM clients c
        LEFT JOIN optics o ON o.client_id = c.id
        WHERE o.r_sph = @TargetRSph
        GROUP BY c.id";

    // 执行SQL并映射到DTO
    var clientCounts = dbContext.Database.SqlQuery<ClientWithOpticsCount>(
        sqlQuery,
        new System.Data.SqlClient.SqlParameter("@TargetRSph", targetRSph)
    ).ToList();

    yourDataGrid.ItemsSource = clientCounts;
}

3. WPF DataGrid 绑定配置 #

在XAML中配置DataGrid的列（如果关闭AutoGenerateColumns的话），确保绑定字段和DTO属性对应：

<DataGrid x:Name="yourDataGrid" AutoGenerateColumns="False" Margin="10">
    <DataGrid.Columns>
        <DataGridTextColumn Header="客户ID" Binding="{Binding Id}" />
        <DataGridTextColumn Header="客户姓名" Binding="{Binding Name}" />
        <DataGridTextColumn Header="联系电话" Binding="{Binding Phone}" />
        <DataGridTextColumn Header="出生日期" Binding="{Binding DateOfBirth, StringFormat=d}" />
        <DataGridTextColumn Header="符合条件的镜片数量" Binding="{Binding OpticsCount}" />
        <!-- 补充其他列 -->
    </DataGrid.Columns>
</DataGrid>

注意事项 #

• 如果你希望保留所有客户（即使没有符合条件的Optics记录），需要把原SQL的WHERE条件移到JOIN子句中（LEFT JOIN optics o ON o.client_id = c.id AND o.r_sph = 12），对应的LINQ查询也要调整过滤位置，这样统计数会显示0而不是过滤掉该客户。
• 务必用using包裹DbContext，确保数据库连接及时释放。

内容的提问来源于stack exchange，提问作者W.M.