来自存储过程的IQueryable (实体框架)

时间:2021-09-03 21:48:01

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

我想在执行存储过程时获得IQueryable <>结果。

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:

将存储过程添加到m_Entities,但它返回IEnumerable <>并立即执行sp:

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

使用EFExtensions库实现查询,但它是IEnumerable <>。如果我使用AsQueryable()和OrderBy(),Skip(),Take()并在ToList()之后执行该查询 - 我得到DataReader打开的异常,我需要先关闭它(不能粘贴错误 - 这是俄语)。

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

也只是跳过.AsQueryable()没有用 - 同样的例外。当我执行ToList()查询时,如果没有Skip(),Take()执行查询太昂贵了。

3 个解决方案

#1


8  

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.

您无法执行您要执行的操作,原因与您无法将存储过程放在SELECT查询的FROM子句中相同 - SQL不是为支持此类操作而构建的。

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

你能把你想要的逻辑放到视图而不是存储过程吗?

#2


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.

您可以使用我创建的名为LinqToAnything的项目,该项目允许您使用不可查询的数据访问方法并将其转换为IQueryable。

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

我在这里有关于如何使用它的博客文章。

#3


-1  

sually you can get around these issues with ToList()

sually你可以解决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.

为什么你不能对可枚举的Skip()和Take()做?执行此操作只会下载跳过或拍摄的结果,其他结果将不会被读取。

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

编辑:以前的版本在很多方面都是完全错误的。

#1


8  

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.

您无法执行您要执行的操作,原因与您无法将存储过程放在SELECT查询的FROM子句中相同 - SQL不是为支持此类操作而构建的。

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

你能把你想要的逻辑放到视图而不是存储过程吗?

#2


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.

您可以使用我创建的名为LinqToAnything的项目,该项目允许您使用不可查询的数据访问方法并将其转换为IQueryable。

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

我在这里有关于如何使用它的博客文章。

#3


-1  

sually you can get around these issues with ToList()

sually你可以解决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.

为什么你不能对可枚举的Skip()和Take()做?执行此操作只会下载跳过或拍摄的结果,其他结果将不会被读取。

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

编辑:以前的版本在很多方面都是完全错误的。