public ActionResult ShowLargestOrder() { using (MainContext db = new MainContext()) { BIDashboardVM data = new BIDashboardVM(); return(View(data)); } }
// GET: BIDashboard public ActionResult Index() { //initialize db connection to maincontext using (MainContext db = new MainContext()) { //Linq query to join customer and order table var usersWithCount = (from c in db.Customers join o in db.Orders on c.CustomerId equals o.CustomerId into result from sub in result.DefaultIfEmpty() select new ViewModels.CustomerOrder { OrderId = sub != null ? sub.OrderId : (int?)null, CustomerId = c.CustomerId, } ) // Here we have the left join result. .GroupBy(g => g.CustomerId, (k, orders) => new { CustomerId = k, //do count on number of orders per customer OrderCount = orders.Count(t => t.OrderId != null), //order the query result from highest number of orders to lowest. }).OrderByDescending(t => t.OrderCount).ToList(); //select the top 3 customers from query results var top3Customers = usersWithCount.Take(3); //initialize new BIDashboard ViewModel BIDashboardVM data = new BIDashboardVM(); //populate the view models with customer data from top 3 performers foreach (var item in top3Customers) { int cid = item.CustomerId; var customer = db.Customers.Find(cid); data.customerList.Add(customer); db.SaveChanges(); } var allOrders = (from o in db.Orders select new OrderValue { OrderId = o.OrderId, Amount = o.TotalAmount, customerId = o.CustomerId } ).OrderByDescending(t => t.Amount).ToList(); var top3OrderValues = allOrders.Take(3); //Get top 3 values from order table save to orderLists foreach (var item in top3OrderValues) { decimal value = item.Amount; int cid = item.customerId; int? oid = item.OrderId; var order = db.Orders.Find(oid); data.ordersLists.Add(order); db.SaveChanges(); } foreach (var item in allOrders) { int?oid = item.OrderId; var order = db.Orders.Find(oid); data.allOrderList.Add(order); db.SaveChanges(); } //Load BIDashboardVM with all orders data. //OrderbyDecending(o => o.OrderId).ToList(); //pass ViewModel data to the view return(View(data)); } }