MVC - Charting - Display multiple series using data from SqlServer( Entity Framework)

Watch this example on YouTube


1. Table 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChartTest](
    [ProductName] [varchar](50) NULL,
    [Month] [varchar](50) NULL,
    [Amount] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Apple', N'Jan', 20)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Apple', N'Feb', 40)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Apple', N'Mar', 22)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Apple', N'Apr', 44)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Orange', N'Jan', 55)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Orange', N'Feb', 22)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Orange', N'Mar', 33)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Orange', N'Apr', 88)
GO

2. Stored Procedure

CREATE PROCEDURE [dbo].[GetProducts]

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * FROM ChartTest
END


3. Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI.DataVisualization.Charting;
using System.IO;
using System.Text;
using System.Drawing;
using WebApplication4.Models;

namespace WebApplication4.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();

        public ActionResult ChartMultiColumns()
        {
            var dataAll = db.GetProducts().ToList();
            var months = from d in dataAll orderby d.Month group d by d.Month into m select m.Key;
            var chart = new Chart();
            foreach(var mn in months)
            {
                var data = from d in dataAll where d.Month == mn select d;
                var area = new ChartArea();
                chart.ChartAreas.Add(area);
                var series = new Series();
                foreach(var item in data)
                {
                    series.Points.AddXY(item.ProductName, item.Amount);
                }
                series.ChartType = SeriesChartType.Column;
                series.Name = data.First().Month;
                chart.Series.Add(series);
            }
            Legend chartLegend = new Legend();
            chartLegend.Name = "Result";
            chartLegend.LegendStyle = LegendStyle.Column;
            chart.Legends.Add(chartLegend);

            var returnStream = new MemoryStream();
            chart.ImageType = ChartImageType.Png;
            chart.SaveImage(returnStream);
            returnStream.Position = 0;
            return new FileStreamResult(returnStream, "image/png");
        }


4. View

@{
    ViewBag.Title = "ChartMultiColumns";
}

<h2>ChartMultiColumns</h2>

<img src="@Url.Action("ChartMultiColumns")" />