Linq to SQL: How can I Order By a composite object?

Question

I have the following Linq to SQL query, in which I'm trying to do a multi-column 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'.").

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?

Note that I've already tried an anonymous type for the grouping, which failed with a similar 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'

Answer 1

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)

Answer 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.

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);

Answer 3

Matt,

Looks like you already have your solution, but just FYI LINQ to SQL does support .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 UPPER([t0].[ProductName]) = @p0',N'@p0 nvarchar(4)',@p0=N'CHAI'

Hope that helps, - Lee

Answer 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!