LINQ Query to get data

Question

I have got 4 XML files that contain data in this format. This data is actually from Microsoft Northwind database but I have got some tables in the XML format. The full relationship diagram is available here.

Orders

<Orders>
  <Order>
    <OrderID>10248</OrderID>
    <CustomerID>VINET</CustomerID>
    <EmployeeID>5</EmployeeID>
  </Order>
.............
.............
.............

OrderDetails

<OrderDetails>
  <OrderDetail>
    <OrderID>10248</OrderID>
    <Quantity>12</Quantity>
    <UnitPrice>14.0000</UnitPrice>
  </OrderDetail>
  <OrderDetail>
    <OrderID>10248</OrderID>
    <Quantity>10</Quantity>
    <UnitPrice>9.8000</UnitPrice>
  </OrderDetail>
.............
.............
.............

Employees

<Employee>
    <EmployeeID>5</EmployeeID>
    <FirstName>Steve</FirstName>
    <LastName>Buchanan</LastName>
  </Employee>
  <Employee>
    <EmployeeID>6</EmployeeID>
    <FirstName>Michael</FirstName>
    <LastName>Suyama</LastName>
  </Employee>
.............
.............
.............

Customers

<Customer>
    <CustomerID>VINET</CustomerID>
    <CompanyName>Vins et alcools Chevalier</CompanyName>
    <ContactName>Paul Henriot</ContactName>
  </Customer>
  <Customer>
    <CustomerID>WANDK</CustomerID>
    <CompanyName>Die Wandernde Kuh</CompanyName>
    <ContactName>Rita Müller</ContactName>
  </Customer>
  <Customer>
.............
.............
.............

Now I want to get a list of objects such that each object contains the following:

OrderId (from the Orders table - example - 10248)
Company Name for the above order id (from Customers table - example - Vins et alcools Chevalier)
Contact Name for the above order id (from Customers table - example - Paul Henriot)
Employee Name for an employee id for the corressponding order id (from Employees table - example - Steve Buchanan)
Total Quantity for the above order id from OrderDetails table. This will be 12 + 10 = 22 because there are two orders for order id 10248
Total Price for the above order id from the OrderDetails table. This will be 12*14 + 10*9.8 = 266.

So one of the objects will look like this - {10248, Vins et alcools Chevalier, Paul Henriot, Steve Buchanan, 22, 266}

Now I am able to write a LINQ query to get me orderid, contactname and company name like this:

var list = from o in ordersList
                join cl in customersList
                on o.CustomerId equals cl.CustomerId
                select new
                {
                    o.OrderId,
                    cl.CompanyName,
                    cl.ContactName
                };

But this only gives me three things. I am struggling to get all 6 things that are needed for the object to contain. Also how to perform the calculation since one orderid in orders table can have multiple orderdetails. For example - for order id 10248 we have 2 order details.

Thanks

Damith Damith · Accepted Answer · 2014-10-12T05:00:42

you need multiple joins and group by like below

var list = from o in ordersList
            join cl in customersList
            on o.CustomerID equals cl.CustomerID
            join ol in orderDetailsList
            on o.OrderID equals ol.OrderID
            join e in employeeList
            on o.EmployeeID equals e.EmployeeID
            select new
            {
                o.OrderID,
                cl.CompanyName,
                cl.ContactName,
                EmployeeName = e.FirstName + " " +e.LastName,
                ol.Quantity,
                ol.UnitPrice
            };
var result =  list.GroupBy(x => x.OrderID).Select(g => new
{
    OrderID = g.Key,
    CompanyName = g.First().CompanyName,
    ContactName = g.First().ContactName,
    EmployeeName = g.First().EmployeeName,
    TotalQuantity = g.Sum(x => x.Quantity),
    TatalPrice = g.Sum(x => x.Quantity * x.UnitPrice)
});

LINQ Query to get data

1 Answers