LINQ中的Order by不适用于Concat() [英] Order by does not work with Concat() in LINQ

问题描述

使用VB.net和以下LINQ语句.我怀疑排序依据"不适用于Concat().我想列出用户拥有的当前项目，然后按升序列出更多可用项目.因此，首先我从数据库中选择当前项目，然后按顺序选择下一个可用项目. LINQ忽略了按语句的顺序，而按PK(即itemID)排序.我在执行该语句后立即检查了列表.当我分解陈述并分别进行处理时，它们将按预期工作.任何想法，建议或意见. 谢谢， 下午

Using VB.net and the following LINQ statement. I suspect the "Order by" does not work with Concat(). I want to list the current item the user has and then list more available items in asending order. So first i select the current item from the db and then select the next available items in order. LINQ is ignoring the order by statement and sorting by the PK (which is itemID) I examined the list immediately after executing the statement. When I break up the statement and do them separately they work as predicted. Any ideas, suggestions or comments. Thanks, PM

(From items In myDatabase.ItemAssignments _
 Where items.BuildingID = buildingID _
 And items.ResidentID = ResidentID _
 Select items).Concat(From moreitems In myDatabase.ItemAssignments _
                      Where moreitems.occupied = 0 _
                      And moreitems.BuildingID = buildingID _
                      Order by moreitems.Floor, moreitems.ItemNumber _
                      Select moreitems)

推荐答案

对于LINQ to SQL，Concat确实忽略了order by子句.如果您使用的是 LINQPad 或设置

The Concat does indeed ignore the order by clause when it comes to LINQ to SQL. This can be verified from the generated SQL if you're using LINQPad or setup the DataContext.Log property.

处理此问题的一种方法是通过匿名类型引入虚拟值以帮助进行排序.为了清楚起见，我将下面的查询进行了拆分，尽管使用从头开始的查询语法也可以使用相同的方法，直到需要指定顺序为止.

One way to deal with this is to introduce a dummy value via an anonymous type to help with the ordering. I've split up the queries below for clarity, although the same approach is possible using the query syntax you started out with up until you need to specify the ordering.

Dim firstQuery = From items In myDatabase.ItemAssignments _
                 Where items.BuildingID = buildingID _
                 And items.ResidentID = ResidentID _
                 Select New With { .Row = items, .Order = 1 }
Dim secondQuery = From moreitems In myDatabase.ItemAssignments _
                  Where moreitems.occupied = 0 _
                  And moreitems.BuildingID = buildingID _
                  Select New With { .Row = moreitems, .Order = 2 }

Dim query = firstQuery.Concat(secondQuery) _
                      .OrderBy(Function(o) o.Order) _
                      .ThenBy(Function(o) o.Row.Floor) _
                      .ThenBy(Function(o) o.Row.ItemNumber) _
                      .Select(Function(o) o.Row)

另一个不太理想的选择是调用 AsEnumerable方法在其中一个查询上，这将从数据库中提取结果.根据涉及的项目数以及是否需要进一步过滤，这可能会对性能产生不利影响.

Another less desirable option is to call the AsEnumerable method on one of the queries, which will pull the results from the database. Depending on the number of items involved and if further filtering is needed, this may have an adverse affect on performance.

要使用这种方法，请将原始查询的第一部分更改为使用:

To use this this approach change the first part of your original query to use:

From items In myDatabase.ItemAssignments.AsEnumerable() ...

第二部分的排序将按预期工作，并且所生成的SQL将反映出很多内容.

The ordering on your second part will then work as intended and the generated SQL will reflect as much.

这篇关于LINQ中的Order by不适用于Concat()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！