Linq date comparision

Question

My query is like

var TMJM = (from TT in r2ge.Transcription_Tracker
            join TMA in r2ge.Transcription_Master on TT.Transcription_Id equals TMA.Transcription_Id
            select new
            {
                Trans_Id = TMA.Transcription_Id,
                Modified_dtm = TMA.Modified_dtm
            }).Distinct();

var qq = TMJM.Where(dr => (dr.Modified_dtm.Date >= sev_back_datetime) && (dr.Modified_dtm.Date <= DateTime.Now.Date));

It gives output Enumeration yielded no results while Data is there But when I separate start date and end date then It will show date but when I combine both start and end date then It won't give output.. Any mistake in query?

Answer 1

Evaluating each part of the date could be fraught with issue. I am assuming you are doing this to remove the time portion which can sometimes be problematic. Subsequently you can just evaluate the 'date' portion of the date time variable. This little example should explain.

class Program
{
    static void Main(string[] args)
    {

        List<DateTime> dates = new List<DateTime>();

        dates.Add(DateTime.Now.AddDays(-50));
        dates.Add(DateTime.Now.AddDays(-51));
        dates.Add(DateTime.Now.AddDays(-52));
        dates.Add(DateTime.Now.AddDays(-53));
        dates.Add(DateTime.Now.AddDays(-99));
        dates.Add(DateTime.Now.AddDays(-100));
        dates.Add(DateTime.Now.AddDays(-101));

        var sev_back_datetime = DateTime.Now.AddDays(-100);

        Console.WriteLine(sev_back_datetime.Date.ToShortDateString());
        Console.WriteLine("---------------------");

        var query = from date in dates 
                    where date.Date >= sev_back_datetime.Date && 
                          date.Date <= DateTime.Now.Date 
                    select date;

        foreach (var date in query.ToList())
            Console.WriteLine(date.ToShortDateString());

        Console.ReadLine();
    }


}

EDIT - a direct example for you.

        var TMJM = (from TT in r2ge.Transcription_Tracker
                    join TMA in r2ge.Transcription_Master on TT.Transcription_Id equals TMA.Transcription_Id
                    select new
                    {
                        Trans_Id = TMA.Transcription_Id,
                        Modified_dtm = TMA.Modified_dtm
                    }).Distinct();


        var qq = TMJM.Where(dr => (EntityFunctions.TruncateTime(dr.Modified_dtm) >= EntityFunctions.TruncateTime(sev_back_datetime)) && (EntityFunctions.TruncateTime(dr.Modified_dtm) <= EntityFunctions.TruncateTime(DateTime.Now)));