Check if any value exists in database table from date list using Entity Framework

user_16414383 0
Viewed 583

I have a list of dates like this:

DateTime now = DateTime.Today;

var expiriesDays = new List<int> { 1, 7, 14 };
List<DateTime> expiriesDates = expiriesDays.Select(x => now.AddDays(x).Date).ToList();

Now I have one table and it has field with name ComplianceExpiryDate, now I want to check if this field has any value from expiriesDates.

Like any record has value like 13-07-2021 OR 19-07-2021 OR 26-07-2021 in ComplianceExpiryDate

I just want to compare with Date, not time.

I tried with TruncateTime but, DbFunctions does not show this function to use and is throwing an error.

entity-framework-core entity-framework asp.net .net c#
2 Answers

This is the straight-forward solution that generates SQL with an IN.

You should test @GertArnold's solution as well in order to see what fits your case best. Answer from @GertArnold is using OR instead of IN, but will probably perform better when your db grows.

var today = DateTime.Today;

// create a list of dates without time-part specified
var expiryDays = new List<int> { 1, 7, 14 };
var expirationDates = expiryDays
    .Select(it => today.AddDays(it))
    .ToList(); 

// insert test data into db
var currentId = 1;
foreach (var currentDate in expirationDates)
{
    db.MyTables.Add(new MyTable
    {
        Id = currentId,
        ComplianceExpiryDate = currentDate.AddHours(3) // move time a few hours into the day
    });
    currentId++;
}

db.SaveChanges();

var numberOfMatchingItems = db.MyTables
    .Count(e => expirationDates.Contains(e.ComplianceExpiryDate.Date));

Console.WriteLine(
    $"Number of matching dates found in db: {numberOfMatchingItems}");

Output:

Number of matching dates found in db: 3

I used this definition for entity class with date:

public class MyTable
{
    public int Id { get; set; }
    public DateTime ComplianceExpiryDate { get; set; }
}

SQL generated by EF:

      SELECT COUNT(*)
      FROM "MyTables" AS "m"
      WHERE rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', "m"."ComplianceExpiryDate", 'start of day'), '0'), '.') IN ('2021-07-14 00:00:00', '2021-07-20 00:00:00', '2021-07-27 00:00:00')
user_140724980

You must always be careful when transforming database values before filtering. Because that's what's going to happen when applying AddDays to ComplianceExpiryDate or truncating its time part and then compare the results to a given date value. The database has to transform the values of the entire table before it comes to comparing them. That may be expensive, but it's worse that any index on the search field is now unusable. This phenomenon is captured in one short phrase: not sargable.

Therefore, always try to filter based on original database values. With dates, this often amounts to testing if a date lies in some date interval. The approach here is to build a day-long interval for each expiration date.

In short, build date intervals between which you want to find ComplianceExpiryDate and create a query that has "between start date and end date" conditions, combined by OR. This is best done using a predicate builder, for instance Linqkit, or this one.

The code then looks like this:

DateTime now = DateTime.Today;

var expiryDays = new List<int> { 1, 7, 14 };
var expirationIntervals = expiryDays
    .Select(x => (StartDate: now.AddDays(x - 1), EndDate: now.AddDays(x))).ToList();
    
Expression<Func<MyTable,bool>> predicate = PredicateBuilder.False<MyTable>();

predicate = expirationIntervals.Aggregate (predicate, (pred, tuple) =>
    pred.Or(t => t.ComplianceExpiryDate >= tuple.StartDate
              && t.ComplianceExpiryDate < tuple.EndDate));

var result = MyTables.Where(predicate);

This short Or method is an extension method in either predicate builder that chains two predicates into one predicate by an "OrElse" token.

user_8617160
Related