CODEWITHSUNDEEP
c#linqgoogle-visualization
Arun
Arun

Reputation: 1472

Linq Select product count based on year and category

I am trying to plot a google bar chart like belowenter image description here

I have the objects ,which are

product
{
id,
name,
categoryid
}

category
{
id,
categoryname
}

I tried code like

var grouped = (
  from c in allCategories
 from p in allProducts

 group p by new { year = p.CreateDate.Year} into d

 select new { year = d.Key.year, count = d.Count()}).OrderByDescending(g => g.year).Distinct();
<script type="text/javascript">
    google.load("visualization", "1", { packages: ["corechart"] });
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Category Name', '2006', '2011', '2013'],

           @foreach (var p in grouped)
            {
                int productThisYear = grouped.Where(x => x.year <= p.year).Select(x => x.count).Sum();

                <text>
              ['',  @productThisYear,@productThisYear,@productThisYear]
                </text>
                if( counter++ != grouped.Count() - 1){
                    @Html.Raw(",")
                }
            }
        ]);

        var options = {
            title: 'Product Categories',
            hAxis: { title: '' }
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data, options);
    }
    </script>

but I can't get the category name and the product count based on this category name and year.any body can explain the problem? Thanks in advance for help..

Upvotes: 3

Views: 1041

Answers (1)

Grundy
Grundy

Reputation: 13381

you can use join clause like this

var grouped = (
    from c in allCategories
    join p in allProducts on c.id equals p.categoryid
    group p by new {p.CreateDate.Year, c.categoryname} into g
    select new {categoryname=g.Key.categoryname, g.Key.Year, count=g.Count()})
   .OrderByDescending(g => g.year);

so you can get category name in your foreach as p.categoryname

Upvotes: 3

Related Questions