LEFT JOIN在LINQ到实体？ [英] LEFT JOIN in LINQ to entities?

问题描述

我想出来的LINQ to实体。

我有以下问题：
我希望它这样做：

  SELECT
     T_Benutzer.BE_User
    ，T_Benutzer_Benutzergruppen.BEBG_BE
从T_BenutzerLEFT JOIN T_Benutzer_Benutzergruppen
    ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID
 

我来最接近的是：

  VAR笑=（
            从u在Repo.T_Benutzer            //其中u.BE_ID == 1
            从o在Repo.T_Benutzer_Benutzergruppen.DefaultIfEmpty（）
                //对u.BE_ID等于o.BEBG_BE            其中（u.BE_ID == || o.BEBG_BE == o.BEBG_BE空）            在Repo.T_Benutzergruppen.DefaultIfEmpty //加入BG（）
            //对o.BEBG_BG等于bg.ID            //其中bg.ID == 899            排序依据
                u.BE_Name上升
                //，bg.Name降            //选择ü
            新选择
            {
                 u.BE_User
                ，o.BEBG_BG
                //，bg.Name
            }
         ）.ToList（）;
 

但是，这会产生相同的结果内部联接，而不是一个左连接。

此外，它创建了一个完全疯狂的SQL：

  SELECT
     [Extent1]。[BE_ID] AS [BE_ID]
    [Extent1]。[BE_User] AS [BE_User]
    [Join1]。[BEBG_BG] AS [BEBG_BG]
FROM [DBO]。[T_Benutzer] AS [Extent1]CROSS JOIN
（
    选择
         [Extent2]。[BEBG_BE] AS [BEBG_BE]
        [Extent2]。[BEBG_BG] AS [BEBG_BG]
    FROM（SELECT 1 AS X）AS [SingleRowTable1]
    LEFT OUTER JOIN [DBO]。[T_Benutzer_Benutzergruppen] AS [Extent2]
        ON 1 = 1
）AS [Join1]WHERE [Extent1]。[BE_ID] = [Join1]。[BEBG_BE]
OR [Join1]。[BEBG_BE] IS NULLORDER BY [Extent1]。[BE_Name] ASC
 

如何我可以做一个左侧LINQ-2-实体的方式加入其中，
其他人仍然可以了解发生了什么在code正在做？

和most- preferably如果生成的SQL如下：

  SELECT
     T_Benutzer.BE_User
    ，T_Benutzer_Benutzergruppen.BEBG_BE
从T_BenutzerLEFT JOIN T_Benutzer_Benutzergruppen
    ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID
 

解决方案

嗯，知道了myselfs。

LINQ-2-实体的怪癖和夸克。

这看起来最可以理解的：

  VAR QUERY2 =（
    从Repo.T_Benutzer用户
    从Repo.T_Benutzer_Benutzergruppen映射
        。凡（映射=＆GT; mapping.BEBG_BE == users.BE_ID）.DefaultIfEmpty（）
    从Repo.T_Benutzergruppen组
        。凡（GRUPPE =＆GT; gruppe.ID == mappings.BEBG_BG）.DefaultIfEmpty（）
    //其中users.BE_Name.Contains（关键字）
    // // || mappings.BEBG_BE.Equals（666）
    // || mappings.BEBG_BE == 666
    // || groups.Name.Contains（关键字）    新选择
    {
         用户ID = users.BE_ID
        用户名= users.BE_User
        ，UserGroupId = mappings.BEBG_BG
        ，组名= groups.Name
    }）;
变种的xy =（QUERY2）.ToList（）;
 

删除 .DefaultIfEmpty（），你会得到一个内部联接。

这正是我所期待的。

I'm trying out LINQ to entities.

I have a problem with the following: I want it to do this:

SELECT 
     T_Benutzer.BE_User
    ,T_Benutzer_Benutzergruppen.BEBG_BE
FROM T_Benutzer

LEFT JOIN T_Benutzer_Benutzergruppen
    ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID 

the closest thing I've come to is this:

        var lol = (
            from u in Repo.T_Benutzer

            //where u.BE_ID == 1
            from o in Repo.T_Benutzer_Benutzergruppen.DefaultIfEmpty()
                // on u.BE_ID equals o.BEBG_BE

            where (u.BE_ID == o.BEBG_BE || o.BEBG_BE == null)

            //join bg in Repo.T_Benutzergruppen.DefaultIfEmpty()
            //    on o.BEBG_BG equals bg.ID

            //where bg.ID == 899 

            orderby
                u.BE_Name ascending
                //, bg.Name descending

            //select u 
            select new
            {
                 u.BE_User
                ,o.BEBG_BG
                //, bg.Name 
            }
         ).ToList();

But this generates the same results as an inner join, and not a left join.
Moreover, it creates this completely crazy SQL:

SELECT 
     [Extent1].[BE_ID] AS [BE_ID]
    ,[Extent1].[BE_User] AS [BE_User]
    ,[Join1].[BEBG_BG] AS [BEBG_BG]
FROM  [dbo].[T_Benutzer] AS [Extent1]

CROSS JOIN  
(
    SELECT 
         [Extent2].[BEBG_BE] AS [BEBG_BE]
        ,[Extent2].[BEBG_BG] AS [BEBG_BG]
    FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
    LEFT OUTER JOIN [dbo].[T_Benutzer_Benutzergruppen] AS [Extent2] 
        ON 1 = 1 
) AS [Join1]

WHERE [Extent1].[BE_ID] = [Join1].[BEBG_BE] 
OR [Join1].[BEBG_BE] IS NULL

ORDER BY [Extent1].[BE_Name] ASC

How can I do a left join in LINQ-2-entities in a way where another person can still understand what's being done in that code ?

and most-preferably where the generated SQL looks like:

SELECT 
     T_Benutzer.BE_User
    ,T_Benutzer_Benutzergruppen.BEBG_BE
FROM T_Benutzer

LEFT JOIN T_Benutzer_Benutzergruppen
    ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID 

解决方案

Ah, got it myselfs.
The quirks and quarks of LINQ-2-entities.
This looks most understandable:

var query2 = (
    from users in Repo.T_Benutzer
    from mappings in Repo.T_Benutzer_Benutzergruppen
        .Where(mapping => mapping.BEBG_BE == users.BE_ID).DefaultIfEmpty()
    from groups in Repo.T_Benutzergruppen
        .Where(gruppe => gruppe.ID == mappings.BEBG_BG).DefaultIfEmpty()
    //where users.BE_Name.Contains(keyword)
    // //|| mappings.BEBG_BE.Equals(666)  
    //|| mappings.BEBG_BE == 666 
    //|| groups.Name.Contains(keyword)

    select new
    {
         UserId = users.BE_ID
        ,UserName = users.BE_User
        ,UserGroupId = mappings.BEBG_BG
        ,GroupName = groups.Name
    }

);


var xy = (query2).ToList();

Remove the .DefaultIfEmpty(), and you get an inner join.
That was what I was looking for.

这篇关于LEFT JOIN在LINQ到实体？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！