Linq to SQL: add a custom column and the Order By it?

Question

How could I do something like this in Linq to SQL (VB.NET if possible)?

SELECT *, ISNULL(DueDate, DATEADD(year, 10, GETDATE())) AS DueDateForSorting 
FROM Tasks
ORDER BY DueDateForSorting

Answer 1

I don't think the SQL provider supports adding lambda columns to the query. However, it does support a coalesce operator, and since you are essentially doing this:

SELECT * FROM Tasks
ORDER BY ISNULL(DueDate, DATEADD(year, 10, GETDATE()))

Try this:

from o in dc.Orders orderby o.DueDate ?? DateTime.Today.AddYears(10)

If you need that fake, future date to also be in your resultset for some reason, you can modify the entity class in the LINQtoSQL designer.cs file:

[Column(Storage="_DueDate", DbType="SmallDateTime")]
public System.Nullable<System.DateTime> DueDate
{
    get
    {
     if(this._DueDate == null)
     {
      return DateTime.Today.AddYears(10);
     }
     return this._DueDate;
    }

So if you ever ask for DueDate on the object and DueDate is null, you'll at least get the far-future date.