Sql to Linq Nested Joins

Question

I need to get the corresponding Linq query for the sql mentioned below, i am struggling with Nested Joins

SQL Code :

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'

I tried to break the query in smaller pieces and tried with the initial 2 joins

    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) Is the above linq correct for above sql. I am getting different number of records from sql and linq so i believe the linq code is incorrect.

2) I need to convert the orginal sql to linq.

Any help will be greatly appreciated.

Answer 1

Try This... It's Work for me perfectly..

var query = (from a in objEntity.tblStock
                     join b in objEntity.tblScript on a.ScriptId equals b.ScriptId
                     join c in (from x in objEntity.tblScript join y in objEntity.tblSector on x.SectorId equals y.SectorId select new{x.ScriptId, x.SectorId, y.SectorName}) on a.ScriptId equals c.ScriptId
                     join d in objEntity.tblShareholder on a.ShareholderId equals d.ShareholderId
                     select new
                         {
                             a.StockId,
                             b.ScriptId,
                             b.SectorId,
                             a.ShareholderId,
                             b.ScriptName,
                             c.SectorName,
                             d.Shareholdername,
                         }).ToList();

Answer 2

Maybe you need something like this.

Example Classes

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 Implementation:

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 }
                         };