在运行时。net core应用程序中停止执行的异常:System.Data.SqlClient。SqlException:“关键字“GROUP”附近的语法错误。

Why is the C# compiler giving a runtime exception about this raw query not executing properly when it executes correctly in SQL Server Management Studio?

为什么c#编译器在SQL Server Management Studio中正确执行时，会对这个原始查询给出运行时异常?

I'm getting this exception stopping the app at runtime in the C# code below this query:

在这个查询下面的c#代码中，我将在运行时停止应用程序:

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

System.Data.SqlClient。SqlException:“关键字“GROUP”附近的语法错误。

Goal: Return 3 rows of data as JSON to the ASP.NET controller (controller is already returning hardcoded dummy data, but needs to be able to return this data as JSON eventually).

目标:将3行数据作为JSON返回到ASP。NET controller (controller已经返回硬编码的假数据，但最终需要能够以JSON形式返回数据)。

Original SQL Server procedure (executes without errors in SQL Server Management Studio, see very bottom for data returned by this query):

原始的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;

Equivalent C# / .NET Core code to query the database for this information

等效c# / .NET核心代码，用于查询数据库以获取此信息

//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;
    }
}

This is what is returned by the SQL query in SQL Server Management Studio:

这是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

1 个解决方案

#1

The biggest issue I see is using reserved words as column names. This is a bad practice as it causes problems...not to mention that the reserved are usually horrible names anyway. You need to use non-ambiguous names. A column named date is a poor name, what is it? TestDate? CreatedDate, DeletedDate?

我看到的最大问题是使用保留词作为列名。这是一种不好的做法，因为它会导致问题……更不用说那些保留的名字通常都很可怕。您需要使用明确的名称。一个名为date的列是一个糟糕的名字，它是什么?TestDate吗?CreatedDate,DeletedDate ?

For getting the top 3 values you can use TOP. No need to return the rows to your application just to throw them away. Something like this should be close.

要得到前三个值，可以使用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];