在一个Linq-to-SQL语句中，Linq-to-SQL左联接为左联接/多个左联接 [英] Linq-to-SQL left join on left join/multiple left joins in one Linq-to-SQL statement

问题描述

我正在尝试将SQL过程重写为Linq，只要运行在小型数据集上，一切都运行良好并且运行良好.我真的在任何地方都找不到答案.问题是，我在查询中有3个连接，2个是left joins，1个是inner join，它们都彼此连接/就像树一样.在下面您可以看到SQL过程:

I'm trying to rewrite SQL procedure to Linq, it all went well and works fine, as long as it works on small data set. I couldn't really find answer to this anywhere. Thing is, I have 3 joins in the query, 2 are left joins and 1 is inner join, they all join to each other/like a tree. Below you can see 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

我想问你的是一种将其翻译成Linq的方法.现在我有这个:

What I'd like to ask you is a way to translate this to Linq. For now 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 { };

看起来不错，但是当使用SQL Profiler进行检查时，发送到数据库的查询看起来像这样:

Seems alright, but when checked with SQL Profiler, query that is sent to database looks like that:

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))

如您所见，两个SQL查询都有些不同.效果是一样的，但是后者的工作速度却无与伦比(不到一秒到30分钟以上).还有一个union，但这不应该是问题.如果要求我将其粘贴代码.

As you can see both SQL queries are bit different. Effect is the same, but latter works incomparably slower (less than a second to over 30 minutes). There's also a union made, but it shouldn't be the problem. If asked for I'll paste code for it.

对于如何改善Linq语句的性能或如何以正确翻译的方式编写它的任何建议，我将不胜感激.

I'd be grateful for any advice on how to better the performance of my Linq statement or how to write it in a way that is translated properly.

推荐答案

我想我找到了解决方法:

I guess I found the 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 { };

换句话说，在每个into table语句之后的where table.Any().它并不能使翻译效果更好，但可以将执行时间从近30分钟(！)缩短到大约5秒.

In other words where table.Any() after each into table statement. It doesn't make translation any better but has sped up execution time from nearly 30minutes(!) to about 5 seconds.

这必须谨慎使用，因为它可能会导致结果集中的某些记录丢失.

This has to be used carefully though, because it MAY lead to losing some records in result set.

这篇关于在一个Linq-to-SQL语句中，Linq-to-SQL左联接为左联接/多个左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！