title: How to write left join, group by and average in c# entity framework Linq
body: <p>To put it in simple terms i need to write the following SQL in entity framework. I would prefer to do it in Lynq. <em>I'm using mysql</em></p>
  
  <pre><code>SELECT `p`.`Id`, p.price, p.categoryid, avg(o.rate)
  FROM `Product` AS `p`
  LEFT JOIN `Order` AS `o` ON `p`.`Id` = `o`.`ProductId`
  group by p.id 
  </code></pre>
  
  <p>What I have did so far is below</p>
  
  <pre><code>var data = from p in _context.Product
             join o in _context.Order on p.Id equals o.ProductId into orderTemp
             from ord in orderTemp.DefaultIfEmpty()
             group p by p.Id into gp
             select new
             {
                 p.Id,
                 p.Price,
                 p.CategoryId,
                 gp.Average(m =&gt; m.Rate)
             };
  </code></pre>
  
  <p>I have been struggling o do this the whole day. Any help would be highly valuable and appreciated. Thank you in advance.</p>
  
  <p>The above is not working, as <code>m</code> is referencing the object of <code>Product</code>.</p>