C # Linq Where Date Between 2 Dates

C # Linq Where Date Between 2 Dates

---

두 날짜 사이의 모든 레코드를 가져 오기 위해 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)

---

그냥 변경하십시오

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;


        }

이 검색 데이터를 기반으로 시작 날짜와 종료 날짜 사이에 검색 레코드에 대한 내 쿼리

---

따라서 답변이 작동하지 않기 때문에 아래로 스크롤합니다.

이것은 마술처럼 작동합니다 (하지만 그들은 빅 데이터에 대한 효율성 문제가 있다고 말하며 나처럼 신경 쓰지 않습니다)

1- 데이터베이스의 데이터 유형은 제 경우에는 "datetime"및 "nullable"입니다.

DB의 예제 데이터 형식은 다음과 같습니다.

2018-11-06 15:33:43.640

C #에서 문자열로 변환하면 다음과 같습니다.

2019-01-03 4:45:16 PM

따라서 형식은 다음과 같습니다.

yyyy/MM/dd hh:mm:ss tt

2- 따라서 먼저 적절한 형식으로 datetime 변수를 준비해야합니다.

예 1

yourDate.ToString("yyyy/MM/dd hh:mm:ss tt")

예 2-지난 30 일 동안의 날짜 / 시간 범위

    DateTime dateStart = DateTime.Now.AddDays(-30);
    DateTime dateEnd = DateTime.Now.AddDays(1).AddTicks(-1);

3- 마지막으로 찾기 위해 하루를 잃어버린 linq 쿼리 (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()
        };

참고URL : https://stackoverflow.com/questions/2237440/c-sharp-linq-where-date-between-2-dates