Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

IQueryable<> from stored procedure (entity framework)

I want to get IQueryable<> result when executing stored procedure.

Here is piece of code that works fine:

IQueryable<SomeEntitiy> someEntities;  
var globbalyFilteredSomeEntities = 
  from se in m_Entities.SomeEntitiy
    where
      se.GlobalFilter == 1234 
  select se;

I can use this to apply global filter, and later use the result in something like:

result = globbalyFilteredSomeEntities
  .OrderByDescending(se => se.CreationDate)
  .Skip(500)
  .Take(10);

What I want to do - use some stored procedures in global filter.
I tried:

Add stored procedure to m_Entities, but it returns IEnumerable<> and executes sp immediately:

var globbalyFilteredSomeEntities = 
  from se in m_Entities.SomeEntitiyStoredProcedure(1234);

Materialize query using EFExtensions library, but it is IEnumerable<>.
If I use AsQueryable() and OrderBy(), Skip(), Take()
and after that ToList() to execute that query -
I get exception that DataReader is open and I need to close it first(can't paste error - it is in russian).

var globbalyFilteredSomeEntities = 
  m_Entities.CreateStoreCommand("exec SomeEntitiyStoredProcedure(1234)")
            .Materialize<SomeEntitiy>();
            //.AsQueryable()
            //.OrderByDescending(se => se.CreationDate)
            //.Skip(500)
            //.Take(10)
            //.ToList();   

Also just skipping .AsQueryable() is not helpful - same exception.
When I put ToList() query executes,
but it is too expensive to execute query without Skip(), Take().

like image 486
arena-ru Avatar asked May 05 '10 10:05

arena-ru


People also ask

Can we use stored procedure in Entity Framework?

You can use stored procedures either to get the data or to add/update/delete the records for one or multiple database tables. EF API creates a function instead of an entity in EDM for each stored procedure and User-Defined Function (UDF) in the target database.

What is the difference between returning IQueryable T vs IEnumerable T >?

Both IEnumerable and IQueryable are forward collection. Querying data from a database, IEnumerable execute a select query on the server side, load data in-memory on a client-side and then filter data. Querying data from a database, IQueryable execute the select query on the server side with all filters.


2 Answers

You can't do what you're trying to do, for the same reason that you can't put a stored procedure in a FROM clause of a SELECT query - SQL isn't built to support this kind of operation.

Could you put the logic you want into a view instead of a stored procedure?

like image 156
Damien_The_Unbeliever Avatar answered Sep 23 '22 02:09

Damien_The_Unbeliever


You can use a project I created called LinqToAnything which lets you take a non-queryable data access method and turn it into an IQueryable.

I've got a blog post here on how to use it.

like image 22
mcintyre321 Avatar answered Sep 27 '22 02:09

mcintyre321