title: Join two tables using entity framework and linq in c#
body: <p>I am trying to combine two tables in c# for orderManagement.where "SalesOrderDetails" table returns <a href="https://i.stack.imgur.com/IvKxl.png" rel="nofollow noreferrer">query results 1</a> comma seperated product code and quantity </p>
  
  <p><a href="https://i.stack.imgur.com/BDkeL.png" rel="nofollow noreferrer">query results 2</a></p>
  
  <p>"SalesOrder" table return order detail.
  I have Written stored procedure</p>
  
  <pre><code>ALTER procedure [dbo].[SP_GetAllOrders] 
  AS
  BEGIN
  
  
  SELECT
      SalesOrder.OrderDate,
      SalesOrder.OrderTitle,
      SalesOrder.OrderPriority,
      Customer.Lname,
      Customer.Fname,
      OrderType.OrderTypeName,
      (
          SELECT
              CAST( ProductCode AS varchar ) + ':' + CAST( Quantity AS varchar ) + ','
          FROM
              SalesOrderDetails 
          WHERE
              SalesOrderDetails.SalesOrderId = SalesOrder.SalesOrderId
          FOR xml path('')
      ) AS 'Product'
  FROM
      SalesOrder
      INNER JOIN Customer ON SalesOrder.CustomerId = Customer.CustomerId
      INNER JOIN OrderType ON SalesOrder.OrderTypeId = OrderType.OrderTypeId
  
  END
  </code></pre>
  
  <p>It gives me perfect output.same output I want without using stored procedure.
  I want same output using entity framework,linq and c# asp.net.I am stuck here</p>
  
  <p>this is what i have tried</p>
  
  <p>TrainingDemoEntities entity = new TrainingDemoEntities();</p>
  
  <pre><code>    var details = (from order in entity.SalesOrders
                     join cust in entity.Customers
                     on order.CustomerId equals cust.CustomerId
                     join ordertypevalue in entity.OrderTypes
                         on order.OrderTypeId equals ordertypevalue.OrderTypeId
                     select new
                     {
                         orderId = order.SalesOrderId,
                         orderDate = order.OrderDate,
                         orderTitle = order.OrderTitle,
                         orderPriority = order.OrderPriority,
                         orderType = order.OrderType,
                         productstr = string.Join(",", (from prod in entity.SalesOrderDetails
                                                        where prod.SalesOrderId == order.SalesOrderId
                                                        select prod.ProductCode + ":" + prod.Quantity))
                     }).ToList();
  </code></pre>