3

Firstly i am using EF v6.0. I have this function below;

public List<UserProgressViewModel> GetUserProgresses(int userId)
{
   return Context.Database.SqlQuery<UserProgressViewModel>(
   "SELECT Puzzles.Name AS PuzzleName, UserProgresses.Minute, " +
   "UserProgresses.Session FROM UserProgresses " +
   "INNER JOIN Puzzles ON Puzzles.Id = UserProgresses.PuzzleId " +
   "WHERE UserProgresses.UserId = @userid", new SqlParameter("@userid", userId)).ToList();
}

And my UserProgressViewModel class is below;

public class UserProgressViewModel
{
    public string PuzzleName { get; set; }
    public int Minute { get; set; }
    public int Session { get; set; }
}

(This class isn't a DbSet, it's only a view model.)
When i run the application (it's a WebAPI project) i get this error:
"Incorrect syntax near 'UserProgresses'."

I tried running this query on Mssql and it works fine. Results came just as i exptected.

I searched if EF 6.0 doesn't support raw sql query like this one, but it does support.
(According to this page: http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application#rawsql )

What is this thing that i can't see?
Thank you, have a great one!

Onur Keskin
  • 140
  • 10
  • `Incorrect syntax near 'UserProgresses'` is generated by SQL Server, make sure the query is syntactically valid. It's unlikely to cause a problem but I'd be writing `Puzzles.Name` as `Puzzles.[Name]` and `UserProgresses.Minute` as `UserProgresses.[Minute]` (ditto for `Session`) at a minimum. https://technet.microsoft.com/en-us/library/ms189822(v=sql.110).aspx – ta.speot.is Dec 29 '17 at 11:13
  • Voting to close due to this being a transient problem with Visual Studio, as OP mentions in the comments of the answer @tenbits provided. – Bradley Uffner Dec 29 '17 at 13:07

2 Answers2

1

Actually your code should work and it looks ok. EF supports any raw sql statements your mssql version supports.

Try to refactor your code and add db logging:

var sql = @"
    SELECT 
        p.Name AS PuzzleName, 
        up.Minute, 
        up.Session 
    FROM UserProgresses as up
    INNER JOIN Puzzles as p ON p.Id = up.PuzzleId 
    WHERE up.UserId = @userid
";
// Instead of console, you may want to use your default app logging.
ctx.Database.Log += (message) => Console.WriteLine(message);
ctx
    .Database
    .SqlQuery<UserProgressViewModel>(sql, new SqlParameter("@userid", userid))
    .ToList();

Now, you will see detailed db responses.

Is not quite a solution, just wanted to use proper highlighting

tenbits
  • 7,568
  • 5
  • 34
  • 53
  • Thank you for your response, it was a good idea to log. As you said my code should work, and it works actually. The cause of the problem was visual studio itself. Somehow it didn't recognize that piece of code. Then it started to show me "source file is different" error. After a bit of research i found this which helped me:http://stackoverflow.com/questions/3087390/the-source-file-is-different-from-when-the-module-was-built – Onur Keskin Apr 20 '16 at 11:32
-1

Beware you have to use properties instead of normal public class fields! SqlQuery mapping works only with class properties and throws no error so it is hard to find.

lukyer
  • 7,595
  • 3
  • 37
  • 31