public static void RecreateCleanDatabase()
{
using var context = new ShoesContext(quiet: true);
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}
public static void PopulateDatabase()
{
using var context = new ShoesContext(quiet: true);
context.AddRange(
new Person
{
FirstName = "Jim",
MiddleInitial = "A",
LastName = "Bob",
Age = 20,
Feet = new Feet {
Size = 11
},
Shoes = { new() { Style = "Sneakers", Age = 19 }, new() { Style = "Dress", Age = 20 } }
},
public HomeController()
{
db = new ShoesContext();
}
public static void Translate_GroupBy_followed_by_FirstOrDefault_over_group()
{
Console.WriteLine($">>>> Sample: {nameof(Translate_GroupBy_followed_by_FirstOrDefault_over_group)}");
Console.WriteLine();
Helpers.RecreateCleanDatabase();
Helpers.PopulateDatabase();
// Example 1. From #12088
using (var context = new ShoesContext())
{
#region GroupBy1
var people = context.People
.Include(e => e.Shoes)
.GroupBy(e => e.FirstName)
.Select(
g => g.OrderBy(e => e.FirstName)
.ThenBy(e => e.LastName)
.FirstOrDefault())
.ToList();
#endregion
Console.WriteLine();
foreach (var person in people)
{
Console.WriteLine($"{person.FirstName} {person.MiddleInitial} {person.LastName} has {person.Shoes.Count} pairs of shoes.");
}
Console.WriteLine();
}
// Example 2. From #16648
using (var context = new ShoesContext())
{
#region GroupBy2
var group = context.People
.Select(
p => new
{
p.FirstName,
FullName = p.FirstName + " " + p.MiddleInitial + " " + p.LastName
})
.GroupBy(p => p.FirstName)
.Select(g => g.First())
.First();
#endregion
Console.WriteLine();
Console.WriteLine($"First name: {group.FirstName} Full name: {group.FullName}");
Console.WriteLine();
}
// Example 3. From #12640
using (var context = new ShoesContext())
{
#region GroupBy3
var people = context.People
.Where(e => e.MiddleInitial == "Q" && e.Age == 20)
.GroupBy(e => e.LastName)
.Select(g => g.First().LastName)
.OrderBy(e => e.Length)
.ToList();
#endregion
Console.WriteLine();
foreach (var person in people)
{
Console.WriteLine(person);
}
Console.WriteLine();
}
// Example 4: From #18037
using (var context = new ShoesContext())
{
#region GroupBy4
var results = (from person in context.People
join shoes in context.Shoes on person.Age equals shoes.Age
group shoes by shoes.Style
into people
select new
{
people.Key,
Style = people.Select(p => p.Style).FirstOrDefault(),
Count = people.Count()
})
.ToList();
#endregion
Console.WriteLine();
foreach (var result in results)
{
Console.WriteLine($"{result.Key}: {result.Style} Count: {result.Count}");
}
Console.WriteLine();
}
// Example 5. From #12601
using (var context = new ShoesContext())
{
#region GroupBy5
var results = context.People
.GroupBy(e => e.FirstName)
.Select(g => g.First().LastName)
.OrderBy(e => e)
.ToList();
#endregion
Console.WriteLine();
foreach (var result in results)
{
Console.WriteLine(result);
}
Console.WriteLine();
}
// Example 6. From #12600
using (var context = new ShoesContext())
{
#region GroupBy6
var results = context.People.Where(e => e.Age == 20)
.GroupBy(e => e.Id)
.Select(g => g.First().MiddleInitial)
.OrderBy(e => e)
.ToList();
#endregion
Console.WriteLine();
foreach (var result in results)
{
Console.WriteLine(result);
}
Console.WriteLine();
}
// Example 7. From #25460
using (var context = new ShoesContext())
{
#region GroupBy7
var size = 11;
var results
= context.People
.Where(
p => p.Feet.Size == size &&
p.MiddleInitial != null &&
p.Feet.Id != 1)
.GroupBy(
p => new
{
p.Feet.Size,
p.Feet.Person.LastName
})
.Select(
g => new
{
g.Key.LastName,
g.Key.Size,
Min = g.Min(p => p.Feet.Size),
})
.ToList();
#endregion
Console.WriteLine();
foreach (var result in results)
{
Console.WriteLine($"Last name = {result.LastName} Min = {result.Min} Size = {result.Size}");
}
Console.WriteLine();
}
// Example 8. From #24869
using (var context = new ShoesContext())
{
#region GroupBy8
var result = context.People
.Include(x => x.Shoes)
.Include(x => x.Feet)
.GroupBy(
x => new
{
x.Feet.Id,
x.Feet.Size
})
.Select(
x => new
{
Key = x.Key.Id + x.Key.Size,
Count = x.Count(),
Sum = x.Sum(el => el.Id),
SumOver60 = x.Sum(el => el.Id) / (decimal)60,
TotalCallOutCharges = x.Sum(el => el.Feet.Size == 11 ? 1 : 0)
})
.Count();
#endregion
Console.WriteLine();
Console.WriteLine($"Count = {result}");
Console.WriteLine();
}
// Example 9. From #24591
using (var context = new ShoesContext())
{
#region GroupBy9
var results = context.People
.GroupBy(n => n.FirstName)
.Select(g => new
{
Feet = g.Key,
Total = g.Sum(n => n.Feet.Size)
})
.ToList();
#endregion
Console.WriteLine();
foreach (var result in results)
{
Console.WriteLine($"{result.Feet}: {result.Total}");
}
Console.WriteLine();
}
// Sample 10. From #24695 closed as duplicate of #13805.
using (var context = new ShoesContext())
{
#region GroupBy10
var results = from Person person1
in from Person person2
in context.People
select person2
join Shoes shoes
in context.Shoes
on person1.Age equals shoes.Age
group shoes by
new
{
person1.Id,
shoes.Style,
shoes.Age
}
into temp
select
new
{
temp.Key.Id,
temp.Key.Age,
temp.Key.Style,
Values = from t
in temp
select
new
{
t.Id,
t.Style,
t.Age
}
};
#endregion
Console.WriteLine();
foreach (var result in results)
{
Console.WriteLine($"{result.Id}: {result.Age} year old {result.Style}");
foreach (var value in result.Values)
{
Console.WriteLine($" {value.Id}: {value.Age} year old {value.Style}");
}
}
Console.WriteLine();
}
// Sample 11. From #19506 closed as duplicate of #13805.
using (var context = new ShoesContext())
{
#region GroupBy11
var grouping = context.People
.GroupBy(i => i.LastName)
.Select(g => new { LastName = g.Key, Count = g.Count(), First = g.FirstOrDefault(), Take = g.Take(2) })
.OrderByDescending(e => e.LastName)
.ToList();
#endregion
Console.WriteLine();
foreach (var group in grouping)
{
Console.WriteLine($"LastName: {group.LastName} Count: {group.Count} First: {group.First.FirstName} {group.First.MiddleInitial} {group.First.LastName}");
foreach (var person in group.Take)
{
Console.WriteLine($" {person.FirstName} {person.MiddleInitial} {person.LastName}");
}
}
Console.WriteLine();
}
// Sample 12. From #13805
using (var context = new ShoesContext())
{
#region GroupBy12
var grouping = context.People
.Include(e => e.Shoes)
.OrderBy(e => e.FirstName)
.ThenBy(e => e.LastName)
.GroupBy(e => e.FirstName)
.Select(g => new { Name = g.Key, People = g.ToList() })
.ToList();
#endregion
Console.WriteLine();
foreach (var group in grouping)
{
foreach (var person in group.People)
{
Console.WriteLine($"{person.FirstName} {person.LastName} has {person.Shoes.Count} pairs of shoes.");
}
}
Console.WriteLine();
}
// Sample 13. From #12088
using (var context = new ShoesContext())
{
#region GroupBy13
var grouping = context.People
.GroupBy(m => new { m.FirstName, m.MiddleInitial })
.Select(am => new
{
Key = am.Key,
Items = am.ToList()
})
.ToList();
#endregion
Console.WriteLine();
foreach (var group in grouping)
{
Console.WriteLine($"Group: {group.Key}");
foreach (var person in group.Items)
{
Console.WriteLine($" {person.FirstName} {person.MiddleInitial} {person.LastName}");
}
}
Console.WriteLine();
}
}
