linq 获取当月数据的记录数

stszievb  于 2023-09-28  发布在  其他
关注(0)|答案(1)|浏览(140)

我试图在我的数据库中查找当月结束的车辆记录总数。我不知道在这种情况下应该在InvoiceDate部分写什么。

public void MonthlyStatus(NetContext context)
{
    var monthlyStatus = (from car in context.CarInformation
        where car.CurrentSituation== "Finish" && car.InvoiceDate == 
        select car);

    if (monthlyStatus == null)
    {
        label1.Text = "0";
    }
    else
    {
        label1.Text = Convert.ToString(monthlyStatus);
    }     
}
kkbh8khc

kkbh8khc1#

您可以根据MonthYear提供要过滤的查询。

var monthlyCount = (from car in context.CarInformation
    where car.CurrentSituation== "Finish" 
        && car.InvoiceDate.Month == DateTime.Now.Month
        && car.InvoiceDate.Year == DateTime.Now.Year 
    select car)
    .Count();

label1.Text = Convert.ToString(monthlyCount);

请注意,如果没有materialize (immediate execution) the query,则不会执行当前的LINQ查询。
注意,COUNT查询可以用Count<TSource>(IEnumerable<TSource>, Func<TSource,Boolean>)的方法语法编写:

var monthlyCount = context.CarInformation
    .Count(car => car.CurrentSituation== "Finish" 
        && car.InvoiceDate.Month == DateTime.Now.Month
        && car.InvoiceDate.Year == DateTime.Now.Year);

正如@Svyatoslav Danyliv所建议的,要通过提供日期范围过滤器来利用索引用法,应该是:

DateTime now = DateTime.Now;
DateTime startDate = new DateTime(now.Year, now.Month, 1);
DateTime endDate = startDate.AddMonth(1);

var monthlyCount = (from car in context.CarInformation
    where car.CurrentSituation== "Finish" 
        && car.InvoiceDate >= startDate
        && car.InvoiceDate < endDate 
    select car)
    .Count();

相关问题