I have two two model table and one model for store data which that i want grouping and select new object using model store using controller
[Table("Product")]
public class Product
{
[Key]
public int ProductID { get; set; }
public string ProductName { get; set; }
public virtual ICollection<SalesData> SalesData { get; set; }
}
[Table("SalesData")]
public class SalesData
{
[Key]
public int SaleID { get; set; }
public int ProductID { get; set; }
public string Region { get; set; }
public double SaleAmount { get; set; }
public DateTime SaleDate { get; set; }
[ForeignKey("ProductID")]
public virtual Product Product { get; set; }
}
public class SalesDataDto
{
public string ProductName { get; set; }
public List<double> X { get; set; } // Date converted to numeric format
public List<double> Y { get; set; } // Sales Amount
public List<string> Z { get; set; } // Region
public SalesDataDto()
{
X = new List<double>();
Y = new List<double>();
Z = new List<string>();
}
}
[HttpGet]
public JsonResult GetPlotlyDBChartData()
{
try
{
var salesData = db.SalesDatas
.GroupBy(s => s.Product.ProductName)
.Select(g => new SalesDataDto
{
ProductName = g.Key,
X = g.Select(s => s.SaleDate.ToOADate()).ToList(), // Converts Date to OLE Automation date format (numeric)
Y = g.Select(s => s.SaleAmount).ToList(),
Z = g.Select(s => s.Region).ToList()
}).ToList();
return Json(salesData, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(ex.Message, JsonRequestBehavior.AllowGet);
}
}
Why i Get error like this :
