Linq-to-SQL left join in a left join / multiple left join in a single Linq-to-SQL expression

Question

I am trying to rewrite the SQL procedure in Linq, everything went well and works fine if it works on a small dataset. I could not find the answer to this. The fact is that I have 3 connections in the request, 2 - left joins , and 1 - inner join , they all connect to each other / like a tree. Below you can see the SQL procedure:

 SELECT ... FROM sprawa s (NOLOCK) LEFT JOIN strona st (NOLOCK) on s.ident = st.id_sprawy INNER JOIN stan_szczegoly ss (NOLOCK) on s.kod_stanu = ss.kod_stanu LEFT JOIN broni b (NOLOCK) on b.id_strony = st.ident 

What I would like to ask you is a way to translate this to Linq. At the moment I have this:

 var queryOne = from s in db.sprawa join st in db.strona on s.ident equals st.id_sprawy into tmp1 from st2 in tmp1.DefaultIfEmpty() join ss in db.stan_szczegoly on s.kod_stanu equals ss.kod_stanu join b in db.broni on st2.ident equals b.id_strony into tmp2 from b2 in tmp2.DefaultIfEmpty() select new { }; 

It seems nice, but when checking with SQL Profiler, the query sent to the database looks like this:

 SELECT ... FROM [dbo].[sprawa] AS [Extent1] LEFT OUTER JOIN [dbo].[strona] AS [Extent2] ON [Extent1].[ident] = [Extent2].[id_sprawy] INNER JOIN [dbo].[stan_szczegoly] AS [Extent3] ON [Extent1].[kod_stanu] = [Extent3].[kod_stanu] INNER JOIN [dbo].[broni] AS [Extent4] ON ([Extent2].[ident] = [Extent4].[id_strony]) OR (([Extent2].[ident] IS NULL) AND ([Extent4].[id_strony] IS NULL)) 

As you can see, SQL queries are a bit different. The effect is the same, but the latter works incomparably slower (less than a second to more than 30 minutes). union also performed there, but this should not be a problem. If they ask me, I will put the code for it.

I would appreciate any advice on how to improve the performance of my Linq instruction or how to write it in a way that translates correctly.

Answer 1

I assume I found a solution:

 var queryOne = from s in db.sprawa join st in db.strona on s.ident equals st.id_sprawy into tmp1 where tmp1.Any() from st2 in tmp1.DefaultIfEmpty() join ss in db.stan_szczegoly on s.kod_stanu equals ss.kod_stanu join b in db.broni on st2.ident equals b.id_strony into tmp2 where tmp2.Any() from b2 in tmp2.DefaultIfEmpty() select new { }; 

In other words, where table.Any() after each into table statement. This does not make the translation better, but it speeds up the execution time from almost 30 minutes (!) To 5 seconds.

This should be used carefully because it MAY result in the loss of some records in the result set.