Linq to Entities Join with Group By

Question

Hello,

I'm trying to figure out how to perform a query using Linq to Entities.  I have it working in sql just fine but for some reason I keep coming up short using Linq.  The SQL query is:

SELECT Category.CategoryName, COUNT(ReuseForm.CategoryId) AS count, SUM(ReuseForm.CustomerCost) AS cost, SUM(Product.PurchasePrice) AS msrp
FROM   ReuseForm INNER JOIN
				 Category ON ReuseForm.CategoryId = Category.CategoryId INNER JOIN
				 Product ON ReuseForm.InventoryCode = Product.InventoryCode
GROUP BY Category.CategoryName

Any thoughts on how to best construct this query?

Thanks,
Jason

Answer 1

Hello Jason,

Welcome back to EF forum and thanks for your thread!

The sql built-in function COUNT cannot be directly translated to Linq Query. However, we can call the Count method to get what we want as shown below (Oh, I’m use Northwind database, please modify the query for your requirement):

var query = from c in db.Categories
         join p in db.Products
         on c.CategoryID equals p.CategoryID
         group new {c, p} by new {c.CategoryName} into g
         select new 
         {
              g.Key.CategoryName,
              SumPrice = (decimal?)g.Sum(pt=>pt.p.UnitPrice),
              Count = g.Select(x=>x.c.CategoryID).Distinct().Count()
         };

Please have  a try and let me know if it does not work.

-Best Regards,
Roahn Luo MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact [e-mail]
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

---

Welcome to Microsoft All-In-One Code Framework to download or request code samples from Microsoft Community Team!

Answer 2

Hello,

I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

Have a nice day!

 

---

Roahn Luo [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

It's work perfect!! 

Thanks!

Answer 4

It really helpful, Thank so much!! 

Answer 5

public IQueryable<Expanse> Get(string user, DateTime date)
        {


            return (from c in db.Category
                    join p in db.Expanse
                    on c.Id equals p.CategoryId
                    where p.User.Equals(user) && p.Date.Equals(date)
                    group new { c, p } by new { c.Type } into g

                    select new
                    {
                        amount,
                        date,
                        type,

                    });.AsQueryable();

}

     Severity Code  Description Project File Line Suppression State
Error CS0266  Cannot implicitly convert type 'System.Linq.IQueryable<<anonymous type: System.DateTime date, decimal amount, string type>>' to 'System.Linq.IQueryable<XpanseWebApi.Models.Expanse>'. An explicit conversion exists (are you missing a cast?)