Producing queries based on user interface selection

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_referen

Company_contacts.Any(x => x.Company_contacts.Cust_order_header.Any(y => y.Order_time >= @0))

That should put the joins in for you. Right now it is not referencing the objects you use in the query through their relationships so it is writing the SQL out as though they were subqueries.

StackExchange Code Review Q#14029, answer score: 3