求一SQL语句，在EF中写法

[lizhengqin]

select n.* ,b.FileName from NewsInfo n left join

(select fid,filename from brandfile where typeid=2 and fileid in (select max(fileid) from brandfile group by fid) ) b

on n.newsid=b.fid

[机器人]

var maxids = brandfile.GroupBy(x => x.fid).Select(g => g.Max(x => x.fieldid));
var b = brandfile.Where(x => x.typeid == 2 && maxids.Contains(x => x.fileid));
var result = from n in NewsInfo 
             join b in brandfile on n.newsid equals b.fid into n1
             from n2 in n1.DefaultIfEmpty()
             select new 
             {
                NewsInfo = n,
                FileName = b.filename
             };
             

[lizhengqin]

感谢2位，调试一直不成功，只好用存储过程了

[q107770540]

[Quote=引用楼主 的回复:]
select n.* ,b.FileName from NewsInfo n left join

(select fid,filename from brandfile where typeid=2 and fileid in (select max(fileid) from brandfile group by fid) ) b

on n.newsid=b.fid
[/Quote]

var query=from n in db.NewsInfo
          let m=db.brandfile.GroupBy(y=>y.fid).Select(g=>g.Max(z=>z.fieldid)
          join b in  db.brandfile.Where(x=> x.typeid==2 && 
                m.Contains(x=>x.fieldid))
          on n.newsid equals b.fid into Left
          from b in Left.DefalutIfEmpty()
          select new {n,FileName=b==null?"":b.FileName};