当被选入一个对象时； Linq To SQL仍在执行SQL查询中的逻辑吗? [英] When selected into an object; does Linq To SQL still perform the logic in a SQL Query?

问题描述

假设我有以下简化的Linq查询，它将使用LinqToSQL在数据库上运行:

Let's say I have the following simplified Linq query which will be run against the database using LinqToSQL:

public IEnumerable<MembershipDto> GetAllUserMemberships()
{
    return (from m in _workspace.GetDataSource<UserMembership>()
            join g in _workspace.GetDataSource<Group>()
                on m.GroupID equals g.GroupID
            join u in _workspace.GetDataSource<User>()
                on m.UserID equals u.UserID
            select 
                new MembershipDto
                {
                    GroupID = g.GroupID,
                    GroupName = g.Name,
                    UserID = u.UserID,
                    UserName = u.Name
                }
            ).Distinct()
             .OrderBy(x => x.GroupName)
             .ThenBy(x => x.UserName);
}

出于性能考虑，我希望在生成的SQL查询中尽可能多地运行此查询；但是我希望在Linq中维护业务逻辑，以便可以轻松地对其进行单元测试.

For performance sake I want as much of this query as possible to be run inside the generated SQL query; however I want the maintain the business logic in Linq so it can be easily unit tested.

我遇到的问题是，我不确定上述查询在哪里停止在SQL中执行并开始在内存中进行操作.

The issue I have is that I am uncertain where the above query stops being executed in SQL and starts being operated on in memory.

我的假设 是，一旦将数据选择到new MembershipDto中，就不再使用SQL进行；因此随后的OrderBy() ThenBy()和Distinct()操作在内存中发生.

My assumption is that as soon as the data is being selected into the new MembershipDto it is no longer being conducted in SQL; therefore the subsequent OrderBy() ThenBy() and Distinct() operations occur in memory.

但是OrderBy()会生成类型为IOrderedQueryable的集合，而不是IOrderedEnumerable；这向我表明查询可能仍可以在SQL中完整执行.

However the OrderBy() produces a collection of type IOrderedQueryable rather than an IOrderedEnumerable; which indicates to me that the query may still be executed in its entirety in SQL.

我的假设是正确的，还是整个查询都转换为SQL?如果是，为什么会这样?

Is my assumption correct, or is the whole query converted into SQL and if so why is this the case?

推荐答案

仅查看生成的SQL.我非常有信心您的斜体假设是错误的. LINQ to SQL提供程序将识别出OrderBy，ThenBy和Distinct操作是专门针对SQL结果执行的，因此它会在将它们反序列化为对象之前完成对所选行的操作.如果您尝试对执行时数据库不可用的值进行操作，则查询的那部分将仅由LINQ to Objects执行.

Just look at the generated SQL. I'm fairly confident that your italicized assumption is wrong. The LINQ to SQL provider will recognize that your OrderBy, ThenBy and Distinct operations are executed exclusively on SQL results so it completes those on the rows you select before deserializing them into objects. That part of the query would only executed by LINQ to Objects if you tried to operate on values that aren't available to the database at execution time.

如果在初始化器中将某些属性设置为查询结果中不可用的值，然后在OrderBy子句中使用该属性，则肯定会在内存中进行排序，因为没有其他方法.在这种情况下，您的选择将转换为SQL SELECT，然后像往常一样调用OrderBy和Distinct.将选择转换为对象是一项完全独立于选择数据库中列的操作.对于SQL提供程序来说，您的查询与简单的select语句没有什么不同，它会构建一个表达式树，并在执行时将所有可能的内容转换为SQL.

If in your initializer you set some property to a value that wasn't available in the query results then used that in the OrderBy clause it would certainly do the ordering in memory because there would be no other way. In this case, your select is translated into a SQL SELECT, then OrderBy and Distinct are called as they normally would be. Converting your selection to an object is an operation that is completely independent from selecting columns in the database. To the SQL provider your query is no different than a simple select statement, it builds an expression tree and converts whatever it can into SQL at execution time.

这篇关于当被选入一个对象时； Linq To SQL仍在执行SQL查询中的逻辑吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！