How to write left join, group by and average in c# entity framework Linq

Question

To put it in simple terms i need to write the following SQL in entity framework. I would prefer to do it in Lynq. I'm using mysql

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

What I have did so far is below

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 => m.Rate)
           };

I have been struggling o do this the whole day. Any help would be highly valuable and appreciated. Thank you in advance.

The above is not working, as m is referencing the object of Product.

`group p by p.Id into gp` groups products together, you could try just `group by p.Id into gp` and see what object is referenced by m. Or also `group by o.ProductId into gp`, since `p.Id` and `o.ProductId` are equal anyways. — Keyur PATEL, Jun 16 '17 at 04:12
Try using `group by p.Id into gp select new { p.Id, p.Price, p.CategoryId, gp.Average(m => m.ord.rate)};`. The referenced object by `m` is `ord` which contains joined `Order` table inside it. — Tetsuya Yamamoto, Jun 16 '17 at 04:18
Your sql query should not execute as you are selecting the columns that are not listed in group by clause. Have you tried executing the sql in sql server? — Jenish Rabadiya, Jun 16 '17 at 04:20
is `group by` a valid term in linq. its giving this error `CS1525 Invalid expression term 'by'` — Faraj Farook, Jun 16 '17 at 04:34
@JenishRabadiya Yes, the above SQL is working fine and giving me the results as intended. I'm using MySQL. I know what you mean, but may be because, id is the primary key, it's working so. Anyhow, lets say if i change the query to have multiple group by, even at that point, can you help me out getting the correct linq for the above. — Faraj Farook, Jun 16 '17 at 04:37
Well, No I assumed you are using SQL server and this query want execute with SQL server. You should mention that in your question. — Jenish Rabadiya, Jun 16 '17 at 05:02

Faraj Farook · Accepted Answer · 2017-06-18T23:52:13.170

Thank you everyone who tried to help me out. After a struggle the below worked for me.

    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 ord by p into gp
        select new
        {
            Id = gp.Key.Id,                                        
            Price = gp.Key.Price,
            CategoryId = gp.Key.CategoryId,
            Rate = gp.Average(m => m == null ? 0 : m.Rate)
        };

score 0 · Answer 2 · answered Jun 16 '17 at 04:56

0

You can try this to find the average from your order table, here the thing is you need select the left outer join result and then do grouping up on it.

(from p in products
 join o in Orders on p.id equals o.productId
 into pg
 from g in pg.DefaultIfEmpty()
 select  new { p.id, p.categoryId, p.price, g?.rate} into lg
 group lg by lg.id into sg
 from s in sg
 select new { s.id, s.price, rate =sg.Average(r=> r.rate)}).Distinct().ToList();

answered Jun 16 '17 at 04:56

Sathyajith

3,836
3
15
28

Thank you @SAJ I found the solution, yet I will try this solution as well. – Faraj Farook Jun 16 '17 at 05:11

How to write left join, group by and average in c# entity framework Linq

2 Answers2

Linked