使用sql linq查询的结果导致两次查询到数据库beeing [英] Using the result of a sql linq query leads to the database beeing queried twice

问题描述

我查询数据库，并使用以下两种方法将结果推送到控制台和文件:

I query a database and push the result out to the console and a file using two methods like this:

var result = from p in _db.Pages
             join r in rank on p.PkId equals r.Key
             orderby r.NumPages descending
             select new KeyNameCount
                                    {
                                        PageID = p.PkId,
                                        PageName = p.Name,
                                        Count = r.NumPages
                                    };

WriteFindingsToFile(result, parentId);
WriteFindingsToConsole(result, parentId);

IEnumerable 而不是IQuerable 在两个方法调用中用作参数时，用作结果的参数类型.

IEnumerable<T>, not IQuerable<T> is used as parametertype for result when used as a parameter in the two method calls.

在两个调用中，结果都在foreach中进行迭代.这将导致对数据库的两次相同调用，每种方法一次.

In both calls the result is iterated in a foreach. This leads to two identical calls against the database, one for each method.

我可以将这两种方法重构为一种，并且只使用一种foreach，但这很快就会变得很难维护(添加对html的写入，对xml的写入等)

I could refactor the two methods into one and only use one foreach, but that would fast become very hard to maintain (adding write to html, write to xml, etc.)

我很确定这是一个非常普遍的问题，但是使用google并没有使我变得更明智，所以我转向你们:-)

I am pretty sure this is a farly common question, but using google has not made me any wiser, so I turn to you guys :-)

推荐答案

每次访问LINQ查询时，它将重新查询数据库以刷新数据.要阻止这种情况发生，请使用.ToArray()或.ToList().

Every time you access a LINQ query it will requery the database to refresh the data. To stop this happening use .ToArray() or .ToList().

例如

var result = (from p in _db.Pages
             select p).ToList(); //will query now

Write(result);  //will not requery
Write(result2); //will not requery

重要的是要了解原始的LINQ查询在使用时运行，而不是在代码中写入时运行(例如，在此之前不要丢弃_db).

It's important to understand that a raw LINQ query is run when it is used, not when it is written in the code (e.g. don't dispose of your _db before then).

当您意识到它真正地工作原理时，可能会感到惊讶.这允许方法链接和查询的后续修改反映在数据库上运行的最终查询中.切记，这很重要，因为它可能导致运行时错误，这些错误在编译时不会捕获，通常是因为在使用列表之前，数据库连接已关闭，因为您正在传递看起来很简单的IEnumerable

It can be surprising when you realise how it really works. This allows method chaining and later modification of the query to be reflected in the final query run on the DB. It is important to always keep in mind as it can cause run-time bugs that will not be caught at compile time, usually because the DB connection is closed before the list is used, as you are passing around what appear to be a simple IEnumerable.

已更改为删除我的意见并在评论中反映讨论内容.我个人认为编译器应该假定链接查询的最终结果可以立即运行，除非您明确表示稍后将对其进行进一步修改.只是为了避免不可避免地导致运行时错误.

Changed to remove my opinion and reflect the discussion in the comments. Personally I think the compiler should assume that the end result of chained queries is immediately run unless you explicitly say that it'll be further modified later. Just to avoid the run-time bugs it inevitably causes.

这篇关于使用sql linq查询的结果导致两次查询到数据库beeing的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！