title: Left join in Entity Framework with 1-to-many relation
body: <p>I have five tables:</p>
  
  <pre><code>Coupons
  Coupon_Redemptions
  User_Coupon
  Wash
  Account
  </code></pre>
  
  <p>This is <code>SQL Query</code> that works in <code>SQL Server</code> and <code>Entity Raw SQL</code>, but can't get it to work with <code>LINQ</code> because <code>1-to-many</code> releations, with lazy loading can't manage to get it because User_coupons, Wash... are collections.</p>
  
  <pre><code>SELECT 
      C.Id AS "CouponId", C.Coupon_code AS "CouponCode", 
      C.Discount_amount AS "DiscountAmount", 
      C.Valid_for_all AS "ValidForAll", C.Expiration_date AS "ExpirationDate", 
      R.Redemption_date AS "RedemptionDate",
      U.Date_added AS "DateAddedToUser", W.Id AS "WashId", A.Name  
  FROM 
      Coupon C
      LEFT JOIN User_coupon U on U.CouponId = C.Id
      LEFT JOIN Coupon_redemption R on R.CouponId = C.Id
      LEFT JOIN Wash W on W.CouponId = C.Id
      LEFT JOIN Account A on U.AccountId = A.Id
  </code></pre>
  
  <p>Here is snippet of Relation diagram <a href="https://i.stack.imgur.com/7MARq.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7MARq.jpg" alt="enter image description here"></a></p>
  
  <p>Tried variation on this query, it returns one row. But it looks perfect. </p>
  
  <pre><code>var results =
                  from c in db.Coupons
                  from u in c.User_coupon.DefaultIfEmpty()
                  from r in c.Coupon_redemption.DefaultIfEmpty()
                  from w in c.Washes.DefaultIfEmpty()
                  select new {
                      CouponId = c.Id,
                      CouponCode = c.Coupon_code,
                      DiscountAmount = c.Discount_amount,
                      ValidForAll = c.Valid_for_all,
                      ExpirationDate = c.Expiration_date,
                      RedemptionDate = r.Redemption_date,
                      DateAddedToUser = u.Date_added,
                      WashId = w.Id
                  };
  </code></pre>
  
  <p>Result to String:</p>
  
  <pre><code>SELECT[Extent1].[Id] AS [Id], [Extent1].[Coupon_code] AS [Coupon_code], [Extent1].[Discount_amount] AS [Discount_amount], [Extent1].[Valid_for_all] AS [Valid_for_all], [Extent1].[Expiration_date] AS [Expiration_date], [Extent3].[Redemption_date] AS [Redemption_date], [Extent2].[Date_added] AS [Date_added], [Extent4].[Id] AS [Id1] 
  FROM [dbo].[Coupon] AS [Extent1] 
  LEFT OUTER JOIN [dbo].[User_coupon] AS [Extent2] ON [Extent1].[Id] = [Extent2].[CouponId]
  LEFT OUTER JOIN [dbo].[Coupon_redemption] AS [Extent3] ON [Extent1].[Id] = [Extent3].[CouponId]
  LEFT OUTER JOIN [dbo].[Wash] AS [Extent4] ON [Extent1].[Id] = [Extent4].[CouponId]
  </code></pre>