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_headerThe 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) daysThis 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] cojoin  [dbo].[Company_contacts] cc on co.Company_reference = cc.Company_referencejoin  [Cust_order_header] coh on coh.Company_cont_key = cc.Company_cont_keyWHERE  [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 should also mention I am using an SQL2000 database. There is nothing I can do about this by the way.Whilst playing around with the SQL to see if I can get improvements, I have noticed that bizarrely if I change the operator from '>' as it should be to '<', which takes the set of rows I was to ignore, it returns 18k rows (instead of the intended 300) but only takes 2 seconds instead of 4 minutes for the smaller query.I'm wondering what the options are, or specifically how I should go about configuring LINQ to do things a bit better. I'm even starting to wonder if it's my dynamic LINQ extension code that is unoptimised (downloaded from here).
Producing queries based on user interface selection
sql;performance;linq