How do I OrderBy a DateTime difference in Linq?

Question

I have a table with a CreatedDate column. I want to order by the rows that have been in the database the longest, but need to determine the number of days by subtracting it from DateTime.Now.

I realise this is doable simply by ordering by the CreatedDate, but I need to do more than that.

I need to order the items by the amount of times they have been used within a period of time, in effect getting the most popular items.

So what I want to do is first determine the number of days, and after that divide this by the number of times each item has been used.

Step 1)

orderby (DateTime.Now.Subtract(t.CreatedDate.Value).Days)

Step 2)

orderby (t.UsedCount/DateTime.Now.Subtract(t.CreatedDate.Value).Days)

This results in the following error:

Method 'System.TimeSpan Subtract(System.DateTime)' has no supported translation to SQL.

Answer 1

Oddly, "Subtract()" doesn't work, but Minus does. try:

 orderby (t.UsedCount/(DateTime.Now - t.CreatedDate.Value).Days)

(Tested in LINQPad using the "master" database)

from sp in Spt_monitors
orderby sp.Io_busy / (DateTime.Now - sp.Lastrun).Days
select sp

Answer 2

here is a useful link for translatable methods for DateTime and LINQ to SQL

MSDN