Querying an Entity Data Model - Entity Framework 6 Recipes - page 98

To get all of the events grouped by state and then city, follow the pattern in Listing 3-33.
Listing 3-33. Grouping by Multiple Properties
using (var context = new EFRecipesEntities())
{
// delete previous test data
context.Database.ExecuteSqlCommand("delete from chapter3.event");
// add new test data
context.Events.Add(new Event { Name = "TechFest 2010",
State = "TX", City = "Dallas" });
context.Events.Add(new Event { Name = "Little Blue River Festival",
State = "MO", City = "Raytown" });
context.Events.Add(new Event { Name = "Fourth of July Fireworks",
State = "MO", City = "Raytown" });
context.Events.Add(new Event { Name = "BBQ Ribs Championship",
State = "TX", City = "Dallas" });
context.Events.Add(new Event { Name = "Thunder on the Ohio",
State = "KY", City = "Louisville" });
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Using LINQ");
var results = from e in context.Events
// create annonymous type to encapsulate composite
// sort key of State and City
group e by new { e.State, e.City } into g
select new
{
State = g.Key.State,
City = g.Key.City,
Events = g };
Console.WriteLine("Events by State and City...");
foreach (var item in results)
{
Console.WriteLine("{0}, {1}", item.City, item.State);
foreach (var ev in item.Events)
{
Console.WriteLine("\t{0}", ev.Name);
}
}
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("\nUsing Entity SQL");
var esql = @"select e.State, e.City, GroupPartition(e) as Events
from Events as e
group by e.State, e.City";