System not supported when converting DateTime - DotNetHighChart

Question

I am very new to C# and MVC and Im creating a web application. I am trying to create a line graph using DotNet High Chart which will populate using data from my Database. I am having a problem converting the DateTime to string. My chart controller is:

var dataLeft = (from d in db.Appointments
                        select new
                        {
                            Date = d.Date.ToString("yyyyMMdd"),
                            IOPLeft = d.IOPLeft,
                        }).ToList();

        var xSeries = dataLeft.Select(a => a.Date).ToArray();
        var ySeries = dataLeft.Select(a => a.IOPLeft).ToArray();
// instantiate an object  of the high charts type
        var chart = new Highcharts("chart")
            // define the type of chart
                .InitChart(new Chart { DefaultSeriesType = ChartTypes.Line })
            //overall title of the chart
                .SetTitle(new Title { Text = "Left IOP" })
            //small label below the main title
                .SetSubtitle(new Subtitle { Text = "LeftIOP" })
            // load the x values
                .SetXAxis(new XAxis { Categories = xSeries })
            // set the y title
                .SetYAxis(new YAxis { Title = new YAxisTitle { Text = "IOP" } })
                    .SetTooltip(new Tooltip
                    {
                        Enabled = true,
                        Formatter = @"function() { return '<b>'+this.series.name +'</b><br/>'+this.x+': '+this.y;}"
                    })
                        .SetPlotOptions(new PlotOptions
                        {
                            Line = new PlotOptionsLine
                            {
                                DataLabels = new PlotOptionsLineDataLabels
                                {
                                    Enabled = true
                                },
                                EnableMouseTracking = false
                            }
                        })
            //load y values
.SetSeries(new[]
    {
    new Series {Name = "Patient", 
        Data = new Data(new object[] {ySeries})},
        
});


        return View(chart);
    }
}
}

My model:

[Display(Name = "Date")]
    [DataType(DataType.Date)]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:dd/MM/yyyy}")]
    public DateTime Date { get; set; }

 [Display(Name = "Left IOP")]
    public int IOPLeft { get; set; }

When I try to run the application I am getting the following error:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

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

Any hep would be greatly appreciated Thanks

drneel · Accepted Answer · 2015-08-15T16:31:38.723

You are receiving the error because of the .ToString("yyyyMMdd") in the code below. Basically, SqlServer doesn't know how to interpret the c# .ToString() functionality and causes the exception.

var dataLeft = (from d in db.Appointments
                        select new
                        {
                            Date = d.Date.ToString("yyyyMMdd"),
                            IOPLeft = d.IOPLeft,
                        }).ToList();

You have to pull the data out of the database in the 'correct' format, and then manipulate it to match the format that you would like.

So something like this would be better:

var dataLeft = (from d in db.Appointments
                        select new
                        {
                            Date = d.Date,
                            IOPLeft = d.IOPLeft,
                        }).ToList();

        var xSeries = dataLeft.Select(a => a.Date.ToString("yyyyMMdd")).ToArray();

score -1 · Answer 2 · answered Nov 19 '21 at 07:15

-1

Make dateTime nullable so it will not throw exception while fetching data

public DateTime? Date { get; set; }

answered Nov 19 '21 at 07:15

vikashjha.tp

37
3

How does this relate to the error message "LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression."? – Lance U. Matthews Nov 19 '21 at 07:37
The default and the lowest value of a DateTime object is January 1, 0001 00:00:00 (midnight). The maximum value can be December 31, 9999 11:59:59 P.M. , and whenever we paas a null value in this field the default date 01.01.0001 goes into the data base and the database identify this value as datetime2 data type , hence throws an exception.... – vikashjha.tp Nov 19 '21 at 12:33

System not supported when converting DateTime - DotNetHighChart

2 Answers2