SQL转LinQ

[叔到用时方恨嫂]

select 
a.code,
b.name,
(select top 1 isnull(desc,'') from tb_c where code = a.code and YEAR(keepdate) =YEAR(@date)  and MONTH(keepdate) = '1' ) 
as N'1月描述'
from tb_a a
left join tb_b b on a.id = b.id

主要是字段里是SQL语句，这linq弄了好久没弄出来，在线求解

[娃都会打酱油了]

from a in db.tb_a join b in db.tb_b on a.id equals b.id into j1 from b in j1.DefaultIfEmpty() join c in db.tb_c on a.code equals c.code into j2 from c in j2.DefaultIfEmpty() select new { a.code, name=b==null?"":b.name, desc=c==null?"":c.desc }

[娃都会打酱油了]

少了where部分了 将db.tb_c改成 db.tb_c.Where(i=>SqlFunctions.DatePart("year",i.keepdate)==2015 &&SqlFunctions.DatePart("month",i.keepdate)==1)

[叔到用时方恨嫂]

select 
a.code,
b.name,
(select top 1 isnull(desc,'') from tb_c where code = a.code and YEAR(keepdate) =YEAR(@date)  and MONTH(keepdate) = '1' ) 
as N'1月描述',
(select top 1 isnull(desc,'') from tb_c where code = a.code and YEAR(keepdate) =YEAR(@date)  and MONTH(keepdate) = '2' ) 
as N'2月描述',
(select top 1 isnull(desc,'') from tb_c where code = a.code and YEAR(keepdate) =YEAR(@date)  and MONTH(keepdate) = '3' ) 
as N'3月描述'
from tb_a a
left join tb_b b on a.id = b.id

这样能拼出来么

[娃都会打酱油了]

当然这个where加载最下面，join完之后也可以

[娃都会打酱油了]

你按上面的一样写啊，不过如果你这个是要行列转换的话，那么还是将所有数据都读取出来后，在程序里面进行group吧

[ajianchina]

加分之后怎么不回一下帖子，嘎嘎 int y = 2015; int[] mArr = { 1, 2, 3 };//月范围 var query = tb_a.GroupJoin(tb_b,a=>a.id,b=>b.id,(a,b)=>b.Select(p=>new{ a.code, p.name, 月描述 = tb_c.Select(s=>s.Year == y && mArr.Contains(s.Month)).ToDictionary(s => s.Month,s => s.desc) })).SelectMany(p=>p); foreach(var s in query) { Console.WriteLine(s.code+" "+s.name + "1月描述" + (s.月描述.ContainsKey(1) ? s.月描述[1] : "")); }

[ajianchina]

s.Year跟s.Month 都改成 s.keepdate.Year跟s.keepdate.Month 你再调试调试 这样查，月描述按字典出来，随你查几个月

[ajianchina]

月描述查询的那一段，跟你那个还少了个where code = a.code的条件，我全部重新写一下吧 int y = 2015; int[] mArr = { 1, 2, 3 };//月范围 var query = tb_a.GroupJoin(tb_b,a=>a.id,b=>b.id,(a,b)=>b.Select(p=>new{ a.code, p.name, 月描述 = tb_c.Select(s=>s.code == a.code && s.keepdate.Year == y && mArr.Contains(s.keepdate.Month)).ToDictionary(s => s.keepdate.Month,s => s.desc) })).SelectMany(p=>p);

[叔到用时方恨嫂]

我用的是Nhibernate,对LinQ有些不一定支持。 没办法用CreateSQLQuery,拼SQL语句了。

[ajianchina]

你建立视图，随后查询就变得简单了。

[ajianchina]

你这段sql语句能查询吗？isnull(desc,'')这个里面的保留字括号也没有，可以查询？ 你最好重开贴，加点分，你自己都搞了几天了，怎么才40分啊。