c# - 使用 EF 查询映射 sql 查询不起作用

Question

我正在尝试使用 EF6 执行查询。我的 SQL 格式查询如下:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT 
      [SUBJECT],COUNT(SUBJECT)

  FROM [PGC].[dbo].[QC] group by [SUBJECT]

效果很好。

主题是字符串类型。

但是我将查询转换为 EF，如您在此处看到的:

 listDataSource =
                db.QCs.Where(j => j.SUBJECT != null).ToList().GroupBy(i => new {i.SUBJECT}).Select(m => new chart()
                    {
                        date = m.Key.SUBJECT,
                        Count = m.Count(i=>i.SUBJECT).ToString()
                    }).ToList();

m.Count(i=>i.SUBJECT) 返回错误:无法将表达式类型字符串转换为返回类型 bool

最诚挚的问候

Answer 1

扩展 Yuriy 的答案(这是完全可以接受的)，并进行了一些修复，主要是为了性能:

listDataSource = db.QCs
    .Where(j => j.SUBJECT != null)
    .GroupBy(i => i.SUBJECT) //no need to create an anonymous type, you don't use it afterwards
    .Select(group => new { SUBJECT = group.Key, Count = group.Count()}) //counting in SQL is faster
    .AsEnumerable() //materialization, all the heavy lifting is performed by SQL until now
    .Select(value => //seems redundant, but the previous selection just translates into an SQL select statement with SUBJECT and Count columns
        new chart
        {
            date = value.SUBJECT,
            Count = value.Count.ToString()
        })
    .ToList();