Nested query in from clause

Question

Hello, I wrote this LINQ code:

from workTask in VwWorkTask.Where(e => e.TaskStateStr != "A" 
                                    && e.TaskStateStr != "B")
join workContext in TblWorkTOBProlongationWorkContexts 
        on workTask.WorkContextId equals workContext.Id  
join client in VwClient 
        on workContext.Client equals client.ClientId  into t1
from client in t1.DefaultIfEmpty()
....// other joins
select workTask

Which generates this T-SQL query:

SELECT [t0].*
FROM [vwWorkTask] AS [t0]
INNER JOIN [tblWorkTOBProlongationWorkContext] AS [t1] 
        ON [t0].[WorkContextId] = ([t1].[Id])
LEFT OUTER JOIN [vwClient] AS [t2] ON [t1].[Client] = [t2].[ClientId]
... -- other joins
WHERE ([t0].[TaskStateStr] <> @p0) AND ([t0].[TaskStateStr] <> @p1)

But I`m need something like that instead:

SELECT [t0].*
FROM [select * vwWorkTask WHERE ([t0].[TaskStateStr] <> @p0) 
                          AND ([t0].[TaskStateStr] <> @p1)] AS [t0]
INNER JOIN [tblWorkTOBProlongationWorkContext] AS [t1] 
        ON [t0].[WorkContextId] = ([t1].[Id])
LEFT OUTER JOIN [vwClient] AS [t2] ON [t1].[Client] = [t2].[ClientId]
... -- other joins

In other words I need nested query with "where" check before all joins, not after. Any ideas how can I rewrite LINQ query to accomplish that?

Thanks.

Answer 1

Are you sure you need that?

The two query should be effectively the same.

I think you are worried that the data server is doing more work than needed, but you should trust that it knows what it is doing.

Answer 2

Ok, maybe there are another options? Maybe I must try to convince my co-workers and PM that LINQ2SQL and, maybe ORMs at all, - bad choose for our project and we must rewrite queries using only sql, before it`s too late?

Answer 3

Generated t-sql query executes about 18 secs, which completly inappropriate. Second one, written "by hands", executes 2 secs at max.

Show us the actual queries and we'll show you where the filtering occurs in the second one that allows it to use indexing.

Until then, you're just trolling.

Answer 4

Show us the actual queries

Original post contains both queries. What else do your need? They almost identical except one little detail - generated query apply "where" conditions after all joinings. I believe that MSSQL optimizer cant built optimal execution plan because of nested views... But in the other hand I cant just stop using this views, otherwise man responsible for their creation just would kill me.

Answer 5

If you aren't satisfied with the TSQL that's being generated, then consider your option of writing your exact query into a stored procedure. Make a new stored procedure, perhaps named GetWorkTaskClientSomething. Paste that TSQL call with the appropriate parameters required.

You can then map the return result sets to a custom class of your choice. You can write this from scratch, or reuse an existing class.

That would allow you to control the TSQL as you see fit, and would allow you to continue using the model classes that LINQ To SQL providers for you.

List<foo> = db.GetWorkTaskClientSomething('foo', 'bar', 1);