Linq to SQL：我如何按复合对象排序？

I have the following Linq to SQL query, in which I'm trying to do a multi-column GROUP BY:

我有以下Linq to SQL查询,其中我正在尝试执行多列GROUP BY:

return from revision in dataContext.Revisions
       where revision.BranchID == sourceBranch.BranchID-1
                && !revision.HasBeenMerged
       group revision by new Task(revision.TaskSourceCode.ToUpper(), 
                                  revision.TaskSourceID)
       into grouping
       orderby grouping.Key ascending
       select (ITask)grouping.Key;

This throws InvalidOperationException ("Cannot order by type 'Task'.").

抛出InvalidOperationException(“不能按类型'任务'排序。”)。

Is there an interface that Task must implement (it already implements IComparable, IComparable<ITask>)? Does Task need to be a Linq to SQL Entity (it isn't, currently, since there's no corresponding table). Or is this just something that Linq to SQL doesn't support?

是否有一个Task必须实现的接口(它已经实现了IComparable,IComparable )? Task是否需要是Linq to SQL Entity(目前不是,因为没有相应的表)。或者这只是Linq to SQL不支持的东西?

Note that I've already tried an anonymous type for the grouping, which failed with a similar InvalidOperationException:

请注意,我已经尝试了一个匿名类型的分组,它失败了类似的InvalidOperationException:

...
group revision by new { Code = revision.TaskSourceCode.ToUpper(),
                        Id = revision.TaskSourceID } 
...

For what it's worth, note that the Task object is a composite of 2 fields; one is a single character (typically 'S' or 'B') and the other is an int (actually a cross-database "foreign key" if you like). The act of ordering by Tasks just compares their string representations; E.G. Task 'B101' < Task 'S999'

值得注意的是,Task对象是2个字段的组合;一个是单个字符(通常是'S'或'B'),另一个是int(如果你愿意,实际上是一个跨数据库的“外键”)。任务排序的行为只是比较他们的字符串表示;例如。任务'B101' <任务's999'< p>

4 个解决方案

#1

Looks like you already have your solution, but just FYI LINQ to SQL does support .ToUpper().

看起来你已经有了解决方案,但只有FYI LINQ to SQL确实支持.ToUpper()。

For instance:

NorthwindDataContext dc = new NorthwindDataContext();
Product product = dc.Products.Single(p => p.ProductName.ToUpper() == "CHAI");

Is translated into:

被翻译成:

exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE <b>UPPER([t0].[ProductName]) = @p0</b>',N'@p0 nvarchar(4)',@p0=N'CHAI'

Hope that helps.

希望有所帮助。

#2

OK, I figured this out. I had two problems; first of all, ToUpper() doesn't translate into SQL, and secondly, I don't think Linq to SQL supports orderby on objects; at least not non-entities. By decomposing the orderby into its constituent columns, everything started to work as planned.

好的,我想出来了。我有两个问题;首先,ToUpper()不会转换为SQL,其次,我认为Linq to SQL不支持对象的orderby;至少不是非实体。通过将orderby分解为其组成列,一切都按计划开始工作。

return from revision in dataContext.Revisions
       where revision.BranchID == sourceBranch.BranchID-1
                && !revision.HasBeenMerged
       group revision by new { revision.TaskSourceCode, 
                                  revision.TaskSourceID }
       into grouping
       orderby grouping.Key.TaskSourceCode, 
               grouping.Key.TaskSourceID ascending
       select (ITask)new Task(grouping.Key.TaskSourceCode, 
                              grouping.Key.TaskSourceID);

#3

I think you need a select before your group by - you want to convert objects to Tasks, then order by something else (Task.start or something)

我认为你需要在你的小组之前选择一个 - 你想将对象转换为任务,然后通过其他东西订购(Task.start或其他)

#4

Linq to SQL may support String.ToUpper(), but it doesn't support Char.ToUpper() which is what I would need for this particular table and column. But thanks for the info anyway!

Linq to SQL可能支持String.ToUpper(),但它不支持Char.ToUpper(),这是我需要的特定表和列。但无论如何,谢谢你的信息!

#1