问题描述:

Im trying to create a table that counts all orders and groups them in a table from sql to linq to use in a bar graph with google charts.

Table`

Orders Status

8 Created

3 Delayed

4 Enroute

sql

SELECT Count (OrderID) as 'Orders', order_status FROM [ORDER]

where order_status ='Created'OR order_status= 'Delayed' OR order_status='Enroute'

group by order_status

controller

 public ActionResult GetChart()

{

var Orders = db.Order.Select(a => new { a.OrderID, a.order_status })

.GroupBy(a => a.order_status);

return Json(Orders, JsonRequestBehavior.AllowGet);

}

this is not displaying the correct results as the linq seems to be wrong.

can someone please point me in the right direction? I am relatively new to this.

thanks in advance.

网友答案:

This should work:-

var result = db.Order.Where(x => x.order_status == "Created"
                             ||  x.order_status == "Delayed"
                             ||  x.order_status == "Enroute")
                     .GroupBy(x => x.order_status)
                     .Select(x => new 
                                  {
                                       order_status = x.Key,
                                       Orders = x.Count()
                                  });

Or if you prefer query syntax then:-

var result = from o in db.Order
             where o.order_status == "Created" || o.order_status == "Delayed" 
                || o.order_status == "Enroute"
             group o by o.order_status
             select new 
                    {
                        orderStatus = x.Key,
                        Counts = x.Count()
                    };
网友答案:

I think you want to group by Status and count total number of orders in each group (I build a simple console program to demonstrate). I suppose the data is:

Orders  Status
8       Created
3       Delayed
4       Enroute
2       Created
1       Delayed

Order.cs

public class Order
{
    public Order(int orderId, string status)
    {
        OrderId = orderId;
        Status = status;
    }

    public int OrderId { get; set; }
    public string Status { get; set; }
}

Program.cs

class Program
{
    static void Main(string[] args)
    {
        // Data
        var orders = new List<Order>
        {
            new Order(8, "Created"),
            new Order(3, "Delayed"),
            new Order(4, "Enroute"),
            new Order(2, "Created"),
            new Order(1, "Delayed"),
        };

        // Query
        var query = orders
            .GroupBy(x => x.Status)
            .Select(x => new {Status = x.Key, Total = x.Count()});

        // Display
        foreach (var item in query)
        {
            Console.WriteLine(item.Status + ": " + item.Total);
        }
        Console.ReadLine();
    }
}

The one you need to focus in is query. After using GroupBy, you will have a list of groups. For each group, the Key is the criteria to group (here is the Status). Then, we call Count() to get the total number of element in that group.

So, from the program above, the output should be:

Created: 2
Delayed: 2
Enroute: 1
相关阅读:
Top