C # Linq 两个日期之间的日期

我正在尝试获得我的 linq 语句，以获得两个日期之间的所有记录，我不太确定我需要改变它的工作: (a.Start >= startDate && endDate)

var appointmentNoShow =
from a in appointments
from p in properties
from c in clients
where a.Id == p.OID && (a.Start.Date >= startDate.Date && endDate)
362624 次浏览
小开
最佳答案

Just change it to

var appointmentNoShow = from a in appointments
from p in properties
from c in clients
where a.Id == p.OID &&
(a.Start.Date >= startDate.Date && a.Start.Date <= endDate)
小开 
var appointmentNoShow = from a in appointments
from p in properties
from c in clients
where a.Id == p.OID
where a.Start.Date >= startDate.Date
where a.Start.Date <= endDate.Date
小开 
var QueryNew = _context.Appointments.Include(x => x.Employee).Include(x => x.city).Where(x => x.CreatedOn >= FromDate).Where(x => x.CreatedOn <= ToDate).Where(x => x.IsActive == true).ToList();
小开 

 public List<tbltask> gettaskssdata(int? c, int? userid, string a, string StartDate, string EndDate, int? ProjectID, int? statusid)
{
List<tbltask> tbtask = new List<tbltask>();
DateTime sdate = (StartDate != "") ? Convert.ToDateTime(StartDate).Date : new DateTime();
DateTime edate = (EndDate != "") ? Convert.ToDateTime(EndDate).Date : new DateTime();
tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).
Where(x => x.tblproject.company_id == c
&& (ProjectID == 0 || ProjectID == x.tblproject.ProjectId)
&& (statusid == 0 || statusid == x.tblstatu.StatusId)
&& (a == "" || (x.TaskName.Contains(a) || x.tbUser.User_name.Contains(a)))
&& ((StartDate == "" && EndDate == "") || ((x.StartDate >= sdate && x.EndDate <= edate)))).ToList();






return tbtask;




}

this my query for search records based on searchdata and between start to end date

小开

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 => (DbFunctions.TruncateTime(s.Created_at.Value) >= dateStart && DbFunctions.TruncateTime(s.Created_at.Value) <= 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) >= dateStart && 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) <= dateEnd)

Note the following method mentioned on other stackoverflow questions and answers will not work correctly:

....
&&
(
s.Created_at.Value.Day >= dateStart.Day && s.Created_at.Value.Day <= dateEnd.Day &&
s.Created_at.Value.Month >= dateStart.Month && s.Created_at.Value.Month <= dateEnd.Month &&
s.Created_at.Value.Year >= dateStart.Year && s.Created_at.Value.Year <= 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 <= 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

小开

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 => x.From <= yesterday && x.To >= yesterday).ToList(),
Today = _context.Table.Where(x => x.From <= today & x.To >= today).ToList(),
Tomorrow = _context.Table.Where(x => x.From <= tomorrow & x.To >= tomorrow).ToList()
};
小开

If someone interested to know how to work with 2 list and between dates

var newList = firstList.Where(s => secondList.Any(secL => s.Start > secL.RangeFrom && s.End < secL.RangeTo))
小开

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=>
(x.ValidFrom >= intervalDateFrom && x.ValidFrom <= intervalDateTo) ||
(x.ValidTo >= intervalDateFrom && x.ValidTo <= intervalDateTo) ||
(intervalDateFrom >= x.ValidFrom && intervalDateFrom <= x.ValidTo) ||
(intervalDateTo >= x.ValidFrom && intervalDateTo <= x.ValidTo)
);
小开

You can use DbFunctions.TruncateTime(StartDateTime) To remove the time from datetime

var appointmentNoShow =
from a in appointments
from p in properties
from c in clients
where a.Id == p.OID && (DbFunctions.TruncateTime(a.Start) >= DbFunctions.TruncateTime(startDate) && endDate)

提交答案