C# linq left join 操作表和datatable

C# linq left join 操作表和datatable

https://blog.csdn.net/The_flying_pig/article/details/79568072

操作表:

现有sfcusn表,sfcmo表,为保证sfcusn表数据的完整使用left join 查询。结构如下

C# linq left join 操作表和datatable

sql语句

  1. ---oracle写法
  2. SELECT A.usn,A.mo,A.upn,A.stage,B.line FROM sfcusn A,sfcmo B WHERE A.mo = B.mo(+)

linq语句

  1. var query= from A in db.sfcusn
  2. join B in db.sfcmo on A.mo equals B.mo into temp
  3. from t in temp.DefaultIfEmpty()
  4. select new
  5. {
  6. usn = A.usn,
  7. mo = A.mo,
  8. upn = A.upn,
  9. stage = A.stage,
  10. line = t==null?"":t.line //判断第二集合中可能为空
  11. };

操作datatable:

现需对两个datatable进行left join查询,结构如下

C# linq left join 操作表和datatable

这里需要用到A,B中三个栏位。

如果是查询表用sql写当然是很简单了,可以仿看上面的sql写出来。

linq在操作datatable,注意字段的类型。

  1. var query = from aa in A.AsEnumerable()
  2. join bb in B.AsEnumerable()
  3. on new { USERID = aa.Field<string>("USERID"), TRNDATE = aa.Field<string>("TRNDATE"), MODEC = aa.Field<string>("MODEC") }
  4. equals new { USERID = bb.Field<string>("USERID"), TRNDATE = bb.Field<string>("TRNDATE"), MODEC = bb.Field<string>("MODEC") }
  5. into temp
  6. from tt in temp.DefaultIfEmpty()
  7. select new
  8. {
  9. USERID = aa.Field<string>("USERID"),
  10. TOTAL = tt == null ? 0 : tt.Field<decimal>("TOTAL"),
  11. ECOUNT = aa.Field<decimal>("ECOUNT") == null ? 0 : aa.Field<decimal>("ECOUNT"),
  12. RATE = (100 * (tt == null ? 1 : (tt.Field<decimal>("TOTAL") == 0 ? 1 : aa.Field<decimal>("ECOUNT") / tt.Field<decimal>("TOTAL")))).ToString("#0.00") + "%",
  13. TRNDATE = a.Field<string>("TRNDATE") == null ? "" : a.Field<string>("TRNDATE"),
  14. MODEC = aa.Field<string>("MODEC") == null ? "" : aa.Field<string>("MODEC")
  15. };
最后希望对大家有帮助谢谢!




上一篇:go语言一个包含nil指针的接口不是nil接口的理解


下一篇:[PTA] 习题4-4 特殊a串数列求和