CODEWITHSUNDEEP
c#sqllinqsql-server-2008entity-framework
Jitendra Pancholi
Jitendra Pancholi

Reputation: 7562

Convert string time to datetime or timespan in entity framework query

I have stored Time string say "12:20 PM" in the database and i want to compare it with current time in linq query (EF). As i can not convert it using Convert.ToDateTime etc because it can not be converted into any sql expression.

I am thinking to write a query like below, but i know this wont help. Please advise.

List<CompanyScheduler> companySchedulers = 
    context.CompanySchedulers
          .Where(m => m.IsActive && m.Start <= EntityFunctions.AddMinutes(td, m.TimeZoneOffset))
          .Where(m => (m.LastRun.HasValue && EntityFunctions.TruncateTime(m.LastRun) < tdExcludeTime) || (!m.LastRun.HasValue))
          .Where(m => (m.When == (int)When.Daily && (Convert.ToDateTime("01-01-1990 " + m.RecurAt).TimeOfDay < EntityFunctions.AddMinutes(td, m.TimeZoneOffset).Value.TimeOfDay)) ||
                      (m.When == (int)When.Once && !m.LastRun.HasValue) ||
                      (m.When == (int)When.Weekly && m.RecurrEvery.Contains(today)))
          .ToList();

Upvotes: 1

Views: 2744

Answers (3)

Arturo Menchaca
Arturo Menchaca

Reputation: 15982

There is not direct way to 'parse' strings to DateTime or TimeSpan in Linq to Entities, but you can do it using SqlFunctions and DbFunctions classes:

For TimeSpan:

DbFunctions.CreateTime(SqlFunctions.DatePart("hh", timeString),
                       SqlFunctions.DatePart("mi", timeString), 
                       SqlFunctions.DatePart("ss", timeString));

For DateTime:

DbFunctions.CreateDateTime(SqlFunctions.DatePart("yy", dateString),
                           SqlFunctions.DatePart("mm", dateString),
                           SqlFunctions.DatePart("dd", dateString),
                           SqlFunctions.DatePart("hh", dateString),
                           SqlFunctions.DatePart("mi", dateString),
                           SqlFunctions.DatePart("ss", dateString));

Upvotes: 2

user1983547
user1983547

Reputation:

It's not possible below EF 6.x. Either use stored procedure or make all conversions before to link query.

Upvotes: 1

RePierre
RePierre

Reputation: 9576

You can load your data in memory (by calling ToArray() or ToList()) on your collection and use Convert.ToDateTime:

var date = DateTime.Today;
var areEqual = dataContext.YourTable.ToList() 
        //now the data is in memory and you can apply whatever projection you want
        .Select(x => new{Date = Convert.ToDateTime(x.DateStringColumn)})
        .All(x => x.Date == date);

However, this can increase the memory usage if the dataset is quite large. To avoid this use the Where() method to load only the records you need.

Upvotes: 0

Related Questions