C Linq Where Date Between 2 Dates

Question

I m trying to get my linq statement to get me all records between two dates  and I m not quite sure what I need to change to get it to work   a Start  gt   startDate  amp  amp  endDate   var appointmentNoShow       from a in appointments     from p in properties     from c in clients     where a Id    p OID  amp  amp   a Start Date  gt   startDate Date  amp  amp  endDate

User · Answer

If someone interested to know how to work with 2 list and between dates  var newList   firstList Where s   gt  secondList Any secL   gt  s Start  gt  secL RangeFrom  amp  amp  s End  lt  secL RangeTo

User · Answer

var appointmentNoShow   from a in appointments                         from p in properties                         from c in clients                         where a Id    p OID                         where a Start Date  gt   startDate Date                         where a Start Date  lt   endDate Date

User · Answer

So you are scrolling down because the Answers do not work   This works like magic  but they say it has efficiency issues for big data  And you do not care just like me   1- Data Type in Database is  datetime  and  nullable  in my case   Example data format in DB is like   2018-11-06 15 33 43 640   An in C  when converted to string is like   2019-01-03 4 45 16 PM   So the format is    yyyy MM dd hh mm ss tt   2- So you need to prepare your datetime variables in the proper format first   Example 1  yourDate ToString  yyyy MM dd hh mm ss tt     Example 2 - Datetime range for the last 30 days      DateTime dateStart   DateTime Now AddDays -30       DateTime dateEnd   DateTime Now AddDays 1  AddTicks -1     3- Finally the linq query you lost your day trying to find  Requires EF 6   using System Data Entity    dbContext Shipments Where s   gt   DbFunctions TruncateTime s Created at Value   gt   dateStart  amp  amp  DbFunctions TruncateTime s Created at Value   lt   dateEnd   Count      To take time comparison into account as well     DbFunctions CreateDateTime s Created at Value Year  s Created at Value Month  s Created at Value Day  s Created at Value Hour  s Created at Value Minute  s Created at Value Second   gt   dateStart  amp  amp  DbFunctions CreateDateTime s Created at Value Year  s Created at Value Month  s Created at Value Day  s Created at Value Hour  s Created at Value Minute  s Created at Value Second   lt   dateEnd    Note the following method mentioned on other stackoverflow questions and answers will not work correctly         amp  amp        s Created at Value Day  gt   dateStart Day  amp  amp  s Created at Value Day  lt   dateEnd Day  amp  amp      s Created at Value Month  gt   dateStart Month  amp  amp  s Created at Value Month  lt   dateEnd Month  amp  amp      s Created at Value Year  gt   dateStart Year  amp  amp  s Created at Value Year  lt   dateEnd Year    count      if the start day was in this month for example and the end day is on the next month  the query will return false and no results  for example   DatabaseCreatedAtItemThatWeWant    2018 12 05  startDate   2018 12 01  EndDate   2019 01 04  the query will always search for days between 01 and 04 without taking the  month  into account  so  s Created at Value Day  lt   dateEnd Day  will fail   And in case you have really big data you would execute Native SQL Query rather than linq              where Shipments Created at BETWEEN CAST  Created at from as datetime  AND CAST  Created at to as datetime              Thanks

User · Answer

x000D   x000D   public List lt tbltask gt  gettaskssdata int  c  int  userid  string a  string StartDate  string EndDate  int  ProjectID  int  statusid  x000D            x000D              List lt tbltask gt  tbtask   new List lt tbltask gt     x000D              DateTime sdate    StartDate          Convert ToDateTime StartDate  Date   new DateTime    x000D              DateTime edate    EndDate          Convert ToDateTime EndDate  Date   new DateTime    x000D              tbtask   entity tbltasks Include x   gt  x tblproject  Include x   gt  x tbUser   x000D                  Where x   gt  x tblproject company id    c x000D                       amp  amp   ProjectID    0    ProjectID    x tblproject ProjectId  x000D                       amp  amp   statusid    0    statusid    x tblstatu StatusId  x000D                       amp  amp   a           x TaskName Contains a     x tbUser User name Contains a    x000D                       amp  amp    StartDate        amp  amp  EndDate             x StartDate  gt   sdate  amp  amp  x EndDate  lt   edate     ToList    x000D   x000D   x000D   x000D              return tbtask  x000D   x000D   x000D            x000D   x000D   x000D    this my query for search records based on searchdata and between start to end date

User · Answer

var QueryNew    context Appointments Include x   gt  x Employee  Include x   gt  x city  Where x   gt  x CreatedOn  gt   FromDate  Where x   gt  x CreatedOn  lt   ToDate  Where x   gt  x IsActive    true  ToList

User · Answer

I had a problem getting this to work   I had two dates in a db line and I need to add them to a list for yesterday  today and tomorrow   this is my solution           var yesterday   DateTime Today AddDays -1           var today   DateTime Today          var tomorrow   DateTime Today AddDays 1                       var vm   new Model                         Yesterday    context Table Where x   gt  x From  lt   yesterday  amp  amp  x To  gt   yesterday  ToList                Today    context Table Where x   gt  x From  lt   today  amp  x To  gt   today  ToList                Tomorrow    context Table Where x   gt  x From  lt   tomorrow  amp  x To  gt   tomorrow  ToList

User · Answer

Just change it to  var appointmentNoShow   from a in appointments                         from p in properties                         from c in clients                         where a Id    p OID  amp  amp                           a Start Date  gt   startDate Date  amp  amp  a Start Date  lt   endDate

User · Answer

If you have date interval filter condition and you need to select all records which falls partly into this filter range  Assumption  records has ValidFrom and ValidTo property   DateTime intervalDateFrom   new DateTime 1990  01  01   DateTime intervalDateTo   new DateTime 2000  01  01    var itemsFiltered   allItems Where x  gt        x ValidFrom  gt   intervalDateFrom  amp  amp  x ValidFrom  lt   intervalDateTo          x ValidTo  gt   intervalDateFrom  amp  amp  x ValidTo  lt   intervalDateTo          intervalDateFrom  gt   x ValidFrom  amp  amp  intervalDateFrom  lt   x ValidTo          intervalDateTo  gt   x ValidFrom  amp  amp  intervalDateTo  lt   x ValidTo

[c#] C# Linq Where Date Between 2 Dates

Examples related to c#

Examples related to linq