使用LINQ多次联接后从表中获取Sum数据 [英] Get data with Sum from a table after multiple joining using LINQ
问题描述
我有EDMX:
如何从FreelancerPayment表中获取单个Freelancer金额的总和?如果自由职业者是新的,那么FreelancerPayment表始终没有Amount.我只想列出自由职业者的详细信息和总收入. 我试过了:
How can i get sum of Amount of single Freelancer from FreelancerPayment table if has? All time FreelancerPayment table doesn't has Amount if the freelancer is new. I just want list of freelancers with their details and Total earn. I tried this:
var freelancers = (from fl in db.FreelancerLogins
join f in db.Freelancers
on fl.FreelancerID equals f.FreelancerID
select new
{
FreelancerID = fl.FreelancerID,
UserName = fl.UserName,
EmailAddress = fl.EmailAddress,
EmailConfirmed = fl.EmailConfirmed,
Status = fl.Status,
LogInTime = fl.LogInTime,
LogOutTime = fl.LogOutTime,
Picture = f.Picture,
Title = f.Title,
Name = f.FirstName + " " + f.LastName,
Overview = f.Overview,
JoiningDate = f.JoiningDate,
BirthDay = f.BirthDay,
Rate = f.Rate,
Location = f.Location,
//Earn = (fp.Amount == null ? 0 : fp.Amount)
Earn=(
from fla in db.Freelancers
join apl in db.AppliedJobs
on fla.FreelancerID equals apl.FreelancerID into apll
from ap in apll.DefaultIfEmpty()
join jo in db.JobOffers
on ap.AppliedJobID equals jo.AppliedJobID into joo
from jobo in joo.DefaultIfEmpty()
join c in db.Contracts
on jobo.OfferID equals c.OfferID into coo
from con in coo.DefaultIfEmpty()
join fpay in db.FreelancerPayments
on con.ContractID equals fpay.ContractID into fpayy
from fp in fpayy.DefaultIfEmpty()
select fp.Amount).Sum()
}).AsEnumerable();
return freelancers.AsEnumerable();
但是它返回:
所有金额相同.如何为特定的自由职业者放置where子句?
Amount same for all. How can i put where clause for specific freelancer?
推荐答案
内部查询应以连接到外部查询Freelancer
的AppliedJobs
开头:
Your inner query should start with AppliedJobs
connected to the outer query Freelancer
:
Earn = (
from ap in db.AppliedJobs
where f.FreelancerID == ap.FreelancerID
join jo in db.JobOffers
...
此外,您不需要在子查询中计算出总和的左外部联接.
Also you don't need left outer joins inside subquery that calculates the sum.
最后,您的操作方式使其变得比应有的复杂.您拥有不错的导航属性,因此不要使用Linq的Join .导航!,您将不会遇到此类问题:
Finally, the way you are doing it makes it more complicated than it should be. You have nice navigation properties, so Don’t use Linq’s Join. Navigate! and you'll have no such issues:
var query =
from f in db.Freelancers
let fl = f.FreelancerLogin
select new
{
FreelancerID = fl.FreelancerID,
UserName = fl.UserName,
// ...
Earn = (from ap in f.AppliedJobs
from jo in ap.JofOffers
from c in jo.Contracts
let fp = c.FreelancerPayment
select fp.Amount).DefaultIfEmpty().Sum()
};
这篇关于使用LINQ多次联接后从表中获取Sum数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!