LINQ To SQL 中聚合函数的使用问题

最新推荐文章于 2022-04-28 11:35:59 发布

原创最新推荐文章于 2022-04-28 11:35:59 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#linq #sql #null #object

ASP.NET 同时被 2 个专栏收录

39 篇文章

订阅专栏

34 篇文章

订阅专栏

本文介绍了一种根据指定用户ID及日期范围进行捐赠统计的方法，通过LINQ查询从数据库中获取不同捐赠类别下的总额，并生成统计数据。

internal static object SelectDonationStatByDate(int endUserID, DateTime startDate, DateTime endDate)
    {
        GoodTreeDataContext db = new GoodTreeDataContext();
        IList<DonationStat> donationStats = new List<DonationStat>();
        DonationStat donationStat = null;

        var q = from p in db.Donation
                where p.EndUserID == endUserID
                group p by p.DonationCategory into g
                select new
                {
                    DonationCategory = g.Key,
                    TotalAmount = g.Where(i => Convert.ToDateTime(i.Date) >= startDate & Convert.ToDateTime(i.Date) <= endDate).Sum(d => d.Amount) == null ? 0 : g.Where(i => Convert.ToDateTime(i.Date) >= startDate & Convert.ToDateTime(i.Date) <= endDate).Sum(d => d.Amount)
                };

        int j = 1;
        foreach (var item in q)
        {
            donationStat = new DonationStat();
            donationStat.DonationCategory = item.DonationCategory;
            donationStat.TotalAmount = item.TotalAmount;
            donationStat.StartDate = startDate;
            donationStat.EndDate = endDate;
            donationStat.ID = j;

            donationStats.Add(donationStat);
            j++;
        }

return donationStats;
}

        var q = from p in db.Donation
                group p by p.DonationCategory into g
                select new
                {
                    DonationCategory = g.Key,
                    TotalAmount = g.Where(i => Convert.ToDateTime(i.Date) >= startDate & i.EndUserID == endUserID & Convert.ToDateTime(i.Date) <= endDate).Sum(d => d.Amount) == null ? 0 : g.Where(i => Convert.ToDateTime(i.Date) >= startDate & Convert.ToDateTime(i.Date) <= endDate).Sum(d => d.Amount)
                };