多表LINQ写法

[落阳]

业务表T：

[Key]
        public int ID { get; set; }

        [Display(Name = "员工")]
        [Required(ErrorMessage = "*")]
        public string StaffNo { get; set; }

        [Display(Name = "调动时间")]
        [Required(ErrorMessage = "*")]
        public DateTime TransferDate { get; set; }

        [Display(Name = "调出公司")]
        [Required(ErrorMessage = "*")]
        public int OutCompanyID { get; set; }

        [Display(Name = "调出部门")]
        [Required(ErrorMessage = "*")]
        public int OutDeptID { get; set; }

        [Display(Name = "调出岗位")]
        [Required(ErrorMessage = "*")]
        public int OutPositionID { get; set; }

       

        [Display(Name = "调入公司")]
        [Required(ErrorMessage = "*")]
        public int InCompanyID { get; set; }

        [Display(Name = "调入部门")]
        [Required(ErrorMessage = "*")]
        public int InDeptID { get; set; }

        [Display(Name = "调入岗位")]
        [Required(ErrorMessage = "*")]
        public int InPositionID { get; set; }

岗位表P：

[Key]
        public int ID { get; set; }

        [Display(Name = "岗位名称")]
        [Required(ErrorMessage = "*")]
        [StringLength(15, ErrorMessage = "不能超过15个字符")]
        public string Name { get; set; }

        [Display(Name = "所属部门")]
        public int DeptID { get; set; }

        [ForeignKey("DeptID")]
        public virtual BaseOrganization Organization { get; set; }

部门表D：

[Key]
        public int ID { get; set; }

        [Display(Name = "机构名称")]
        [Required(ErrorMessage = "*")]
        public string Name { get; set; }

        [Display(Name = "机构编号")]
        [Required(ErrorMessage = "*")]
        public string Code { get; set; }

        [Display(Name = "排序号")]
        [Required(ErrorMessage = "*")]
        public int SortNumber { get; set; }

        [Display(Name = "所属机构")]
        [Required(ErrorMessage = "*")]
        public int ParentID { get; set; }

其中，岗位表与部门表级联。
部门表通过Code来快速区分父子级关系，如父级Code为01，则子集第一层0101,0102，再父级的Code基础上加上2位的数字。
Code位数为2位的表示子公司。

现在我要实现以下SQL：

select a.ID,a.StaffNo,
a.OutPositionID,b.Name as OutPositionName,b.DeptID as OutDeptID,c.Name as OutDeptName,
d.ID as OutCompanyID,d.Name as OutCompanyName,
a.InPositionID,e.Name as InPositionName,e.DeptID as InDeptID,f.Name as InDeptName,
g.ID as InCompanyID,g.Name as InCompanyName
from 
T a
left join P b on a.OutPositionID = b.ID
left join D c on b.DeptID = c.ID
left join D d on substring(c.Code,0,3) = d.Code

left join P e on a.InPositionID = e.ID
left join D f on e.DeptID = f.ID
left join D g on substring(f.Code,0,3) = g.Code

请教大神，linq怎么写呢？
没多少分了，刚好20，希望大神不要走。。

[人傻且呆但不萌]

多表Linq left join代码参考

[q107770540]

var query=from a in T
          join b in P on a.OutPositionID equals b.ID into left
          from b in left.DefaultIfEmpty()
          join c in D on b.DeptID equals c.ID into left1
          from c in left1.DefaultIfEmpty()
          join d in D on c.Substring(0,3) equals d.Code into left2
          from d in left2.DefaultIfEmpty()
          //后边你懂的
          

[落阳]

引用 1 楼 nice_fish 的回复:

多表Linq left join代码参考

具体的join方式我会，我要的是我的问题的答案。 因为P表和D表有级联关系，且要用到substring函数。

[落阳]

引用 2 楼 q107770540 的回复:

本帖最后由 q107770540 于 2013-03-25 21:05:13 编辑 C# code?123456789var query=from a in T join b in P on a.OutPositionID equals b.ID into left from b in left.Defa……

猫神，join d in D on c.Substring(0,3) equals d.Code into left2这句是不是少了Code啊，还有，substring的话，我得必须先toList掉是哇。

[q107770540]

刚刚特地用LINQ 2 EF，试了一下，还好，EF是支持substring的 不用ToList()

[落阳]

原来如此，多谢猫神，一如既往地强大，结贴晚安～