[c#]内部和左联接在一起的 Linq 到 SQL

标签: Linq SQLServer C#
发布时间: 2017/2/26 21:27:27
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我的 sql 查询

SELECT T.TicketId,
         T.Subject,
         T.Issue,
         T.Priority,
         T.StatusId,
         T.Attachment,
         T.CreatedDate,
         T.ModifiedDate,
         T.Comment,
         U.Username as CreatedBy,
         TU.Username as Assigned
  FROM   tblTicket T
         INNER JOIN tblUser U
                    ON T.CreatedBy = U.UserId
         LEFT JOIN tblUser TU
                    ON T.AssignTo = TU.UserId

我有试着转换在 linq 中,查询下

from ticket in tblTickets
join user in tblUsers
       on     ticket.CreatedBy equals user.UserId
into temp from tblUsers in temp.DefaultIfEmpty()
select new
{
       ticket.TicketId,
       ticket.Subject,
       ticket.Issue,
       ticket.Priority,
       ticket.StatusId,
       ticket.Attachment,
       ticket.CreatedDate,
       ticket.ModifiedDate,
       ticket.Comment,
       user.Username
}

我越来越低于 linq 垫中的错误

范围变量 'tblUsers' 与 'tblUsers' 以前声明的冲突

我知道我在为左但变量名用户重复表和临时我的意思是不一样所以为什么我得到这种错误和什么是为它的正确的 linq 查询。

解决方法 1:

正如错误暗示,正在 Linq2SQL 查询中重用相同的变量名。

请注意︰ 在 Linq 语法中 from item in tablejoin item2 in table2 ... 两个 item & item2 是新的变量声明。

这样的事情应该修复它。看我如何替换第二个使用 tblUsers 与一个新的变量 userAssignedLeft

from ticket in tblTickets
join user in tblUsers on ticket.CreatedBy equals user.UserId
join userAssigned in tblUsers on ticket.AssignTo equals userAssigned.UserId into temp 
from userAssignedLeft in temp.DefaultIfEmpty()
select new
{
       ticket.TicketId,
       ticket.Subject,
       ticket.Issue,
       ticket.Priority,
       ticket.StatusId,
       ticket.Attachment,
       ticket.CreatedDate,
       ticket.ModifiedDate,
       ticket.Comment,
       UsernameCreatedBy = user.Username,
       UsernameAssigned = userAssignedLeft.Username
}    
官方微信
官方QQ群
31647020