Linq左连接+多条件

[DengWanLong]

select p.Id,r.Id,s.Id,s.Name,p.Title,p.TotalScore from JHExamPaper as ep
inner join Paper as p on ep.PaperId=p.Id
inner join Subject as s on p.SubjectId=s.Id
left join ExamPaperStudent as eps on ep.PaperId=eps.PaperId and eps.ExamId='B39A5BEE-8021-4A0D-8CC2-4BDEFC580579' and UserId='110813D3-AF1D-41A2-9F39-B44604CA2BC6'
left join Report as r on eps.Id=r.EPSId

如何把上面的SQL转化成Linq，求大神指教，在线等。

[liuyilin888]

Linq早晚有一天被微软抛弃

[DengWanLong]

那现在也得用啊

[q107770540]

var query=from ep in JHExamPaper
          join p in Paper on ep.PaperId equals p.Id
          join s in Subject on p.SubjectId equals s.Id
          join eps in ExamPaperStudent on ep.PaperId equals eps.PaperId into leftJoinGroup
          from eps in leftJoinGrou.DefaultIfEmpty()
          join r in Report on eps.Id equlas r.EPSId into Left
          from r in Left.DefalutIfEmpty()
          where eps.ExamId=="B39A5BEE-8021-4A0D-8CC2-4BDEFC580579" && eps.UserId=="110813D3-AF1D-41A2-9F39-B44604CA2BC6"
          select new 
          {
            p.Id,r.Id,s.Id,s.Name,p.Title,p.TotalScore 
          };

[q107770540]

[Quote=引用 1 楼 的回复:]
Linq早晚有一天被微软抛弃
[/Quote]
与其在这危言耸听，不如好好回几个帖子，帮助解决问题的好
比如你买了件衣服，反正早晚有一天会过时或坏掉，难道因为这个，就不穿这件衣服了么

[DengWanLong]

[Quote=引用 3 楼 的回复:]

C# code

var query=from ep in JHExamPaper
join p in Paper on ep.PaperId equals p.Id
join s in Subject on p.SubjectId equals s.Id
join eps in ExamPaperStudent on ep.Pape……
[/Quote]
我把他改了下
var query = from ep in dc.GetTable<JHExamPaper>()
join p in dc.GetTable<Paper>() on ep.PaperId equals p.Id
join s in dc.GetTable<Subject>() on p.SubjectId equals s.Id
join eps in dc.GetTable<ExamPaperStudent>() on ep.PaperId equals eps.PaperId into leftJoinGroup
from eps in leftJoinGroup.DefaultIfEmpty()
join r in dc.GetTable<Report>() on eps.Id equals r.EPSId into Left
from r in Left.DefaultIfEmpty()
where eps.ExamId == JHExamId && eps.UserId == UserId
select new JHExamPaperDTO
{
PaperId = p.Id,
ReportId = r.Id == null ? Guid.Empty : r.Id,
SubjectId = s.Id,
PaperName = p.Title,
SubjectName = s.Name,
PaperScore = p.TotalScore
};
执行结果是不对的
下面是SQL语句
SELECT [t1].[Id] AS [PaperId],
(CASE
WHEN ([t4].[Id]) IS NULL THEN @p2
ELSE [t4].[Id]
END) AS [ReportId], [t2].[Id] AS [SubjectId], [t1].[Title] AS [PaperName], [t2].[Name] AS [SubjectName], [t1].[TotalScore] AS [PaperScore]
FROM [JHExamPaper] AS [t0]
INNER JOIN [Paper] AS [t1] ON [t0].[PaperId] = [t1].[Id]
INNER JOIN [Subject] AS [t2] ON [t1].[SubjectId] = [t2].[Id]
LEFT OUTER JOIN [ExamPaperStudent] AS [t3] ON [t0].[PaperId] = [t3].[PaperId]
LEFT OUTER JOIN [Report] AS [t4] ON [t3].[Id] = [t4].[EPSId]
WHERE ([t3].[ExamId] = @p0) AND ([t3].[UserId] = @p1)

[Mirror然]

[Quote=引用 4 楼 的回复:]

引用 1 楼 的回复:
Linq早晚有一天被微软抛弃

与其在这危言耸听，不如好好回几个帖子，帮助解决问题的好
比如你买了件衣服，反正早晚有一天会过时或坏掉，难道因为这个，就不穿这件衣服了么
[/Quote]

[研发群187380948欢迎加入]

[Quote=引用 3 楼 的回复:]

C# code

var query=from ep in JHExamPaper
join p in Paper on ep.PaperId equals p.Id
join s in Subject on p.SubjectId equals s.Id
join eps in ExamPaperStudent on ep.Pape……
[/Quote]
我认为这个行 但是好像还可以用lambda写不知道那个效率高点

[DengWanLong]

感谢3楼，但是结果不对，其实主要问题就是 在左连接后面加的条件 怎么加到LinQ上去
在Where 后面会过滤掉的。

