LINQ to SQL: making a "double IN" query crashes

Question

I need to do the following thing:

var a = from c in DB.Customers 
        where (from t1 in DB.Table1 where t1.Date >= DataTime.Now 
               select t1.ID).Contains(c.ID) && 
              (from t2 in DB.Table2 where t2.Date >= DataTime.Now 
               select t2.ID).Contains(c.ID) 
        select c

It doesn't want to run. I get the following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

But when I try to run:

var a = from c in DB.Customers 
        where (from t1 in DB.Table1 where t1.Date >= DataTime.Now 
               select t1.ID).Contains(c.ID)
        select c

Or:

var a = from c in DB.Customers 
        where (from t2 in DB.Table2 where t2.Date >= DataTime.Now 
               select t2.ID).Contains(c.ID) 
        select c

It works! I'm sure that there both IN queries contain some customers ids.

Answer 1

It's not necessarily crashing but rather is likely producing an inefficient query that is timing out. A good thing to do is to run the SQL Server Profiler to see the actual query being emitted in SQL and then to do some analysis on that.

Answer 2

In case this is an efficiency issue, it would be a good idea to look at the SQL query that LINQ to SQL produces (in debug mode, place the mouse cursor over a). In any case, you could try rewriting the query using join. Something like this should do the trick:

var a = from c in DB.Customers  
        join t1 in DB.Table1 on c.ID equals t1.ID
        join t2 in DB.Table2 on c.ID equals t2.ID
        where t1.Date >= DateTimeNow && t2.Date >= DateTimeNow 
        select c

Answer 3

I found the problem. It's in my NEWID() order by method, because I want to get random results. When I remove it, it works fine. How can I use NEWID()?