Method Name(int, DateTime) has no supported translation to SQL.

Question

I have a function, Name(int, DateTime), that I'd like to use in a linq2sql query.

Its a simple function that computes the difference between the passed in DateTime object and DateTime.Now using a TimeSpan. I then do some basic math stuff with .TotalHours and the passed in int. The most complicated function is Math.Pow(double,double).

public static double Name(int num, DateTime Timestamp)
{
        DateTime now = DateTime.Now;
        DateTime then = Timestamp;

        TimeSpan elapsedSpan = new TimeSpan(now.Ticks - posted.Ticks);
        double t = elapsedSpan.TotalHours;

        return System.Math.Pow(t, num);
}

I get this error:

Method 'Double Name(Int32, System.DateTime)' has no supported translation to SQL. 

When running a query like this:

var q = from k in db.Stuff
        select new {Name=Name(k.num, k.Timestamp)};

I understand that the function cannot be converted to SQL as it currently is written. Is there a way to convert what I have into something that is SQL useable?

I should note that a ToList() or ToArray() method is not useable because the datatable is large.

Answer 1

As your conversion is only use when projecting, I suggest that you simply do that bit in .NET instead of in SQL:

var q = db.Stuff.Select(k => new { k.num, k.Timestamp })
                .AsEnumerable() // Do further processing in .NET
                .Select(k => Name(k.num, k.Timestamp));

Note that I've taken the result out of an anonymous type too, so the type of q will just be an IEnumerable<double>.

If you really need the processing to be done in the database, I suggest you write a user-defined function in SQL yourself and tell LINQ to SQL about that (or make it part of a view that you select from).

Answer 2

LINQ-to-SQL can't translate arbitrary code methods, as they are IL (it expects "expression trees", which is what lambdas and LINQ query syntax a generate). If you only need this logic in your .NET code, switch to LINQ-to-Objects before calling, I.e.

var qry = some complex LINQ query

var data = from row in qry.AsEnumerable()
            select new {
               row.Id,
               Name = YourType.Name(row.Foo, row.Bar)
            };

The AsEnumerable() breaks the LINQ composition in two, allowing your managed code to run as normal.

If you need to do something similar at the database, then write the code in TSQL as a UDF, then map the UDF into LINQ-to-SQL by dragging it onto the designer. You can then access it as a new method on the data-context, I.e.

 select db.Name(row.Foo, row.Bar)