Sql to Linq嵌套连接
本文关键字:连接 嵌套 Linq to Sql | 更新日期: 2023-09-27 18:06:07
我需要得到相应的Linq查询下面提到的sql,我正在与嵌套连接
斗争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'
我尝试将查询分解成更小的部分,并尝试使用初始的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.也许你需要这样的东西。
示例类
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 }
};
Try This…这是我完美的工作。
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();