使用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屋！