[你的选择H]

var result = from u in db.JHExamPaper
join n in db.Pager on u.PaperId equals n.Id
join m in db.Subject on n.SubjectId equals m.Id
join p in db.ExamPaperStudent on u.PaperId equals p.PaperId
join q in db.Report on p.Id equals q.EPSId
where p.ExamId = "B39A5BEE-8021-4A0D-8CC2-4BDEFC580579" && p.UserId = "110813D3-AF1D-41A2-9F39-B44604CA2BC6"
select new
{
n.Id,
q.Id,
m.Id,
m.Name,
n.TotalScore
};

[DengWanLong]

left join ExamPaperStudent as eps on ep.PaperId=eps.PaperId and eps.ExamId='B39A5BEE-8021-4A0D-8CC2-4BDEFC580579' and UserId='110813D3-AF1D-41A2-9F39-B44604CA2BC6'
主要是这句怎么用Linq 写出来
where p.ExamId = "B39A5BEE-8021-4A0D-8CC2-4BDEFC580579" && p.UserId = "110813D3-AF1D-41A2-9F39-B44604CA2BC6"
这么写是不对的 会过滤掉的

[DengWanLong]

var query = from ep in dc.GetTable<JHExamPaper>()
                        join p in dc.GetTable<Paper>() on ep.PaperId equals p.Id
                        join s in dc.GetTable<Subject>() on p.SubjectId equals s.Id
                        join eps in dc.GetTable<ExamPaperStudent>() on ep.PaperId equals eps.PaperId into leftJoinGroup
                        from eps in leftJoinGroup.Where(t => t.ExamId == JHExamId && t.UserId == UserId).DefaultIfEmpty()
                        join r in dc.GetTable<Report>() on eps.Id equals r.EPSId into Left
                        from r in Left.DefaultIfEmpty()
                        select new JHExamPaperDTO
                        {
                            PaperId = p.Id,
                            ReportId = r.Id == null ? Guid.Empty : r.Id,
                            SubjectId = s.Id,
                            PaperName = p.Title,
                            SubjectName = s.Name,
                            PaperScore = p.TotalScore
                        };

问题解决了，和大家分享下。

[DengWanLong]

leftJoinGroup.Where(t => t.ExamId == JHExamId && t.UserId == UserId).DefaultIfEmpty()

[camou_li]

SELECT B.VSL_NM, NVL(B.IN_VOY, 'NULL') || '/' || NVL(B.OUT_VOY, 'NULL') AS VESSEL_VOYAGE, B.IN_LANE || '/' || B.OUT_LANE AS SERVICE_LANE, B.ATD, T.* FROM (select T.SHIP_LOG_NO, SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'DEF' THEN 1 ELSE 0 END) AS DE_F, SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'DEE' THEN 1 ELSE 0 END) AS DE_E, SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'ESF' THEN 1 ELSE 0 END) AS ES_F, SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'ESE' THEN 1 ELSE 0 END) AS ES_E, SUM(CASE T.BMOVE_TYPE WHEN 'TB' THEN 1 WHEN 'BT' THEN 1WHEN 'BB' THEN 1 ELSE 0 END) AS RESTOW from pcatos.log_ship_move t where T.PTNR_CODE IN ('MSC') AND (T.BMOVE_TYPE = 'DE' OR T.BMOVE_TYPE = 'ES') GROUP BY T.SHIP_LOG_NO) T INNER JOIN (SELECT A.VSL_CD, A.CALL_YEAR, A.CALL_SEQ, A.ATD, A.IN_VOY, A.OUT_VOY, A.IN_LANE, A.OUT_LANE, VV.VSL_NM FROM PCATOS.TB_BERTHPLAN A LEFT JOIN PCATOS.TB_VSL VV ON A.VSL_CD = VV.VSL_CD) B ON B.VSL_CD || B.CALL_YEAR || B.CALL_SEQ = T.SHIP_LOG_NO WHERE B.ATD BETWEEN TO_DATE('2012-09-20 00:00:01', 'YYYY-MM-DD HH24:MI;SS') AND TO_DATE('2012-09-21 23:59:59', 'YYYY-MM-DD HH24:MI;SS') 请问这个怎么写成linq啊？各位大侠，帮忙解决下，谢谢

[随智阔]

var LeftJoin = from emp in ListOfEmployees join dept in ListOfDepartment on new { aa=emp.DeptID ,bb=emp.name} equals new {aa= dept.ID,bb=dept.name }into JoinedEmpDept from dept in JoinedEmpDept.DefaultIfEmpty() select new { EmployeeName = emp.Name, DepartmentName = dept != null ? dept.Name : null }; 左链接或右链接，使用DefaultIfEmpty()语法，采用些语法前需要用到into语法,两个表换位置实现右连接, 多条件是新的new {aa=****,bb=****}