Entity Framework Error - The query syntax is not valid

Question

I am trying to run a simple custom query that is returned into a custom object, however, I keep running into an error and I have no idea what is wrong. The query runs just fine in SQL Server Management Studio.

public class MyClass
{
    public Nullable<int> EligibleCredits { get; set;}
    public Nullable<int> ReadyForSubmissionCredits { get; set; }
    public Nullable<int> RedeemedCredits { get; set; }
}

Main Method:

var query = @"SELECT 
    (select count(*) from MyTable where UserId = @userId and [Status] = 0 and Year(EarnDate) >= (select Year(getdate()) -@years)) as EligibleCredits,
    (select count(*) from MyTable where UserId = @userId and [Status] = 1 and Year(EarnDate) >= (select Year(getdate()) -@years)) as ReadyForSubmissionCredits,
    (select count(*) from MyTable where UserId = @userId and [Status] = 2 and Year(EarnDate) >= (select Year(getdate()) -@years)) as RedeemedCredits";

var objectQuery = new ObjectQuery<MyClass>(query, ((IObjectContextAdapter)this).ObjectContext);
objectQuery.Parameters.Add(new ObjectParameter("userId", userId));
objectQuery.Parameters.Add(new ObjectParameter("years", years));

return objectQuery.FirstOrDefault();

The error that I get is:

The query syntax is not valid. Near term '*', line 2, column 39.

This query runs fine in SQL Server Management Studio.

Also, according to this article, I'm doing it correctly: https://msdn.microsoft.com/en-us/library/bb738521(v=vs.100).aspx

First, why aren't you using EF rather than a raw SQL query? Second, your raw SQL query could be improved with a structure like select count(UserId), CASE WHEN [Status] = 0 THEN 'EligibleCredits' WHEN [Status] = 1 THEN 'ReadyForSubmissionCredits' WHEN [Status] = 2 THEN 'RedeemedCredits' END AS CreditType from MyTable where UserId = @userId and [Status] IN (0,1,2) and Year(EarnDate) >= (select Year(getdate()) -@years)) GROUP BY [Status]. — devlin carnate
– devlin carnate, Commented Jan 31, 2018 at 20:26
Apparently Entity SQL query is not the same as SQL query. The exception is thrown by EF SQL query parser. Rather than obsolete ObjectContext APIs, use the EF6 preferred context.Database.SqlQuery<MyClass>(...) - look at the help how to specify parameters. — Ivan Stoev
– Ivan Stoev, Commented Jan 31, 2018 at 20:37

Diego · Accepted Answer · 2018-02-01 23:09:44Z

2

This is a bug in the EntityFramework query parser. All I can do, is to provide you with a workaround (which BTW, may even run faster):

var query = @"SELECT 
    ISNULL(SUM(CASE WHEN [Status] = 0 THEN 1 ELSE 0 END),0) as EligibleCredits,
    ISNULL(SUM(CASE WHEN [Status] = 1 THEN 1 ELSE 0 END),0) as ReadyForSubmissionCredits,
    ISNULL(SUM(CASE WHEN [Status] = 2 THEN 1 ELSE 0 END),0) as RedeemedCredits
    FROM MyTable 
       WHERE UserId = @userId 
       AND Year(EarnDate) >= Year(getdate()) - @years";

var objectQuery = new ObjectQuery<MyClass>(query, ((IObjectContextAdapter)this).ObjectContext);
objectQuery.Parameters.Add(new ObjectParameter("userId", userId));
objectQuery.Parameters.Add(new ObjectParameter("years", years));

return objectQuery.FirstOrDefault();

Note that if this is an instance of DbContext, you can simplify your code as follows:

var query = @"SELECT 
    ISNULL(SUM(CASE WHEN [Status] = 0 THEN 1 ELSE 0 END),0) as EligibleCredits,
    ISNULL(SUM(CASE WHEN [Status] = 1 THEN 1 ELSE 0 END),0) as ReadyForSubmissionCredits,
    ISNULL(SUM(CASE WHEN [Status] = 2 THEN 1 ELSE 0 END),0) as RedeemedCredits
    FROM MyTable 
       WHERE UserId = @p0 -- <== NOTE THE PARAM NAME HERE AND BELOW
       AND Year(EarnDate) >= Year(getdate()) - @p1";
return new this.Database.SqlQuery<MyClass>(query, userId, years).FirstOrDefault();

This last approach also works with your original query.

edited Feb 1, 2018 at 23:09

answered Feb 1, 2018 at 2:09

Diego

18.5k5 gold badges67 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bagzli Over a year ago

Essentially the bug is the fact that it cannot fill out the null object?

Diego Over a year ago

@Bojan No, it's parsing the SQL statement. EF needs to parse the query so it knows how to assign the output to objects in .NET. Normally, objectQuery.ToTraceString() would output the generated SQL statement, but in this case, it throws the same exception, even before execution takes place.

Neelima Ediga · Accepted Answer · 2020-12-31 07:34:34Z

0

ExecuteStoreQuery worked for me, for the same error. Sample below:

IList<MyClass> contactList =
            (((IObjectContextAdapter) model).ObjectContext.ExecuteStoreQuery<MyClass>(strString))
            .ToList<MyClass>();

answered Dec 31, 2020 at 7:34

Neelima Ediga

3561 gold badge5 silver badges19 bronze badges

Collectives™ on Stack Overflow

Entity Framework Error - The query syntax is not valid

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related