LINQ query records based on date array

Question

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.

Muhammad Hasan Khan · Accepted Answer · 2011-09-29 06:14:58Z

2

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

edited Sep 29, 2011 at 6:14

answered Sep 28, 2011 at 10:20

Muhammad Hasan Khan

35.2k16 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kash Over a year ago

Thanks hasan, I created a class PredicateBuilder from your given source and implemented following code; var predicate = PredicateBuilder.False<LeadGetNewAndScheduledByEmployee_type>(); foreach (DateTime date in arrAppointmentDates) { DateTime temp = date; predicate = predicate.Or(p => Convert.ToDateTime(p.START_TIME).Date == temp); } filteredList = filteredList.Where(predicate).ToList(); But it gives me following error 'System.Collections.Generic.List<EntityModel.LeadGetNewAndScheduledByEmployee_type>' does not contain a definition for 'Where'. Any help in this ...

Muhammad Hasan Khan Over a year ago

Do you have System.Linq namespace included?

Kash Over a year ago

Yes I have Included System.Linq

Muhammad Hasan Khan Over a year ago

What is the type of leadsNewAndScheduled and where it is coming from?

Kash Over a year ago

leadsNewAndScheduled is of complex type Of Entity Model (i.e. List<LeadGetNewAndScheduledByEmployee_type>()) which is an output from a MSSQL Stored Procedure.

Christian Specht · Accepted Answer · 2011-10-12 21:19:09Z

1

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)

edited Oct 12, 2011 at 21:19

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

answered Oct 12, 2011 at 12:48

Kash

2451 gold badge4 silver badges15 bronze badges

Collectives™ on Stack Overflow

LINQ query records based on date array

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related