Counting active listings by month and year-CodePudding

I am currently working with real-estate data, and each Listing entity has a ListingDate and a CloseDate. What I am currently trying to do is to count how many Listings are active in a given month and year (group by year and month).

So for an example if Listing1 had an ListingDate of 05/01/2020 and a CloseDate of 08/01/2020, there would be 1 Active count for May, June, July, and August and a year total of 4.

I am using EF and LINQ, and was wondering if I could solve it somehow.

Any help or advice is appreciated.

CodePudding user response：

Sure you can; if you map listings to each month in which it's active, you can then simply group the results by month and get the counts trivially. Thus, the trickiest part is to just come up with the month DateTime values, which isn't that tricky.

Extension method to get month DateTimes from a start and end date:

public static IEnumerable<DateTime> GetMonths(this DateTime startDate, DateTime endDate)
{
    var monthDiff = (endDate.Month - startDate.Month)   (12 * (endDate.Year - startDate.Year));
    var startMonth = new DateTime(startDate.Year, startDate.Month, 1);
    
    return Enumerable.Range(0, monthDiff   1)
        .Select(i => startMonth.AddMonths(i));
}

Create lookup:

var listingsByMonth = listings
    .SelectMany(l =>
        {
            return l.ListingDate.GetMonths(l.ClosingDate.AddDays(-1)) // assuming closing date is exclusive
                .Select(dt => new KeyValuePair<DateTime, Listing>(dt, l));
        })
    .ToLookup(kvp => kvp.Key, kvp => kvp.Value);

Demonstration of results:

foreach(var g in listingsByMonth)
{
    Console.WriteLine($"{g.Key:yyyy-MM}: {g.Count()}");
}

Fiddle

CodePudding user response：

Let's assume that date is given in DateTime structs. (You can parse text input to DateTime, check this) We can iterate over a List containing Listing entities, and perform a check to see if given date is in the range of ListingDate and ClosingDate. If the check succeeds, copy the entity to another list.

DateTime query = ...;
List<Listing> list = ...;
List<Listing> pass = new();

foreach (Listing entity in list)
{
    if (entity.ListingTime < query && query < entity.ClosingTime)
        pass.Add(entity)
}

While checking whether the query is in range, we could've used DateTime.Compare() but less than/greater than operators make the statement easier to read.