LINQ to SQL：为提供的范围内的每个日期选择计数行？

发布于 2024-12-11 18:07:44 字数 736 浏览 0 评论 0原文

我有一个表 Account，其中有一列名为 CreatedOn（可为空的日期时间）。

我正在编写一个方法，该方法接受两个参数 from 和 to （DateTime 对象），并且我想选择 from 和 to 之间的所有日期以及 T 中在每个日期创建的行数。

如何在 LINQ 中以优雅且有效的方式做到这一点？

我尝试这样做，但似乎为列表日期中的每个日期查询数据库一次：

static void Test(DataClasses1DataContext context, DateTime from, DateTime to)
{
    List<DateTime> dates = new List<DateTime>();
    while (from <= to)
    {
        dates.Add(from.Date);
        from = from.AddDays(1);
    }

    var result = dates.Select(d => new { Date = d, Count = context.Accounts.Count(a => a.CreatedOn.HasValue && a.CreatedOn.Value.Date == d) });
    foreach (var row in result)
            Console.WriteLine(row.Date + "   " + row.Count);
}

原文

I have a table Account with a column called CreatedOn (nullable datetime).

I'm writing a method that takes two parameters from and to (DateTime objects) and I want to select all the dates between from and to along with the number of rows in T that where created on each of those dates.

How can I do this in an elegant and effective way in LINQ?

I tried to do it this way, but it seems to query the database once for each date in the list dates:

static void Test(DataClasses1DataContext context, DateTime from, DateTime to)
{
    List<DateTime> dates = new List<DateTime>();
    while (from <= to)
    {
        dates.Add(from.Date);
        from = from.AddDays(1);
    }

    var result = dates.Select(d => new { Date = d, Count = context.Accounts.Count(a => a.CreatedOn.HasValue && a.CreatedOn.Value.Date == d) });
    foreach (var row in result)
            Console.WriteLine(row.Date + "   " + row.Count);
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

み零 2024-12-18 18:07:44

针对评论进行了更新

我不会说它很优雅，但它只查询数据库一次。

static void Test(DataClasses1DataContext context, DateTime fromDate, DateTime toDate)
{
    var result = context.Accounts
                .Where(p => p.CreatedOn >= fromDate && p.CreatedOn <= toDate)
                .GroupBy(x => x.CreatedOn.Date)
                .Select(x => new {
                   dt = x.Key,
                   count = x.Count()});
}

List<DateTime> dates = new List<DateTime>();
while (fromDate <= toDate)
{
    dates.Add(fromDate.Date);
    fromDate = fromDate.AddDays(1);
}    

var allDates = dates.Select(x => new {
                    dt = x,
                    count = 0});

// Merge both lists and then filter to include highest count
var result = rows.Concat(allDates)
                 .GroupBy(x => x.dt)
                 .Select(x => new {
                    dt = x.Key,
                    count = x.OrderByDescending(c => c.count)
                             .FirstOrDefault().count});

Updated in Response to comment

I wouldn't say its elegant but it does only query the database once.

static void Test(DataClasses1DataContext context, DateTime fromDate, DateTime toDate)
{
    var result = context.Accounts
                .Where(p => p.CreatedOn >= fromDate && p.CreatedOn <= toDate)
                .GroupBy(x => x.CreatedOn.Date)
                .Select(x => new {
                   dt = x.Key,
                   count = x.Count()});
}

List<DateTime> dates = new List<DateTime>();
while (fromDate <= toDate)
{
    dates.Add(fromDate.Date);
    fromDate = fromDate.AddDays(1);
}    

var allDates = dates.Select(x => new {
                    dt = x,
                    count = 0});

// Merge both lists and then filter to include highest count
var result = rows.Concat(allDates)
                 .GroupBy(x => x.dt)
                 .Select(x => new {
                    dt = x.Key,
                    count = x.OrderByDescending(c => c.count)
                             .FirstOrDefault().count});

回复收藏 0 原文

~没有更多了~