CODEWITHSUNDEEP
c#asp.net-mvclinqentity-frameworkdotnethighcharts
coto2
coto2

Reputation: 179

System not supported when converting DateTime - DotNetHighChart

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

Upvotes: 3

Views: 666

Answers (2)

vikashjha.tp
vikashjha.tp

Reputation: 45

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

public DateTime? Date { get; set; }

Upvotes: -1

drneel
drneel

Reputation: 2907

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();

Upvotes: 3

Related Questions