Error while converting nullable datetime to string in LINQ asp.net MVC

Question

I am developing an asp.net MVC system with a Kendo UI. I have to send a "date" from a filter button in View to the controller and filter the LINQ. I used this code:

public ActionResult Grid_ReadLogAdminList([DataSourceRequest] DataSourceRequest request, string filterDate)
        {
            DateTime _temp;
            if (!DateTime.TryParse(filterDate, out _temp))
                _temp = DateTime.Now;

            return Json(_context.Entities<LogAdmin>().NoTracking().OrderByDescending(l => l.TimeStamp)
                .Where(f => f.TimeStamp.Value.ToString("dd.MM.yyyy") == _temp.ToString("dd.MM.yyyy"))
                .Select(l => new LogAdminInfo
                {
                    Id = l.Id,
                    Message = l.Message,
                    MessageTemplate = l.MessageTemplate,
                    Level = l.Level,
                    TimeStamp = l.TimeStamp,
                    Exception = l.Exception,
                    Properties = l.Properties,
                    LogEvent = l.LogEvent,
                })
                .ToDataSourceResult(request));
        }

but it gave me an error with the ".Where". you should know that TimeStamp field is "datetime?" nullable datetime.

I received this Error:

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

how can I fix the Error?

I tried to do it. .Where(f => (f.TimeStamp != null ? f.TimeStamp.Value.ToString("dd.MM.yyyy") : "") == _temp.ToString("dd.MM.yyyy")) .... did not work — Samy Sammour
– Samy Sammour, Commented Oct 11, 2016 at 13:35
Because it couldn't be translated to T-SQL, Linq to Entities couldn't recognize it. See here:stackoverflow.com/a/34061692/2946329 — Salah Akbari
– Salah Akbari, Commented Oct 11, 2016 at 13:35
first select your field and do ToString in the select close, then apply the where. Select clause is in .NET Field where as Where is in SQL field — Franck Ngako
– Franck Ngako, Commented Oct 11, 2016 at 13:37
Use [DisplayFormat(ApplyFormatInEditMode= true, DataFormatString = "{0:dd.MM.yyyy}")] public DateTime? TimeStamp { get; set; } at your ViewModel Property — mmushtaq
– mmushtaq, Commented Oct 11, 2016 at 13:37

YuvShap · Accepted Answer · 2016-10-11 13:51:55Z

1

The error is pretty indicative LINQ to Entities does not support the ToString method, you should compare your dates using another approach.

Based on your code I assume you are only interested in the date part of the DateTime for comparison so I suggest you to try DbFunctions.TruncateTime Method:

Where(f => DbFunctions.TruncateTime(f.TimeStamp) == DbFunctions.TruncateTime(_temp))

edited Oct 11, 2016 at 13:51

answered Oct 11, 2016 at 13:40

YuvShap

3,8352 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fubo · Accepted Answer · 2016-11-09 09:48:07Z

1

replace

.Where(f => f.TimeStamp.Value.ToString("dd.MM.yyyy") == _temp.ToString("dd.MM.yyyy"))

with

.Where(f =>  DbFunctions.TruncateTime(f.TimeStamp) == _temp)

edited Nov 9, 2016 at 9:48

answered Oct 11, 2016 at 13:37

fubo

46.2k20 gold badges109 silver badges139 bronze badges

Comments

Franck Ngako · Accepted Answer · 2016-10-11 13:44:16Z

    var tempDate = _temp.ToString("dd.MM.yyyy")    
    Json(_context.Entities<LogAdmin>().NoTracking().OrderByDescending(l => l.TimeStamp)    
.Select(l => new LogAdminInfo
                    {
                        Id = l.Id,
                        Message = l.Message,
                        MessageTemplate = l.MessageTemplate,
                        Level = l.Level,
                        TimeStamp = l.TimeStamp.Value.ToString("dd.MM.yyyy"),
                        Exception = l.Exception,
                        Properties = l.Properties,
                        LogEvent = l.LogEvent,
                    }).Where(l => l.TimeStamp = tempDate).Select(l)
                    .ToDataSourceResult(request));

Collectives™ on Stack Overflow

Error while converting nullable datetime to string in LINQ asp.net MVC

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related