How to change sql query left join to linq query

Question

I want to change SQL Server statement to linq. I'm beginner for linq. I need to modify linq statement in my application but I understand the SQL statement.

This is my SQL Server statement (result is ok for me). I want to change this SQL statement to a linq statement.

 SELECT 
    oh.OrderNo, 
    oh.PONo, 
    oh.CostCenter, 
    oh.ExpectedDeliveryDate,
    do.DeliveryDate,
    oh.PrimaryContactPerson,
    oh.AlternateContactPerson,
    oh.PrimaryContactNo,
    oh.AlternateContactNo,
    oh.CreatedDate,
    oh.CreatedDate,
    oh.WaybillNo,
    do.DeliveryStatus,
    oh.Email,
    oh.Address,
    oh.PostalCode,
    oh.City,
    oh.Branch,
    oh.DONO,
    ih.InvoiceNo,
    ih.IsPaid
FROM dbo.tbl_OrderHeader AS oh
LEFT JOIN dbo.tbl_DeliveryOrder AS do ON oh.ID = do.OrderHeaderID
LEFT JOIN dbo.tbl_InvoiceHeader AS ih ON oh.ID = ih.OrderHeaderID
LEFT JOIN Admin.tbl_UserAccount AS ua ON oh.CreatedUserID = ua.ID
LEFT JOIN Admin.tbl_UserProfile AS up ON ua.UserProfileID = up.ID
WHERE oh.Active = 1 AND up.CompanyID = '05ba4b10-79d1-4367-a5ed-077be72dae86'
ORDER BY oh.OrderNo DESC

This is need to modify my linq statement. (Result not same with sql statement)

public IList<OHList> GetValues_Admin(Guid companyID)
    {
        IList<OHList> tblObj = null;
        try
        {

            tblObj = (from OH in _entities.tbl_OrderHeader

                  join ua in _entities.tbl_UserAccount on OH.CreatedUserID equals ua.ID
                  join up in _entities.tbl_UserProfile on ua.UserProfileID equals up.ID
                  join IH in _entities.tbl_InvoiceHeader on OH.ID equals IH.OrderHeaderID
                  join DO in _entities.tbl_DeliveryOrder on OH.ID equals DO.OrderHeaderID
                  into O                          
                  from DO in O.DefaultIfEmpty()
                  where (OH.Active == true) && (up.CompanyID == companyID)
                  orderby OH.OrderNo descending
                  select new OHList
                  {
                      OrderNo = OH.OrderNo,
                      PONo = OH.PONo,
                      CostCenter = OH.CostCenter,
                      ExpectedDeliveryDate = OH.ExpectedDeliveryDate,
                      DeliveryDate = DO.DeliveryDate,
                      PrimaryContactPerson = OH.PrimaryContactPerson ?? "",
                      AlternateContactPerson = OH.AlternateContactPerson ?? "",
                      PrimaryContactNo = OH.AlternateContactNo ?? "",
                      AlternateContactNo = OH.AlternateContactNo ?? "",
                      CreatedDate = OH.CreatedDate,
                      OrderDate = OH.CreatedDate,
                      WaybillNo = OH.WaybillNo,
                      DeliveryStatus = DO.DeliveryStatus,
                      Email = OH.Email ?? "",
                      Address = OH.Address ?? "",
                      PostalCode = OH.PostalCode ?? "",
                      City = OH.City ?? "",
                      Branch = OH.Branch,
                      DONo = DO.DONo,
                      InvoiceNo = IH.InvoiceNo ?? "",
                      IsPaid = IH.IsPaid

                  }).ToList();

    }
    catch (Exception ex)
    {
        base.Exception(ex);
    }
    return tblObj;
}

yap. MS SQL query result is ok for me. i want to change that query to LINQ. — B M, Sep 17 '14 at 04:27
i am saying that ur MSSQL where clause is different from ur LINQ where clause... — Usman, Sep 17 '14 at 04:31
may be this helps:http://stackoverflow.com/questions/17142151/linq-to-sql-multiple-tables-outer-join — Ehsan Sajjad, Sep 17 '14 at 04:40

score 0 · Accepted Answer · edited May 23 '17 at 12:27

I converted to Linq with this code.
Thank for all suggestion.
Reference from this link.

 public IList<OHList> GetValues_Admin(Guid companyID)
{
IList<OHList> tblObj = null;
try
{

    tblObj = (from OH in _entities.tbl_OrderHeader
              join DO in _entities.tbl_DeliveryOrder on OH.ID equals DO.OrderHeaderID into t1
              from t11 in t1.DefaultIfEmpty()
              join IH in _entities.tbl_InvoiceHeader on OH.ID equals IH.OrderHeaderID into t2
              from t22 in t2.DefaultIfEmpty()
              join UA in _entities.tbl_UserAccount on OH.CreatedUserID equals UA.ID                          
              join UP in _entities.tbl_UserProfile on UA.UserProfileID equals UP.ID
              where (OH.Active == true) && (UP.CompanyID == companyID)
              orderby OH.OrderNo descending
              select new OHList 
              {
                  OrderNo = OH.OrderNo,
                  PONo = OH.PONo,
                  CostCenter = OH.CostCenter,
                  ExpectedDeliveryDate = OH.ExpectedDeliveryDate,
                  DeliveryDate = t11.DeliveryDate,
                  PrimaryContactPerson = OH.PrimaryContactPerson ?? "",
                  AlternateContactPerson = OH.AlternateContactPerson ?? "",
                  PrimaryContactNo = OH.AlternateContactNo ?? "",
                  AlternateContactNo = OH.AlternateContactNo ?? "",
                  CreatedDate = OH.CreatedDate,
                  OrderDate = OH.CreatedDate,
                  WaybillNo = OH.WaybillNo,
                  DeliveryStatus = t11.DeliveryStatus,
                  Email = OH.Email ?? "",
                  Address = OH.Address ?? "",
                  PostalCode = OH.PostalCode ?? "",
                  City = OH.City ?? "",
                  Branch = OH.Branch,
                  DONo = t11.DONo,
                  InvoiceNo = t22.InvoiceNo ?? "",
                  IsPaid = (t22.IsPaid == true) ? true : (bool?)null
              }).ToList();
}
catch (Exception ex)
{
    base.Exception(ex);
}
    return tblObj;
}

How to change sql query left join to linq query

1 Answers1