c# - 在运行时 .NET 核心应用程序中停止执行的异常 : System. Data.SqlClient.SqlException : 'Incorrect syntax near the keyword ' GROUP'. '

Question

当它在 SQL Server Management Studio 中正确执行时，为什么 C# 编译器会给出关于此原始查询未正确执行的运行时异常？

我在运行时在此查询下方的 C# 代码中遇到了停止应用程序的异常:

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'GROUP'.'

目标:将 3 行数据作为 JSON 返回给 ASP.NET Controller ( Controller 已经返回硬编码的虚拟数据，但最终需要能够将此数据作为 JSON 返回)。

原始 SQL Server 过程(在 SQL Server Management Studio 中执行时没有错误，请参阅此查询返回的数据的最底部):

Select 
    sum(jobs) as JobCount,avg(price) as avgPrice,
    shift, 
    Convert(varchar(10), date, 120) as date 
from  
    [database_test].[dbo].station
where  
    grading > 0 
    and date = '04/21/2017'
    and shift in ('1', '2', '3')
    and stationid in (select stationid 
                      from [database_test].[dbo].WSConfig 
                      where genmodel = 'C:\stations\Zone.mdb') 
group by 
    date, shift
order by 
    date;

用于查询数据库以获取此信息的等效 C#/.NET Core 代码

//Repository class with injected DbContext (Entity Framework Core way of doing it)
public class DateRepository {  
    public database_testContext storeDB = null;

    public DateRepository(database_testContext storeDB)
    {
        this.storeDB = storeDB;
    }

    public void DateRepository(database_testContext dbContext)
    {
        storeDB = dbContext;
    }

    // This method is supposed to return 3 rows of data off of a raw SQL query
    public IEnumerable<station> ReturnData()
    {
         // DB querying variable
         var context = storeDB;

         var testQuery = context.station.FromSql(@"Select sum(jobs) as JobCount,avg(price) as avgPrice,
                 shift, Convert(varchar(10),date,120) as date from  [database_test].[dbo].station
                 where  grading > 0 
                            and date = '04/21/2017'
                           and shift in ('1','2','3')
                           and stationid in 
                            (select stationid from [database_test].[dbo].WSConfig where genmodel = 'C:\stations\Zone.mdb') 
                            GROUP BY date,shift
                           order by date;").ToList().Take(3)


        return testQuery;
    }
}

这是 SQL Server Management Studio 中的 SQL 查询返回的内容:

JobCount    avgPrice    shift   date 
------------------------------------------
4420        251.25      1       2017-04-21
3253        268.69      2       2017-04-21
4634        256.46      3       2017-04-21

Answer 1

我看到的最大问题是使用保留字作为列名。这是一个不好的做法，因为它会导致问题......更不用说保留的名字通常都是可怕的名字。您需要使用明确的名称。名为 date 的列是一个糟糕的名字，它是什么？测试日期？创建日期，删除日期？

要获得前 3 个值，您可以使用 TOP。无需将行返回到您的应用程序就可以将它们丢弃。像这样的事情应该很接近。

Select top 3
    sum(jobs) as JobCount
    , avg(price) as avgPrice
    , [shift]
    , Convert(varchar(10), [date], 120) as [date]
from  [database_test].[dbo].station
where  grading > 0 
    and [date] = '04/21/2017'
    and [shift] in ('1','2','3')
    and stationid in 
    (
        select stationid 
        from [database_test].[dbo].WSConfig 
        where genmodel = 'C:\stations\Zone.mdb'
    ) 
GROUP BY [date]
    , [shift]
order by [date];