Entity Framework Core check if record is older than 5 years-CodePudding

I'm trying to select records that are older than 5 years to archive them.

What I tried is using the DateDiffYear function:

var projekte = await this.db.Project
        .Where(x => EF.Functions.DateDiffYear(
            x.CreateDate, 
             this.dateTimeProvider.Today) >= 5)
        .ToListAsync(cancellationToken: cancellationToken);

Unfortunately, this also selects a record with the CreateDate is 2017-08-20 even if today is 2022-08-19 - it only checks the year...

Is there an easy way to calculate this correctly so that it only selects the row if 5 complete years have passed?

Thanks in advance

CodePudding user response：

this should work:

    DateTime maxAge = DateTime.Now.AddYears(-5);
    var projekte = await this.db.Project.Where(x => maxAge.CompareTo(x.CreateDate >= 0)).ToList();

alternatively using your approach:

DateTime maxAge = DateTime.Now.AddYears(-5);
var projekte = await this.db.Project
        .Where(x => EF.Functions.DateDiffYear(
            x.CreateDate, maxAge) <= 0)
        .ToListAsync(cancellationToken: cancellationToken);

CodePudding user response：

var fiveYearBeforeToday=DateTime.Today.AddYears(-5);
var projekte = await this.db.Project
            .Where(x => 
                x.CreateDate > fiveYearBeforeToday)
            .ToListAsync(cancellationToken: cancellationToken)