CODEWITHSUNDEEP
linq-to-sqllinq-to-entitieslinq-to-objects
Kash
Kash

Reputation: 245

LINQ query records based on date array

I have a table (I am using Entity Model) and filtered that table using LINQ query which is working fine. Now I want to filter the records on the basis of array of dates. I am not able to implement IN clause on array of dates

filteredList = leadsNewAndScheduled.Where(lead =>  
              (LeadTypeIDs.Contains(lead.TYPE_ID.ToString()) ||
              LeadTypeIDs == string.Empty) &&
              (priorityIDs.Contains(lead.PRIORITY_ID.ToString()) ||
              priorityIDs == string.Empty) &&
              (((lead.EXPIRED_ON <= dateExpiry ||
              Convert.ToDateTime(lead.EXPIRED_ON) == DateTime.Today.Date) &&
              lead.STATUS_ID == (int)Enumerations.LeadStatus.New) ||
              lead.STATUS_ID == (int)Enumerations.LeadStatus.Active) &&
              (lead.START_TIME IN (arrAppointmentDates))
            ).ToList();

I want your help in following

(lead.START_TIME IN (arrAppointmentDates))

Thanks in advance.

Upvotes: 2

Views: 1089

Answers (2)

Kash
Kash

Reputation: 245

I solved this problem while declaring list of dates and then applying contains clause in LINQ query.

example:

//list of dates.
List<DateTime> arrAppointmentDates;

//change in query
arrAppointmentDates.Contains(Convert.ToDateTime(lead.START_TIME).Date)

Upvotes: 1

Muhammad Hasan Khan
Muhammad Hasan Khan

Reputation: 35156

Use Predicate Builder

Write your query without the date condition like

var query = leadsNewAndScheduled.Where(lead =>  
              (LeadTypeIDs.Contains(lead.TYPE_ID.ToString()) ||
              LeadTypeIDs == string.Empty) && ....

Then write

  var predicate = PredicateBuilder.False<Lead>();

  foreach (DateTime date in dates)
  {
    DateTime temp = date;
    predicate = predicate.Or (p => p.START_TIME == temp);
  }

  var result = query.Where(predicate).ToList(); // Don't call ToList() earlier

However please note that if you're using Entity Framework you need to call AsExpandable() on the entity set before applying predicates on it like so:

return objectContext.Products.AsExpandable().Where (predicate);

Upvotes: 2

Related Questions