如何在C#实体框架Linq中编写左联接，分组和平均 [英] How to write left join, group by and average in c# entity framework Linq

问题描述

简单来说，我需要在实体框架中编写以下SQL.我宁愿在Lynq中这样做.我正在使用mysql

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.

以上操作无效，因为 m 引用了 Product 的对象.

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

推荐答案

感谢所有尝试帮助我的人.经过一番努力，下面的东西对我有用.

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)
        };

这篇关于如何在C#实体框架Linq中编写左联接，分组和平均的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！