Can LINQ to SQL generate TSQL containing the ISNULL function?

c#, linq-to-sql

Solution

The only way I know of accomplishing this is via your own class like so:

public partial class LocalTestDataContext
{
    [Function(Name = "IsNull", IsComposable = true)]
    [return: Parameter(DbType = "NVarChar(MAX)")]
    public string IsNull(
        [Parameter(Name = "field", DbType = "NVarChar(MAX)")] string field,
        [Parameter(Name = "output", DbType = "NVarChar(MAX)")] string output)
    {
        return ((string)(this.ExecuteMethodCall(this,
                ((MethodInfo)(MethodInfo.GetCurrentMethod())),
                field, output).ReturnValue));
    }
}

This is under "Take #3" from here.

var ctx = new LocalTest.LocalTestDataContext(); 

var query = from c in ctx.Categories 
orderby ctx.IsNull(c.Description1, "") + ctx.IsNull(c.Description2, "") 
select c; 
query.Dump();

And will generate T-SQL with ISNULL().

Problem

I was wondering whether anyone knows definitively if LINQ to SQL has the capability of generating TSQL code that contains the `ISNULL` function? I'm aware that using the coalesce operator (`??`) in a query: ``` from o in Table where (o.Field ?? 0) > 0 select o ``` will cause LINQ to SQL to emit the `COALESCE` function: ``` SELECT [t0].[Field] FROM [Table] AS [t0] WHERE (COALESCE([t0].[Field],0)) > 0 ``` And, that using the conditional operator (`?:`) in a query: ``` from o in Table where (o.Field == null ? 0 : o.Field) > 0 select o ``` will result in TSQL containing a `CASE` statement: ``` SELECT [t0].[Field] FROM [Table] AS [t0] WHERE ( (CASE WHEN [t0].[Field] IS NULL THEN 0 ELSE [t0].[Amount] END)) > 0 ``` But, can LINQ to SQL be coerced into generating TSQL code that contains `ISNULL` like the following? ``` SELECT [t0].[Field] FROM [Table] AS [t0] WHERE (ISNULL([t0].[Field],0)) > 0 ``` I'm betting the answer is "no, it can't," but I'd like to see something authoritative.

Original source