求一条linq语句。
0
[已解决问题]
解决于 2018-01-02 13:33
var expression = from d in this.db.MS_Deposit
join g in this.db.MS_Gold on d.Account equals g.Account
where d.IsDeposit == 1 && d.Status == 1 && g.GoldStatus == 11
&& d.CreateTime >= start && d.CreateTime <= end && g.CreateTime >= start && g.CreateTime <= end
select new { Account = d.Account, Deposit = d.MjMoney, Gold = g.MjMoney, CreateTime = d.CreateTime };
我是这样写的，这样不对。
this.db.MS_Deposit 按条件有1条数据。
条件为： d.IsDeposit == 1 && d.Status == 1 && d.CreateTime >= start && d.CreateTime <= end
this.db.MS_Gold 按条件有0条数据。
条件为：g.GoldStatus == 11 && g.CreateTime >= start && g.CreateTime <= end
期望查询出一条数据：Account=123, Deposit =50, Gold = 0, CreateTime = '2017-1-1'
最佳答案
0
你是要left join么？
on .... into tmp
from obj in tmp.DefaultIfEmpty()
where....
奖励园豆：5
var expression1 = from d in this.db.MS_Deposit
join g in this.db.MS_Gold on d.Account equals g.Account into gsItems
from gs in gsItems.DefaultIfEmpty()
where d.IsDeposit == 1 && d.Status == 1 && gs.GoldStatus == 11
&& d.CreateTime >= start && d.CreateTime <= end && gs.CreateTime >= start && gs.CreateTime <= end
select new { Account = d.Account, Deposit = d.MjMoney, Gold = gs.MjMoney, CreateTime = d.CreateTime };
还是查不到数据啊
@Eysa: 你把右表的where条件提到前面去
join g in MS_Gold.Where(g=>...)
on.....
@Daniel Cai:
var expression = from d in this.db.MS_Deposit.Where(d => d.IsDeposit == 1 && d.Status == 1 && d.CreateTime >= start && d.CreateTime <= end)
join g in this.db.MS_Gold.Where(g => g.GoldStatus == 11 && g.CreateTime >= start && g.CreateTime <= end) on d.Account equals g.Account
into gsItems
from gs in gsItems.DefaultIfEmpty()
select new { Account = d.Account, Deposit = d.MjMoney, Gold = gs.MjMoney, CreateTime = d.CreateTime };
报错了，说是什么decimal类型不能从null转换什么的
@Eysa: 不是这样的
from a in tbA join b in tbB.Where(item=>item.condition....)
on a... equals b..... into tmp
where a.condition.....
from c in tmp.DefaultIfEmpty()
select new {pro1=c.pro1,pro2=c.pro2....};
@Daniel Cai: 到值类型“System.Decimal”的强制转换失败，因为具体化值为 null。结果类型的泛型参数或查询必须使用可以为 null 的类型。
var expression = from d in this.db.MS_Deposit
join g in this.db.MS_Gold.Where(g => g.GoldStatus == 11 && g.CreateTime >= start && g.CreateTime <= end) on d.Account equals g.Account
into gsItems
where d.IsDeposit == 1 && d.Status == 1 && d.CreateTime >= start && d.CreateTime <= end
from gs in gsItems.DefaultIfEmpty()
select new { Account = d.Account, Deposit = d.MjMoney, Gold = gs.MjMoney, CreateTime = d.CreateTime };
@Eysa: 最后你是从gs中拿数据，不是从你前面的两个对象中抽数据
@Daniel Cai: 这个gs貌似只有MS_Gold的数据啊，那我要MS_Deposit的怎么办？
@Eysa: 。。。。
两个表left join，你拿左表从from d in this.db.MS_Deposit这里的d中拿
d.Account....
右表已经into到gsItems，那么右表当然是从from gs in gsItems.DefaultIfEmpty()中的gs中拿（需要判断gs是否为空才能拿取其属性信息）
