LINQ Query performance - how to go about improving THIS query?
===

I have written a module in my application that makes extensive use of Dynamic LINQ to produce linq/sql queries based on user interface selection.
Currently, the linq-sql translation is resulting in very unoptimised query results, so I am looking at ways to get LINQ to improve the SQL that it is producing.

The following is a typical scenario:

Firstly, the user interface controls are selected, resulting in a LINQ statement of:

`Company_contacts.Any(Cust_order_header.Any(Order_time >= @0))`

The table relations are as following : Companies 1-* Contacts 1-* Cust_order_header
The Cust_order_header contains the datetime field 'Order_time'.

Don't worry about @0 - that is a dynamic linq parameter that is passed in containing the datetime object.

The query translates to "Give me all companies that have placed orders within the last (insert days based on datetime) days".

 This results in a query that takes 5 minutes to complete, and kicks the hell out of the server.

The SQL it has created (which I have retrieved using `((ObjectQuery)groupQuery).ToTraceString())` is 


    SELECT *
    FROM [dbo].[Companies] AS [Extent1]
    WHERE  EXISTS (SELECT 
    	1 AS [C1]
    	FROM ( SELECT 
    		[Extent2].[Company_cont_key] AS [Company_cont_key]
    		FROM [dbo].[Company_contacts] AS [Extent2]
    		WHERE [Extent1].[Company_reference] = [Extent2].[Company_reference]
    	)  AS [Project1]
    	WHERE  EXISTS (SELECT 
    		1 AS [C1]
    		FROM [dbo].[Cust_order_header] AS [Extent3]
    		WHERE ([Project1].[Company_cont_key] = [Extent3].[Company_cont_key]) AND ([Extent3].[Order_time] >= convert(datetime, '2012-05-20 00:00:00.000', 121))
    	)
    )

Now, firstly - there are no joins in there, which leads me to think that the sql it is producing is significantly unoptimised.

AS, if I do to the following in SQL Management Studio manually:

    SELECT *
    FROM [dbo].[Companies] co
    join  [dbo].[Company_contacts] cc on co.Company_reference = cc.Company_reference
    join  [Cust_order_header] coh on coh.Company_cont_key = cc.Company_cont_key
    WHERE  [Order_time] >= convert(datetime, '2012-05-20 00:00:00.000', 121)

Then it's < 1s. 

So, I have spent 3 weeks putting together my Dynamic-LINQ based user controls/solution, getting rid of it isn't an option.

I'm wondering what the options are, or specifically how to go about configuring LINQ to do things a bit better. I'm even starting to wonder if its my dynamic LINQ extension code that is resulting in the unoptimised. (downloaded from [here][1])

Any help would be appreciated. Thanks.


  [1]: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx