CODEWITHSUNDEEP
c#sql-serverentity-framework
Alan Amorim
Alan Amorim

Reputation: 33

Limiting results of raw SQL Queries

I'm trying limit the result returned by a raw SQL query using Entity Framework.
The problem is that the software does that at software level not at database level.

ctx.Database.SqlQuery<ViewModelQuery>(sql)
            .Skip(totalPage * (page - 1))
            .Take(totalPage)
            .ToList();

How can I solve this in a way that the command sent to database includes offset and fetch?

Upvotes: 1

Views: 1079

Answers (2)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

It is the only way to solve it, changing the sql query or provide Linq to Entities query. Because Database.SqlQuery already start to return records as IEnumerable. (It fetched by SQL Server already) It's form is not IQueryable. So, you have two options to perform it;

  1. Changing the Sql query by taking and skipping form like this;
SELECT c1,c2 from where table
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
  1. Or you should provide the query as Linq to Entities to keep IQueryable form and perform Skip - Take .

Upvotes: 2

Harald Coppoolse
Harald Coppoolse

Reputation: 30474

I assume ctx is an object of a class derived from DbContext.

Apparently you chose not to use LINQ to write your query, but you chose to use your own SQL command. Your variable sql is a string which contains the commands in your sql.

Then why not add the offset and fetch in this string:

 Select ....
 From ...
 ...
 OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;

It would work, but I think you should reconsider you decision to use sql statements once you decided to use entity framework. Your DbSet classes represent your tables, and your DbContext class knows the relations between your table. It is meant to hide that internally SQL is used to communicate with your database.

Users of your DbContext shouldn't have to know what methods are used to save the data. It could by a Database using SQl, or MySql, it could be Mongo, or even a spreadsheet file. This is hidden for users of your DbContext, thus allowing you to change database without having to change the users.

By using SQL in your interface you are violating this information hiding. Why don't you use Linq to query your page of ViewModelQuery items?

Upvotes: 1

Related Questions