这是我的Linq-to-sql
查询:
var ds = (from f in dc.Fields
from l in dc.Units.Where( ltu => ltu.TestID == f.Testid && ltu.tid == tid && ltu.Lbid != null).DefaultIfEmpty()
from s in dc.Sites.Where( st => st.lbid == l.Lbid).DefaultIfEmpty()
where f.tid == tid && f.tablename == e.Parameters[0].Value && f.tablename.Contains(tableprefix)
orderby f.fieldorder
select new { ID = f.id, Units = "Units: " + l.Unit + "For Sites: " + s.siteid}
我如何更改代码,因此,如果一个字段具有多个单元和站点,则选择所有字段,而不仅仅是第一个。
**Fields**
ID | Testid | fieldorder | tablename | tid
0 | test1 | 1 | tbl1 | 100
1 | test2 | 2 | tbl2 | 100
**Units**
ID | TestID | tid | Lbid | Unit
0 | test1 | 100 | Lb1 | m/s
1 | test1 | 100 | Lb1 | km/s
**Sites**
ID | siteid | lbid
0 | 100 | Lb1
1 | 200 | Lb1
所以我想得到的是:
id = 0,units ="单位:" M/s,km/s"用于网站:" 100,200
我不确定您的查询是否会返回您期望的结果(我从中获得了四行(,但是这是通过字段组合行的修改。ID:<:
var ds = from f in dc.Fields
from l in dc.Units.Where(ltu => ltu.TestID == f.Testid && ltu.tid == tid && ltu.Lbid != null).DefaultIfEmpty()
from s in dc.Sites.Where(st => st.lbid == l?.Lbid).DefaultIfEmpty()
where f.tid == tid && f.tablename == e.Parameters[0].Value && f.tablename.Contains(tableprefix)
group new { f, l, s } by f.ID into flsg
orderby flsg.First().f.fieldorder
select new { ID = flsg.Key, Units = "Units: " + String.Join(",", flsg.Select(fls => fls.l?.Unit)) + " For Sites: " + String.Join(",", flsg.Select(fls => fls.s?.siteid)) };