Entity Framework 6.0 Raw Sql to View Model

c# entity-framework entity-framework-6 sql-server


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!

4/20/2016 9:23:18 AM

Popular Answer

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 = @"
        p.Name AS PuzzleName, 
    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);
    .SqlQuery<UserProgressViewModel>(sql, new SqlParameter("@userid", userid))

Now, you will see detailed db responses.

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

4/20/2016 10:11:53 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow