Make Linq to Sql generate T-SQL with ISNULL instead of COALESCE

Question

I have a linq to sql query that returns some orders with non zero balance (in fact, the query is a little bit complicated, but for simplicity I omitted some details). This query also should return orders with no CardItems (both sub-queries return NULL in T-SQL, and comparing two NULLS gives FALSE, so I convert NULL result values of sub-queries to 0 for comparing).

var q = (from o in db.Orders
         where db.Cards(p =>
             p.OrderId == o.Id 
             && p.Sum + (db.CardItems.Where(i => i.IncomeId == p.Id)
                        .Sum(i => (double?)i.Amount) ?? 0) 
                    != (db.CardItems.Where(i => i.DeductId == p.Id)
                        .Sum(i => (double?)i.Amount) ?? 0)
            ).Any()
         select o);

The problem is, that converting expression Sum(i => (double?)i.Amount) ?? 0 produce COALESCE operator, which is ten times slower than exactly the same T-SQL query with replaced COALESCE to ISNULL because of sub-query in it. Is there any possibility to generate ISNULL in this situation?

Answer 1

In my experience, coaxing linq into generating the SQL you want is a hassle at best. If you've got an query implementation that's better than linq (works correctly and performs well), go ahead and use it, even if just for this one query.

The quickest way is probably with DataContext.ExecuteQuery<TResult>. It will even hydrate and handle the returned objects for you, just as if linq generated the query itself.

http://msdn.microsoft.com/en-us/library/bb361109.aspx

Many people would rather put this SQL in a stored procedure, especially in production code. Then you just have to map the stored procedure into your linq objects, and it works the same way. As is often the case, ScottGu has a rather detailed post about how to do that:

http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx