Can't get data out using SQL query in Entity Framework

Question

I'm trying to use the following SQL query to get the first two columns of data out of my database:

SELECT  Id, DomainUrl
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY Id ) AS RowNum, Id, DomainUrl
          FROM      SiteDatas
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum <= 10
ORDER BY RowNum

I'm using entity framework and passing in parameters for the rows so that it can later be used as a scroll on load function via ajax. However I am getting an error when I get to the line:

var stores = db.SiteDatas.SqlQuery(SQL, parameters).ToList();

It's an entity command execution exception that is stating that the data reader is incompatible with the specified model (my db) member of the type, 'Robots' (which is the next column after the one I am calling), does not have a corresponding column in the data reader with the same name.

How would I go about getting just the first two columns out in a way that can be serialized as json?

Does SiteDatas contain a Robots column? Because the query doesn't — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 16, 2013 at 10:03
You can add NULL AS Robots in your select query to return it always as null? — Nenad Zivkovic
– Nenad Zivkovic, Commented Jul 16, 2013 at 10:15
There are quite a few other columns, is NULLing them all really the best way to go about it? — Kayra
– Kayra, Commented Jul 16, 2013 at 10:16
Maybe you can create a new class based on this query that have only the needed columns? — Nenad Zivkovic
– Nenad Zivkovic, Commented Jul 16, 2013 at 10:20

Panagiotis Kanavos · Accepted Answer · 2013-07-16 10:26:04Z

7

The DbSet.SqlQuery and Database.SqlQuery commands both return entity instances. Your sql query has to return the same columns as your entity. Most probably, your SiteData class contains a Robots column that doesn't exist in your SQL query.

You can still use Database.SqlQuery< T > to return your data, provided you specify the return type. The return type doesn't have to be an entity type, just have the same column names as your result set.

Assuming db is a DbContext instance, you can write:

public class MyResults
{
   public int ID{get;set;}
   public string DomainUrl {get;set;}
}

...

var stores = db.Database.SqlQuery<MyResults>(SQL, parameters).ToList();

edited Jul 16, 2013 at 10:26

answered Jul 16, 2013 at 10:04

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

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

5 Comments

Kayra Over a year ago

It does, but I don't want it as a result, is there a way around this?

Panagiotis Kanavos Over a year ago

Yes, Database.SqlQuery can return any type whose properties match the result columns

Kayra Over a year ago

I've already tried this and get the error: The non-generic method 'System.Data.Entity.DbSet<Bescoured.DomainModels.SiteData>.SqlQuery(strin, params object[]) cannot be used with type arguments.

Panagiotis Kanavos Over a year ago

Wrong method! Try Database.SqlQuery<YourCustomClass>, not DbSet.SqlQuery

Anshul Nigam Over a year ago

another important thing is it was not working if we dont put {get;set;} in model, faced this problem thought to share it so that other won't.

Collectives™ on Stack Overflow

Can't get data out using SQL query in Entity Framework

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related