Entity framework "Select date time from string"

Question

I need to select the records from a table for date interval.

But the date to select from is kept as nvarchar in this pattern

20160511_155015 (yyyymmdd_hhmmss)

I cannot use ToList() to make it as DateTime.ParseExact(entry.StartDate, "yyyyMMdd_HHmmss", CultureInfo.InvariantCulture)

The table keeps several millions records. So I need to make something like this:

var preQuery = context.Table
              .AsNoTracking()
              .Select(x => new
             {
                StartDate = ConvertFrom()),
                Mode = x.Mode,
                SessionStart = x.AStart,
                SessionEnd = x.AEnd,
                x.SensorNumber
            })
            .Where(x => x.StartDate != null 
                   && x.StartDate >= startDate
                   && x.StartDate <= endDate)
            .ToList();

Is it possible to convert the string representation to Datetime and then proceed with Where clause ?

Why do you store them in that mess instead of as `datetime`? — Tim Schmelter, Jul 18 '16 at 11:14
@Roman: it it is, then probably it's the *schema* that should be *re-designed*? You have no need to implement the new design in one go. As a first step, you can create views (with right columns and types), then convert views into materialized views, than make swap: turn materialized views into tables and former tables into just views; finally drop the tables. — Dmitry Bychenko, Jul 18 '16 at 11:34
@Dmitry Bychenko: I can not change the schema either ))). It is the production stage for 2 years — Roman, Jul 18 '16 at 11:36
Can I suggest you writing raw SQL Queries? https://msdn.microsoft.com/en-us/data/jj592907.aspx In that case you can use SUBSTRING and ultimately CONVERT it to DateTime in SQL itself. Provided your situation and permission to modify old code, I think this is the best solution for you. — Krishnandu Sarkar, Jul 18 '16 at 11:57
So with the above methods you can parse it into DateTime using SQL by writing it as CONVERT(DATETIME, SUBSTRING(@dateTimeString, 1, 4) + '-' + SUBSTRING(@dateTimeString, 5, 2) + '-' + SUBSTRING(@dateTimeString, 7, 2) + ' ' + SUBSTRING(@dateTimeString, 10, 2) + ':' + SUBSTRING(@dateTimeString, 12, 2) + ':' + SUBSTRING(@dateTimeString, 14, 2)) — Krishnandu Sarkar, Jul 18 '16 at 12:07

TTomer · Answer 1 · 2016-07-18T11:48:03.173

0

You can try: StartDate.StartsWith("20160511") or so.. or Convert.ToInt32(StartDate.Substring(0,8)) > 20160511

by the way, i think you might want to run a script, as long as it takes, and create a new column which will generate a DateTime based on that column

edited Jul 18 '16 at 11:48

answered Jul 18 '16 at 11:31

TTomer

356
2
11

I can not use ToList() to use DateTime.parseExact – Roman Jul 18 '16 at 11:34

score 0 · Answer 2 · answered Jul 18 '16 at 11:36

0

Please try this DateTime.ParseExact(dateTime ,format,CultureInfo.InvariantCulture);

var preQuery = context.Table.AsNoTracking().Select(x => new
        {
            StartDate = ConvertFrom(),
            Mode = x.Mode,
            SessionStart = DateTime.ParseExact(x.AStart,"yyyyMMdd_HHmmss", CultureInfo.InvariantCulture),
            SessionEnd = DateTime.ParseExact(x.AEnd,"yyyyMMdd_HHmmss", CultureInfo.InvariantCulture),
            x.SensorNumber
        })
        .Where(x => x.StartDate != null && x.StartDate >= startDate && x.StartDate <= endDate)
        .ToList();

answered Jul 18 '16 at 11:36

Nitesh Shaw

216
2
17

DateTime.ParseExact will not be translated to T-SQL! – Roman Jul 18 '16 at 11:41
so you have to add a view as per your desired sql then import that into your model then you try your prevous code very well – Nitesh Shaw Jul 18 '16 at 15:10

Entity framework "Select date time from string"

2 Answers2