[LINQ] group by 与连接查询

//副表 树种-品名-折材率 汇总
var listNeed = (from t in dtNeed.AsEnumerable()
                group t by new { t1 = t.Field<string>("SZ"), t2 = t.Field<string>("PM") } into m
                select new
                {
                    SZ = m.Key.t1,
                    PM = m.Key.t2,
                    Need = m.Sum(n => n.Field<decimal>("Need"))
                }).ToList();
//台账 树种-品名-结余量 汇总
var listSY = (from t in dtStorage.AsEnumerable()
                group t by new { t1 = t.Field<string>("SZNo").Substring(0,4), t2 = t.Field<string>("CZId") } into m
                select new
                {
                    SZ = m.Key.t1,
                    PM = m.Key.t2,
                    SY = m.Sum(n => n.Field<decimal>("SY"))
                }).ToList();
//所需结余对比 树种-品名-副表所需-台账结余
var listAll = (from dn in listNeed
                join ds in listSY
                    on new { dn.SZ, dn.PM } equals new { ds.SZ, ds.PM }
                    into joined
                    from temp in joined.DefaultIfEmpty()
                select new
                {
                    SZ = dn.SZ,
                    PM = dn.PM,
                    Need = dn.Need,
                    SY = temp == null ? 0 : temp.SY
                }).ToList();

结果：

 

http://blog.sina.com.cn/s/blog_46e9573c01014fx2.html

1、左连接：
var LeftJoin = from emp in ListOfEmployees
join dept in ListOfDepartment
on emp.DeptID equals dept.ID into JoinedEmpDept
from dept in JoinedEmpDept.DefaultIfEmpty()
select new                        
{
EmployeeName = emp.Name,
DepartmentName = dept != null ? dept.Name : null                        
};
 
2、右连接：
var RightJoin = from dept in ListOfDepartment
join employee in ListOfEmployees
on dept.ID equals employee.DeptID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new                          
{
EmployeeName = employee != null ? employee.Name : null,
DepartmentName = dept.Name
};
 
3、内连接：
 var query = from t in entitiy.TB_GCGL_ADA_USER
                 join p in entitiy.TB_GCGL_ZY_ZYK
                 on t.ETPRS_CODE equals p.ETPRS_CODE
                 select new TB_USER_ZYK
                 {
                    USER_ID = t.USER_ID,
                    USER_NAME = t.USER_NAME,
                    USER_PASSWORD = t.USER_PASSWORD,
                 };