Entity Framework : Group by and Join

c# entity-framework-6 group-by join

Question

I need to get some statistics from the database, but I can't get it to work as I want. I have two tables, Task and Status, and I need to get the number of Tasks per Status, for example, if I have the statuses New and Closed, and each Task has exactly one Status, I want a list like this:

New      35
Closed   47

This is what I have so far:

var statistics = database.Task
            .GroupBy(t => t.StatusId)
            .Select(g => new { Status = g.Key, Count = g.Count() }).ToList();

This returns the StatusId and the Task count, so all I need to do is to replace the StatusId with Status.Name. Basically I need to combine the above with a join the table Status and then get the Status.Name. But here I fail, can anyone help me?

1
1
2/2/2016 10:47:18 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

Try this:

var statistics = database.Task
    .GroupBy(t => new { Id = t.StatusId, Name = t.Status.Name })
    .Select(g => new { StatusId = g.Key.Id, StatusName = g.Key.Name, Count = g.Count() })
    .ToList();
1
2/2/2016 11:05:49 AM

Related Questions

Cross edmx "join" between Linq to Entities and Entity Framework

You can do this by using Views to represent data in the other database. Read about ...cross database joins in EF
entity-framework linq-to-entities
asked by BritishDeveloper

Join two tables using LINQ to Entities, then group and total the columns from each tables.

the transaction variable is out of scope. If you include it in you grouped result then you can use it....change you group by clause to:...group new { target, transaction } by new { target.ProductGroupID, target.StateID, target.Year } into grouped ...
c# entity-framework linq linq-to-entities
asked by saus

Outer left Entity Framework through Linq To Entities using Where Clause

I have read all the posts related to implementing the equivalent of a LEFT OUTER JOIN in Linq to Entities (.NET 3.5) when using the Entity Framework, but have yet to find a solution to the following problem....Given two tables, represented by the objects below:... public class Foo { public int FooId; // primary key public st...
entity-framework left-join linq-to-entities outer-join
asked by EulerOperator

Convert string to decimal in group join linq query

I have to join two tables but to return only those records in second table where sum of 'Value' of all records associated with the record in first table is the same....from p in db.TPs join n in db.TNs on p.Key equals n.Key where (decimal.Parse(p.Value) == db.TNs.Where( nn => nn.Key == p.Key ) .Sum( nn=> decim...
c# entity-framework linq sql
asked by majkinetor

Entity Framework with Linq, inner Join, Group By, Order By

I have a SQL Query...select Firma.Name as companyName, Taetigkeit.Taetigkeit as skillName, SUM(Zeit) as time from Zeiterfassung inner join Firma On ZEiterfassung.FirmenID = Firma.ID inner join Taetigkeit on Zeiterfassung.TaetigkeitID = Taetigkeit.ID group by Taetigkeit, Firma.Name order by Firma.Name ...And want to "tr...
c# entity-framework linq linq-to-entities sql
asked by Markus_DE_HH

LEFT JOIN or RIGHT JOIN using LINQ Entity-Framework

Trying joining using Linq. What should i use ? Left join or right join?... APPLICANT TABLE PROFILE TABLE APPLICANT_ID|profile_id|Applicant_Name| |profile_id|Applicant_Name 1 | NULL | RAY HEAVENS | | 1 | MARK LAPID 2 | NULL | BEN TULFO | | 2 | SUPER MAN 3 | 1...
asp.net asp.net-mvc c# entity-framework linq
asked by Enrique Gil

Using Join, Group By, and Sum in Entity Framework

My models look like:...public class ReturnItem { public int returnItemId { get ; set; } public int returnRequestId { get; set; } public int quantity { get; set; } public string item { get; set; } } public class ReturnRequest { public int returnRequestId { get; set; } public string orderNumber { get; set; } public IEnum...
c# entity-framework sql
asked by janinaj

Entity Framework left join on multiple tables creates inner join instead

I'm trying to create a query that joins four tables together, only one of which is guaranteed to exist. I've been successful creating left joins in EF before, but never with more than one join. Here is the query:... var details = (from planInfo in context.PlanInfo join templateRec in context.ProductTemplates o...
c# entity-framework entity-framework-6 linq
asked by kindwarrior

Entity Framework forcing Inner Join when Left Join DefaultIfEmpty() Syntax used

I'm trying to create a fairly complex query with the Oracle EF framework (Oracle.ManagedDataAccess.EntityFramework nuget package, version 12.1.2400) with numerous left joins, utilizing the .DefaultIfEmpty() syntax:...from i in dbHR.Identities join p in dbHR.Personals on new { key1 = i.ID, key2 = true, key3 = true } ...
c# entity-framework-6 join linq-to-entities oracle
asked by James

How to use Group By with Join and Min in Linq

I used some classes to stand in for the database, but does this work?...class Product { public int ID; public string Name; public string Description; } class Order { public int ProductID; public DateTime DeliveryDate; } class Program { static void Main(string[] args) { Product[] proTestData = new Product[] { new Pr...
c# entity-framework-6 group-by join linq
asked by BlueMonkMN

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow