IQueryable<> from stored procedure (entity framework)

Question

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().

Is this EF 4? If so, use `ExecuteStoreQuery`, and then you won't need `Materialize`. — Craig Stuntz, May 05 '10 at 12:13
No, it is EF 1, but if `ExecuteStoreQuery` resolves my problem I will consider moving to EF4. Can you share links to some special articles about that(if you have ones). — arena-ru, May 05 '10 at 12:33
I saw http://stackoverflow.com/questions/2201369/entity-framework-objectcontext-executestorequery-produces-detached-objects and it is stated that `ExecuteStoreQuery` returns `ObjectResult` collection - same as `m_Entities.SomeEntitiyStoredProcedure(1234);`. So seems that `ExecuteStoreQuery` will not resolve my problem. — arena-ru, May 05 '10 at 14:19
OK, I see what you're saying. This (`Take()`...) won't be possible, because if *you* write the SQL, the EF cannot further compose that SQL. However, if you *do* use `ExecuteStoreQuery` then you *can* use `TOP` and the like in the SQL that you write. — Craig Stuntz, May 05 '10 at 16:17
Did `ExecuteStoreQuery` return `ObjectResult` or it return `ObjectQuery`? If it returns `ObjectResult` - it is unusable for me. — arena-ru, May 05 '10 at 18:28

score 9 · Accepted Answer · answered May 05 '10 at 14:47

9

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?

answered May 05 '10 at 14:47

Damien_The_Unbeliever

234,701
27
340
448

Nice idea. Will EF return same type of entity from the view, or I have to do some more steps to achieve that? Cane you share some code snippet working with view in ef? – arena-ru May 05 '10 at 15:17
Without EF, there is no problem doing what he wants to, though. – erikkallen May 05 '10 at 17:14
Would be nice to see usage example of Database View in EF. I'm not interested doing it without EF. – arena-ru May 05 '10 at 18:33
1

Support for table-valued functions has been added in .NET 4.5 and EF5. Change your stored proc into a TVF and you can use the code from [this answer](http://stackoverflow.com/a/16811388/175157). – Alex Oct 17 '13 at 13:24

score 1 · Answer 2 · edited Jul 03 '15 at 10:51

1

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.

edited Jul 03 '15 at 10:51

Community

1
1

answered Jun 24 '15 at 16:33

mcintyre321

12,996
8
66
103

erikkallen · Answer 3 · 2010-05-05T21:27:47.143

-3

sually you can get around these issues with ToList()

var globbalyFilteredSomeEntities = m_Entities.CreateStoreCommand("exec SomeEntitiyStoredProcedure(1234)") 
                                             .Materialize<SomeEntitiy>()
                                             .ToList()  // <<-- added this.
                                             .WhateverYouWant();

Why can't you do the Skip() and Take() on the enumerable? Doing this will only download the results that are skipped or taken, the others won't be read.

Edit: The previous version was plain wrong in many aspects.

edited May 05 '10 at 21:27

answered May 05 '10 at 11:44

erikkallen

33,800
13
85
120

It is not what I'm looking for. When I put `ToList()` query executes, but it is too expensive to execute query without `Skip()`, `Take()`. – arena-ru May 05 '10 at 12:29
The AsEnumerable() solution should do the trick in this case (after edit) – erikkallen May 05 '10 at 17:16

IQueryable<> from stored procedure (entity framework)

3 Answers3