Linq to SQL: How can I order by composite object?
I have the following Linq to SQL query in which I am trying to execute many GROUP BY columns:
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 an InvalidOperationException ("Unable to order by type" Task ".).
Is there an interface that Task should do (it already implements IComparable, IComparable <ITask>)? Whether the task should be a Linq to SQL binding (it is missing, currently, as there is no corresponding table). Or is it just something that Linq to SQL doesn't support?
Please note that I already tried anonymous type for grouping which failed with a similar InvalidOperationException:
...
group revision by new { Code = revision.TaskSourceCode.ToUpper(),
Id = revision.TaskSourceID }
...
For this it is worth noting that the Task object is composed of 2 fields; one is one character (usually "S" or "B") and the other is an int (actually it's a "cross-database" "foreign key" if you like). The act of ordering by tasks simply compares their string representations; EG. Task 'B101' <Task 'S999'
source to share
It looks like you already have a solution, but only FYI LINQ to SQL supports .ToUpper()
.
For example:
NorthwindDataContext dc = new NorthwindDataContext();
Product product = dc.Products.Single(p => p.ProductName.ToUpper() == "CHAI");
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 it helps.
source to share
OK, I figured it out. I had two problems; first of all, ToUpper () doesn't translate to SQL, and secondly, I don't think Linq to SQL supports orderby on objects; at least not for non-entities. By decomposing the order 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);
source to share