IQueryable<> from stored procedure (entity framework)
- by mmcteam
I want to get IQueryable<> result when executing stored procedure.
Here is peace 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 result in such way
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().