CODEWITHSUNDEEP
c#linqdatetime
Silver
Silver

Reputation: 453

how to combine two columns Date and Time and compare it with given DateTime using linq

I have following columns in database table - Records:

Info - varchar(50)
Date - DateTime
Time - varchar(20) , format like hh:mm

Now I have a function

 public GetRecords(DateTime pDateTime)
    {
    // this function should get all the records from above table whose DateTime is more than pDateTime
    }

How can I do using Linq? I know how to write a linq query but how can I combine these columns for each row of table and compare with pDateTime.

Upvotes: 0

Views: 1347

Answers (3)

JasonMu
JasonMu

Reputation: 56

public List<NewRecord> GetRecords(DateTime pDateTime)
{
    using (var db = YourDbContext())
    {
        var records = new List<Record>();
        return db.Records.Select(r =>
        {
            var record = new Record();
            record.info = r.info;
            var hm = DateTime.Parse(r.Time);
            var date = new DateTime(r.Date.Year, r.Date.Month, r.Date.Day, hm.Hour, hm.Minute);
            record.Date = date;
            return record;
        }).Where(r=>r.Date >pDateTime).ToList();
    }
}

Upvotes: 1

Silver
Silver

Reputation: 453

  public List<FDetails> GetReocrds(DateTime date)
            {
                var fs = from f in DBContext.RecordsTable
                where f.Date > date 
                && ConvertToIntfun(f.Time.Split(':')[0]) >= date.Hour 
                && ConvertToIntfun(f.Time.Split(':')[1])>date.Minute
                                select new FDetails(f.Id,f.Info, (DateTime)f.Date, f.Time);
                return fs.ToList();
            }
            internal static int ConvertToIntfun(string value)
            {
                int result = 0;
                if (int.TryParse(value, out result))
                {
                    return result;
                }
                else
                {
                    return result;
                }
            }

or

var fs = from f in DBContext.RecordsTable
                            where (new DateTime(f.Date.Value.Year, f.Date.Value.Month, f.Date.Value.Day, ConvertToIntfun(f.FTime.Split(':')[0]), ConvertToIntfun(f.Time.Split(':')[1]),0))>date
                            select new ForecastDetails(forecast.ForecastId, forecast.ForecastSummary, (DateTime)forecast.ForecastDate, forecast.ForecastTime);
            return fs.ToList();

Upvotes: 0

Richard Schneider
Richard Schneider

Reputation: 35477

The best solution is to change your Record table to just contain Info and When; where When is the Date and Time of recorded info.

public IEnumerable<Record> GetRecords(DateTime after)
{
   using (var db = new TheDbContext())
   {
      return db.Records
         .Where(r => r.When > after);
   }
 }

If you can't change the Record schema. Then you could do a partial search in the database based on Date and then remove any entries that fall on the search date and not after the search time.

public IEnumerable<Record> GetRecords(DateTime after)
{
   var afterDate = after.Date;
   using (var db = new TheDbContext())
   {
      var records = db.Records
         .Where(r => r.Date >= afterDate);
      foreach (var r in records)
      {
         if (r.Date > afterDate) yield return r;
         else 
         {
            var when = r.Date + TimeSpan.Parse(r.Time, "hh:mm");
            if (when > after) yeild return r;
         }
      }
   }
 }

Upvotes: 0

Related Questions