Exception stopping execution in a .NET core application at runtime: System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'GROUP'.'

Question

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?

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

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword '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).

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

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

//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:

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

Just tried it in the testQuery, no difference, still getting the same exception message. — user02103012, Aug 28 '17 at 20:21
Have you logged the query being executed (e.g. using SQL Profiler or perhaps attaching a logger to the `DbContext`)? — wablab, Aug 28 '17 at 21:19
I suggest reading this page: http://www.learnentityframeworkcore.com/raw-sql I'm not an expert, but this is where I would start. — cddt, Aug 28 '17 at 21:26
Please show us the statements sent to the SQL Server when using entity framework core. You can do it with the profiler. I've had a similar issue with ORDER BY when using skip/take in some cases. That's a bug in 1.1.2 and fixed in 2.0. Maybe your issue is a similar issue. — user743414, Aug 29 '17 at 08:32
@cddt I followed Sean's edits and now I'm getting a "The required column was not present..." error. The original error is resolved however. This is just EF Core forcing me to return every property from the Entity when I don't need it. — user02103012, Aug 29 '17 at 14:58

score 1 · Accepted Answer · answered Aug 28 '17 at 20:13

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?

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.

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];

answered Aug 28 '17 at 20:13

Sean Lange

33,028
3
25
40

I agree the column names are poorly named, but the database is not created by me, I'm just querying it with my service and returning it as a JSON API endpoint. – user02103012 Aug 28 '17 at 20:23
Doesn't matter who created it, the problem is still the same. You have to wrap reserved words like that in square brackets. Did you try with the adjustments I suggested? Not sure it is the entire problem but certainly is a part of it. – Sean Lange Aug 28 '17 at 20:26
Tried it. Getting a new error here: `'The required column 'DeliveryDataID' was not present in the results of a 'FromSql' operation.'` This is probably EF-related, it's mandating me to return every property from the `station` Entity when I don't need to. – user02103012 Aug 29 '17 at 14:54
Okay, found a work-around to the required columns error: https://stackoverflow.com/questions/34745822/how-can-i-call-a-sql-stored-procedure-using-entityframework-7-and-asp-net-5/34764698#34764698 Query now returns properly, thanks @Sean Lange – user02103012 Aug 29 '17 at 17:04

Exception stopping execution in a .NET core application at runtime: System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'GROUP'.'

1 Answers1