Get the number of records of data in the current month

Question

I am trying to find the total number of records of the vehicles that ended in the current month in my database. I do not know what I should write in the InvoiceDate section in this case.

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);
    }     
}

Answer 1

You can provide the query to filter based on Month and Year.

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);

Note that, your current LINQ query is not executed without materialize (immediate execution) the query.

Side note, the COUNT query can be written in method syntax with 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);

---

As suggested by @Svyatoslav Danyliv, to utilize the index usage by providing the date range filter, it should be:

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();