日期:2014-05-20  浏览次数:20848 次

菜鸟求一Linq表达式
下面的SQL 改成用 Linq 实现。

SELECT
 c.id,
 c.CustomersName as name,
 f.FollowUpTheWay as names,
 f.NextFollowDate,
 f.FollowDate,
 f.id as fid
 from dbo.Slgl_CustomerReception as c 
 left join dbo.Slgl_FollowUpRecords as f on 
f.id = (select TOP(1) id from dbo.Slgl_FollowUpRecords where CustomersId = c.id and FollowDate <= '2011-7-20' order by id DESC)
where c.ProjectId = 9

简要写下就可以了 主要小括号里面的内容 不知道怎么转

------解决方案--------------------
C# code


var query=from c in Slgl_CustomerReception 
          where c.ProjectId==9
          let id=Slgl_FollowUpRecords.Where(r=>r.CustomerId==c.id
                  && r.FollowDate<=Convert.ToDateTime("2011-7-20"))
                 .OrderByDescending(r=>r.id).First().id
          join f in Slgl_FollowUpRecords
          on f.id equals id into g
          from f in g.DefaultIfEmpty()
          select new 
           {
              id=c.id,
              name=c.CustomersName,
              names=f==null?"":f.FollowUpTheWay,
              FollowDate=f==null?DateTime.Now:f.FollowDate,
              fid=f==null?0:f.id
            };