作为一个初学者,这个句子花了我好大的力气才写出来,写出来是次要的,重要的是又学到了一点新的用法。
var query = from gb in ObjectContext.goodsclose_back
where gb.s_sf == comId && gb.s_month == month
join gw in //类似子查询
(
from ghb in ObjectContext.goodsclose_history_caiwu_1
where ghb.s_month == lastmonth
join w in ObjectContext.waitfinancein_import
//new { ghb.s_goodsid, ghb.s_sf, s_month = month } 组合匹配,前后字段的名字要一样
on new { ghb.s_goodsid, ghb.s_sf, s_month = month } equals new { w.s_goodsid, w.s_sf, s_month = w.s_month } into t //左连接, 这里不用DefaultIfEmpty()的话,就相当于左边一条数据对应右边几条,这几条作为个集合存在,用了就打散开来左边重复一条数据几次来对应右边的数据。
select new //GoodsCloseFinanceBackPM
{
//这里放s_goodsid、s_sf、s_month是为了下面用,这里不写的话,下面就调用不到
s_goodsid = ghb.s_goodsid,
s_sf = ghb.s_sf,
s_month = month,
CurrentWaitinqty = t.Sum(x => (x.f_importqty ?? 0) - (x.f_inv_qty ?? 0)),
CurrentWaitinamt = t.Sum(x => (x.f_importqty ?? 0) * (x.f_importpri ?? 0) - (x.f_inv_qty ?? 0) * (x.f_importpri ?? 0)),
OldCloseqty = ghb.f_closeqty ?? 0,
OldCloseamt = ghb.f_closeamt ?? 0,
Oldpri = ghb.f_closepri ?? 0,
OldWaitinqty = ghb.f_current_waitfinanceinqty ?? 0,
OldWaitinamt = ghb.f_current_waitfinanceinamt ?? 0
}
) on new { gb.s_goodsid, gb.s_sf, gb.s_month } equals new { gw.s_goodsid, gw.s_sf, gw.s_month } into gwt
from gw in gwt.DefaultIfEmpty()
select new GoodsCloseFinanceBackPM
{
GoodsId = gb.s_goodsid,
ComId = comId,
Month = month,
Closeqty = gb.f_closeqty ?? 0,
Purpri = gb.f_purpri ?? 0,
Inqty = gb.f_inqty ?? 0,
Outqty = gb.f_outqty ?? 0,
Transeinqty = gb.f_transinqty ?? 0,
Transeoutqty = gb.f_transoutqty ?? 0,
Unit = gb.s_unit,
Oldpri = gw.Oldpri,
CurrentWaitinqty = gw.CurrentWaitinqty,
CurrentWaitinamt = gw.CurrentWaitinamt,
OldCloseqty = gw.OldCloseqty,
OldCloseamt = gw.OldCloseamt,
OldWaitinqty = gw.OldWaitinqty,
OldWaitinamt = gw.OldWaitinamt
};