c# - Sql 到 Linq 嵌套连接

Question

我需要为下面提到的 sql 获取相应的 Linq 查询，我正在为嵌套连接而苦苦挣扎

SQL 代码:

SELECT   
        *
FROM
        ((((table1
        INNER JOIN
        (table2
        RIGHT JOIN
        table3
        ON table2.StID = table3.StID)
        ON table1.SCode = table3.ECode)
        LEFT JOIN
        table4
        ON table3.TypeID = table4.TypeID)
        LEFT JOIN
        table5
        ON table3.ValueID = table5.ValueId)
        LEFT JOIN
        table2 AS table6
        ON table3.[Num] = table6.StID)
        LEFT JOIN
        table5 AS table7
        ON table3.[TValueID] = table7.ValueId
WHERE
        table2.Col1 = '1000'

我尝试将查询分成更小的部分并尝试使用最初的 2 个连接

    I tried to make a Linq for

    select * from
    (table1
    INNER JOIN
    (table2
    RIGHT JOIN
    table3
    ON table2.StID = table3.StID)
    ON table1.SCode = table3.ECode)


    Linq :

    var query = from rightJoin in
                           (
                              from t3 in table3
                              join t2 in table2
                              on t3.StID equals t2 .StID into joined
                              from T in joined.DefaultIfEmpty()
                              select new
                               {
                                    A = t3,
                                    B = T
                                }
                             )
                        join T1 in table1
                        on rightJoin.A.ECode equals T1.SCode into innerjoin
                        select new
                        {
                            C = rightJoin.A.ECode
                        };

             int i = query.Select(a => a.C).ToList().Count;

1) 上面的 linq 对于上面的 sql 是否正确。我从 sql 和 linq 获得了不同数量的记录，所以我认为 linq 代码不正确。

2)我需要将原始sql转换为linq。

任何帮助将不胜感激。

Answer 1

也许你需要这样的东西。

示例类

public class table1
{
    public string SCode { get; set; }
}

public class table2
{
    public int StID { get; set; }
    public string Col1 { get; set; }
}

public class table3
{
    public int StID { get; set; }
    public int TypeID { get; set; }
    public int ValueID { get; set; }
    public int Num { get; set; }
    public int TValueID { get; set; }
    public string ECode { get; set; }
}

public class table4
{
    public int TypeID { get; set; }
}

public class table5
{
    public int ValueId { get; set; }
}

Linq 实现:

var Select = from Table2 in dc.GetTable<table2>()                             
                         //Right Join                                                      
                         from Table3_3 in dc.GetTable<table3>()
                         .Where(item => item.StID == Table2.StID)
                         .Select(item => item)                             
                         //Inner Join From Right Join                                                                                       
                         join Table1_3_1 in dc.GetTable<table1>()                                 
                            on Table3_3.ECode equals Table1_3_1.SCode
                         //Left Join table4
                         join entityTable4 in dc.GetTable<table4>()
                            on Table3_3.TypeID equals entityTable4.TypeID into tempTable4
                         from Table4 in tempTable4.DefaultIfEmpty()
                         //Left Join table5
                         join entityTable5 in dc.GetTable<table5>()
                            on Table3_3.ValueID equals entityTable5.ValueId into tempTable5
                         from Table5 in tempTable5.DefaultIfEmpty()
                         //Left Join table2 (table6)
                         join entityTable2 in dc.GetTable<table2>()
                            on Table3_3.Num equals entityTable2.StID into tempTable2
                         from Table6 in tempTable2.DefaultIfEmpty()
                         //Left Join table5 (table7)
                         join entityTable5 in dc.GetTable<table5>()
                            on Table3_3.TValueID equals entityTable5.ValueId into tempTable5_7
                         from Table7 in tempTable5_7.DefaultIfEmpty()
                         //Filter
                         where Table2.Col1 == "1000"
                         select new
                         {  
                             table1 = new { SCode = (string)Table1_3_1.SCode },
                             table2 = new { StID = (int)Table2.StID, Col1 = (string)Table2.Col1 },
                             table3 = new
                             {
                                 StID = (int)Table3_3.StID,
                                 TypeID = (int)Table3_3.TypeID,
                                 ValueID = (int)Table3_3.ValueID,
                                 TValueID = (int)Table3_3.TValueID,
                                 Num = Table3_3.Num,
                                 ECode = Table3_3.ECode
                             },
                             table4 = Table4 == null ? null : new { TypeID = (int)Table4.TypeID },
                             table5 = Table5 == null ? null : new { ValueID = (int)Table5.ValueId },
                             table6 = Table6 == null ? null : new { StID = (int)Table6.StID, Col1 = (string)Table6.Col1 },
                             table7 = Table7 == null ? null : new { ValueID = (int)Table7.ValueId }
                         };