public static void Demo_SQLDirekt_NonEntityType()
{
CUI.MainHeadline(nameof(Demo_SQLDirekt_NonEntityType));
using (var ctx = new WWWingsContext())
{
// this does not compile: var flightSet = ctx.FromSql<fligthDTO>("Select FlightNo, Departure, Destination, Date from Flight");
var flightSet = ctx.Set <fligthDTO>().FromSql("Select FlightNo, Departure, Destination, Date from Flight");
Console.WriteLine(flightSet.Count());
foreach (var f in flightSet)
{
Console.WriteLine(f);
}
Console.WriteLine(flightSet.Count());
}
}
/// <summary>
/// geht mit Pseudo-Entitätsklasse "DepartureGrouping"
/// </summary>
public static void GroupBy_SQL_Trick()
{
CUI.MainHeadline(nameof(GroupBy_SQL_Trick));
// Get the number of flights per Departure
using (var ctx = new WWWingsContext())
{
Console.WriteLine(ctx.Database.GetType().FullName);
ctx.Log();
// Map SQL to entity class
var sql = "SELECT Departure, COUNT(FlightNo) AS FlightCount FROM Flight GROUP BY Departure";
var groupSet = ctx.Set <BO.DepartureGrouping>().FromSql(sql).Where(x => x.FlightCount > 5).OrderBy(x => x.FlightCount);
// Output
foreach (var g in groupSet)
{
Console.WriteLine(g.Departure + ": " + g.FlightCount);
}
}
return;
// Groupy by "nested"
using (var ctx = new WWWingsContext())
{
ctx.Log();
var groupSet = from p in ctx.FlightSet
orderby p.FreeSeats
group p by p.Departure into g
select g;
Console.WriteLine("Count: " + groupSet.Count());
foreach (var g in groupSet)
{
Console.WriteLine(g.Key + ": " + g.Count());
foreach (var f in g)
{
Console.WriteLine(" " + f.FlightNo);
}
}
}
}
public static void GroupBy_SQL_NonEntityType()
{
// Get the number of flights per Departure
using (var ctx = new WWWingsContext())
{
// Map SQL to non-entity class
Console.WriteLine(ctx.Database.GetType().FullName);
ctx.Log();
var sql = "SELECT Departure, COUNT(FlightNo) AS FlightCount FROM Flight GROUP BY Departure";
// ERROR!!! Cannot create a DbSet for 'Group' because this type is not included in the model for the context."
var groupSet = ctx.Set <DepartureGroup>().FromSql(sql);
// Output
foreach (var g in groupSet)
{
Console.WriteLine(g.Departure + ": " + g.FlightCount);
}
}
return;
// Groupy by "nested"
using (var ctx = new WWWingsContext())
{
ctx.Log();
var groupSet = from p in ctx.FlightSet
orderby p.FreeSeats
group p by p.Departure into g
select g;
Console.WriteLine("Count: " + groupSet.Count());
foreach (var g in groupSet)
{
Console.WriteLine(g.Key + ": " + g.Count());
foreach (var f in g)
{
Console.WriteLine(" " + f.FlightNo);
}
}
}
}