CODEWITHSUNDEEP
c#asp.net.netlinqlinq-to-sql
user4523894
user4523894

Reputation: 143

A problems with display data from linq queries in grid

I make this query. LinqSQl

         var query = from order in mydb.Orders
                    join customer in mydb.Customers on order.CustomerID    equals customer.CustomerID
                    join employee in mydb.Employees on order.EmployeeID equals employee.EmployeeID
                    join shipper in mydb.Shippers on order.ShipVia equals shipper.ShipperID
                    join orderdetail in mydb.Order_Details on order.OrderID equals orderdetail.OrderID
                    join product in mydb.Products on orderdetail.ProductID equals product.ProductID
                    select new
                                 {
                                     OrderID = order.OrderID,
                                     CustomerName = customer.CompanyName,
                                     EmployeeName = employee.FirstName,
                                     ShipperName = shipper.CompanyName,
                                     Products = product.ProductName,
                                     TotalPrice = orderdetail.UnitPrice * orderdetail.Quantity

                                 }
                        into d
                        group d by d.OrderID;

then I want display this data in datagrid

there is my markup:

              <%@ Page Title="Home Page" Language="C#"  AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="asp1._Default" %>
 <!DOCTYPE html>
  <html xmlns="http://www.w3.org/1999/xhtml">
 <head id="Head1" runat="server">
 </head>
  <body>
<form id="form1" runat="server">
<div>
     <asp:GridView ID="Grid" runat="server" AutoGenerateColumns="false"  
           CellPadding="4" ForeColor="#333333"  Width="600px">
        <Columns>
             <asp:BoundField DataField="OrderID" HeaderText="Name" />
             <asp:BoundField DataField="CustomerName" HeaderText="CustomerName" />
             <asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName" />
             <asp:BoundField DataField="ShipperName" HeaderText="ShipperName" />
             <asp:BoundField DataField="Products" HeaderText="Products" />
             <asp:BoundField DataField="TotalPrice" HeaderText="TotalPrice" />
        </Columns>   
        <HeaderStyle BackColor="#7961da"  Font-Bold="True" ForeColor="White" />         
        <RowStyle BackColor="#F7F6F3"  ForeColor="#333333" />
      </asp:GridView>
</div>
</form>
</body>
 </html>

thus method

         Grid.DataSource = query.ToList();
         Grid.DataBind();

And I get error: A field or property with the name 'OrderID' was not found on the selected data source.

What is my problem?

because when I use this query

  var query1 = from order in mydb.Orders
                     join customer in mydb.Customers on order.CustomerID    equals customer.CustomerID
                     join employee in mydb.Employees on order.EmployeeID equals employee.EmployeeID
                     join shipper in mydb.Shippers on order.ShipVia equals shipper.ShipperID
                     join orderdetail in mydb.Order_Details on order.OrderID equals orderdetail.OrderID
                     join product in mydb.Products on orderdetail.ProductID equals product.ProductID

                     select new
                     {

                         OrderID = order.OrderID,
                         CustomerName = customer.CompanyName,
                         EmployeeName = employee.FirstName,
                         ShipperName = shipper.CompanyName,
                         Products = product.ProductName,
                         TotalPrice = orderdetail.UnitPrice * orderdetail.Quantity
                     };
                                 Grid.DataSource = query1;
                                 Grid.DataBind();

I display data, but they aren't grouped.

I get this data

OrderId| CustomerName| EmployeeName |ShipperName | Products| TotalPrice

1          Apple        Kevin            AIG        Iphone     1000

1          Apple        Kevin            AIG        IPAD     1100

2         Samsung          John           KMP        S6       900

2         Samsung          John           KMP        S5       800

I want to get this result.

OrderId| CustomerName| EmployeeName |ShipperName | Products   | TotalPrice

1          Apple        Kevin            AIG       Iphone,Ipad     2100

2         Samsung          John           KMP        S6,s5       1700

Upvotes: 0

Views: 235

Answers (1)

Richard
Richard

Reputation: 108975

When you use group by in the LINQ expression it changes the type of the result from IEnumerable<AnonType> to IEnumerable<IGrouping<int, AnonType>> as OrderID apears to be a int (see the documentation for the method group by uses on MSDN).

Thus you cannot just display as a simple list of the anonymous type.

If you want to order the results by OrderId then group by is a waste of time: use order by to sort.

If you want to process all objects for each OrderID then you need to group.

Edit based on amended question:

Before displaying the data, it appears you want to sum up the runs by OrderId, specically:

  • Sum up TotalPrice
  • Join the Prodcts with comma separators.

This is easy enough to do, but will mean creating new instances. Starting with groupedData from previous query:

var outputData = groupedData.Select(g => new {
                                      OrderId = g.Key,
                                      […]
                                      Products = String.Join(", ", g.Select(x => x.Products)),
                                      TotalPrice = g.Select(x => x.TotalPrice).Sum()
                                    });

Each object from the group by is a IGrouping<int, AnonType> which is an IEnumerable<AnonType> with an additional Key property (what it is grouped by); thus can use the usual LINQ operators to perform the aggregations.

Upvotes: 1

Related Questions