具有存储过程和IQueryable的LINQ(L2E) [英] LINQ (L2E) with stored procedure and IQueryable

问题描述

考虑正常的Linq表达式将如下所示:
(这只是一个使事情更容易理解的示例)

Consider a normal Linq expression will be something like this:
(This is just a sample to make things more understandable)

 IQueryable<Person> personQuery= (from ppl in PersonContext  
                      select ppl).ASQueryable();


List<Person>personList = personQuery.where(x => x.age==13).ToList();

因此，如果我决定将linq查询的第一部分放入存储过程中，
事情会变成这样.

So if I decided to put the 1st part of the linq query inside a stored procedure,
things will work out something like this.

 IQueryable<Person> personQuery= PersonContext.sp_RetrievePerson().ASQueryable();

 List<Person> personList = personQuery.where(x => x.age==13).ToList();

因此对于这个问题，我相信第一种方法仅在调用toList()时才发送sql调用.
换句话说，发送到sql以执行的查询将是

So for the question, I believe that the 1st method only sends the sql call when toList() is called.
In another words, the query sent to sql for execution will be

Select * from Person where age=13

但是对于方法2，此查询将发送多少次执行?
如果只发送1次，那么调用存储过程是否多余，因为众所周知存储过程具有更快的执行速度，并且发送给sql的查询看起来如何?

But for method 2, how many times will this query be sent for execution?
If it is only sent 1 time, does it make it redundant to call the stored procedure as stored procedure is known for having a faster execution and how will the query sent to sql look like?

推荐答案

对此我不确定，但是PersonContext.sp_RetrievePerson()返回一个ObjectResult，该内部使用DbDataReader.这意味着存储过程将被调用一次， personQuery.where(x => x.age==13)遍历结果行并滤除不匹配的对象.

I am not sure about this one, but PersonContext.sp_RetrievePerson() returns an ObjectResult, which internally uses a DbDataReader. That means that the stored procedure is called once, the personQuery.where(x => x.age==13) iterates over the resulting rows and filters out not matching objects.

为此，通过使用存储过程，可能会在查询数据库时获得少量性能提升，但是在从数据库中读取人员表之后，您必须评估人员表中的每个对象.因此，我认为在这种情况下，如果您向存储过程提供参数age来过滤数据库中已经存在的结果，则使用存储过程才有意义.

By using stored procedures for this you might get a small performance gain on querying the database, but you have to evaluate each object in the persons table, AFTER reading it from the database. So I think in this scenario using stored procedures only makes sense, if you provide a parameter age to the stored procedure for filtering the results already in the database.

这篇关于具有存储过程和IQueryable的LINQ(L2E)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！