public static void TrackingMode_Performance()
{
for (int i = 0; i < 10; i++)
{
using (var ctx = new WWWingsContext())
{
ctx.Log(null);
var sw = new Stopwatch();
sw.Start();
var liste1 = ctx.FlightSet.ToList();
sw.Stop();
Console.WriteLine("TRACKING: Loaded objects: " + liste1.Count + " Duration: " + sw.ElapsedMilliseconds);
}
using (var ctx = new WWWingsContext())
{
ctx.Log(null);
var sw = new Stopwatch();
sw.Start();
var liste2 = ctx.FlightSet.AsNoTracking().ToList();
sw.Stop();
Console.WriteLine("NO-TRACKING: Loaded objects: " + liste2.Count + " Duration: " + sw.ElapsedMilliseconds);
}
}
}
public static void GroupBy1()
{
CUI.MainHeadline(nameof(GroupBy1));
// Simple Group by: Min, Max, Sum und Average of FreeSeats per Destination
using (var ctx = new WWWingsContext())
{
Console.WriteLine(ctx.Database.GetType().FullName);
ctx.Log();
var groups1 = (from p in ctx.FlightSet
group p by p.Departure into g
select new { City = g.Key, Min = g.Min(x => x.FreeSeats), Max = g.Max(x => x.FreeSeats), Sum = g.Sum(x => x.FreeSeats), Avg = g.Average(x => x.FreeSeats) });
var count1 = groups1.Count();
Console.WriteLine("Number of groups: " + count1);
if (count1 > 0)
{
// Second roundtrip to the database
foreach (var g in groups1.ToList())
{
Console.WriteLine(g.City + ": Min=" + g.Min + " / Max=" + g.Max + " / Avergae" + g.Avg + " / Sum:" + g.Sum);
}
}
}
}
/// <summary>
/// Attach()/EntityState.Modified --> All Spalten zurückschreiben
/// </summary>
public static void Attached_Flight()
{
CUI.MainHeadline(nameof(Attached_Flight));
Flight f;
CUI.Print("Lade Objekt in Kontextinstanz #1");
using (WWWingsContext ctx1 = new WWWingsContext())
{
ctx1.Log();
f = ctx1.FlightSet.Find(101);
CUI.Print("Objekt ändern");
CUI.Print(f.ToString());
f.Memo = "last changed at " + DateTime.Now;
f.FreeSeats--;
CUI.Print(f.ToString());
var count = ctx1.SaveChanges();
Console.WriteLine("Saved changes: " + count.ToString());
if (count != 1)
{
Debugger.Break();
}
}
}
public static void Demo_AddGraph()
{
CUI.Headline("Demo_AddGraph");
using (var ctx = new WWWingsContext())
{
ctx.Log();
ctx.Database.ExecuteSqlCommand("Delete from Booking where FlightNo = 101");
ctx.Database.ExecuteSqlCommand("Delete from Flight where FlightNo = 101");
var pilot = new Pilot();
pilot.Surname = "testpilot";
//ctx.PilotSet.Add(pilot);
var fneu = new Flight();
fneu.FlightNo = 101;
fneu.Departure = "Essen";
fneu.Destination = "Darmstadt";
fneu.Pilot = pilot;
fneu.Seats = 100;
fneu.Copilot = pilot;
fneu.FreeSeats = 100;
ctx.ChangeTracker.TrackGraph(fneu, (obj) => obj.NodeState = obj.Entry.State = EntityState.Added);
//ctx.ChangeTracker.TrackGraph(fneu, TrackGraph_CallbackAdded);
ctx.FlightSet.Add(fneu);
EFC_Util.PrintChangeInfo(ctx);
var anz = ctx.SaveChanges();
Console.WriteLine("Saved changes: " + anz);
}
}
public static void ComputedColumnWithFormula()
{
CUI.MainHeadline(nameof(ComputedColumnWithFormula));
int flightNo = 101;
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log();
var flight = ctx.FlightSet.Find(flightNo);
Console.WriteLine($"BEFORE: {flight}: Utilization={flight.Utilization:##0.00}%");
flight.FreeSeats -= 10;
//not possible: flight.Utilization = 100;
// The change is not yet visible in the Utilization, since the Utilization is calculated in the DBMS
Console.WriteLine($"After changes: {flight}: Utilization={flight.Utilization:##0.00}%");
ctx.SaveChanges();
// The change in Utilization is now visible
Console.WriteLine($"After saving: {flight}: Utilization={flight.Utilization:##0.00}%");
CUI.Headline("Metadata of Flight properties");
foreach (PropertyEntry p in ctx.Entry(flight).Properties)
{
Console.WriteLine(p.Metadata.Name + ": " + p.Metadata.ValueGenerated);
}
}
}
public static void AddBatch(short Anz = 400)
{
Console.WriteLine(nameof(AddBatch));
using (var ctx = new WWWingsContext())
{
CUI.MainHeadline(Anz + " Add Batch");
var pilot1 = ctx.PilotSet.FirstOrDefault();
var pilot2 = ctx.PilotSet.Skip(1).FirstOrDefault();
ctx.Log();
ctx.Database.ExecuteSqlCommand("Delete from Booking where FlightNo >= 10000");
ctx.Database.ExecuteSqlCommand("Delete from Flight where FlightNo >= 10000");
Stopwatch sw2 = new Stopwatch();
sw2.Start();
for (int i = 0; i < Anz; i++)
{
var newFlight = new Flight();
newFlight.FlightNo = 20000 + i;
newFlight.Pilot = pilot1;
newFlight.Seats = 100;
newFlight.Copilot = pilot2;
newFlight.FreeSeats = 100;
ctx.FlightSet.Add(newFlight);
}
ctx.SaveChanges();
sw2.Stop();
Console.WriteLine(Anz + " Add Batch: " + sw2.ElapsedMilliseconds + "ms");
ctx.Database.ExecuteSqlCommand("Delete from Booking where FlightNo >= 10000");
ctx.Database.ExecuteSqlCommand("Delete from Flight where FlightNo >= 10000");
}
}
/// <summary>
/// GetFlight with MemoryCache (5 sek)
/// </summary>
private static List <Flight> GetFlight1(string departure)
{
string cacheItemName = "FlightSet_" + departure;
// Zugriff auf Cache-Eintrag
System.Runtime.Caching.MemoryCache cache = System.Runtime.Caching.MemoryCache.Default;
List <Flight> flightSet = cache[cacheItemName] as List <Flight>;
if (flightSet == null) // Element ist NICHT im Cache
{
CUI.Print($"{DateTime.Now.ToLongTimeString()}: Cache missed", ConsoleColor.Red);
using (var ctx = new WWWingsContext())
{
ctx.Log();
// Load flights
flightSet = ctx.FlightSet.Where(x => x.Departure == departure).ToList();
}
// Store flights in cache
CacheItemPolicy policy = new CacheItemPolicy();
policy.AbsoluteExpiration = DateTime.Now.AddSeconds(5);
//or: policy.SlidingExpiration = new TimeSpan(0,0,0,5);
cache.Set(cacheItemName, flightSet, policy);
}
else // Data is already in cache
{
CUI.Print($"{DateTime.Now.ToLongTimeString()}: Cache hit", ConsoleColor.Green);
}
return(flightSet);
}
public static void GetFlight_StandardCacheNurLesen(string departure = "Rome")
{
using (var ctx = new WWWingsContext())
{
ctx.Log();
CUI.Headline($"Alle Flights from {departure} laden!");
// Alle Flights laden
var flightSet = ctx.FlightSet.Where(x => x.Departure == departure && x.FlightNo < 300).ToList();
var flightNo = 190; // FlightSet 190 ist ein FlightSet from Rom, der schon geladen wurde // flightSet.ElementAt
CUI.Headline("Ein FlightSet laden mit Find() - allein aus Cache!");
var flight1 = ctx.FlightSet.Find(flightNo);
CUI.Print(flight1?.ToShortString(), ConsoleColor.White);
CUI.Headline("Ein FlightSet laden mit SingleOrDefault() -> Query!");
var flight2 = ctx.FlightSet.SingleOrDefault(x => x.FlightNo == flightNo);
CUI.Print(flight2?.ToShortString(), ConsoleColor.White);
CUI.Headline("Ein FlightSet laden mit FirstOrDefault() -> Query!");
var flight3 = ctx.FlightSet.FirstOrDefault(x => x.FlightNo == flightNo);
CUI.Print(flight3?.ToShortString(), ConsoleColor.White);
CUI.Headline("Ein FlightSet laden mit Where/SingleOrDefault() -> Query!");
var flight4 = ctx.FlightSet.Where(x => x.FlightNo == flightNo).SingleOrDefault();
CUI.Print(flight4.ToShortString(), ConsoleColor.White);
CUI.Headline("Cacheinhalt");
//ctx.FlightSet.Local.Clear();
Console.WriteLine("Flights im Cache: " + ctx.FlightSet.Local.Count);
foreach (var f in ctx.FlightSet.Local) // Local erlaubt Zugriff auf den Cacheinhalt!
{
Console.WriteLine(f.ToShortString());
}
}
}
public static void GroupBy2()
{
CUI.MainHeadline(nameof(GroupBy2));
using (var ctx = new WWWingsContext())
{
ctx.Log();
//Simple Group by(Number of flights per destination)
// BUG in EFCore 2.1 Preview 1
var groups2 = (from p in ctx.FlightSet
group p by p.Departure into g
select new { City = g.Key, Count = g.Count() }).Where(x => x.Count > 5).OrderBy(x => x.Count).ToList();
// First roundtrip to the database (done intentionally here!)
var count2 = groups2.Count();
Console.WriteLine("Number of groups: " + count2);
if (count2 > 0)
{
// Second roundtrip to the database
foreach (var g in groups2.ToList())
{
Console.WriteLine(g.City + ": " + g.Count);
}
}
}
}
public static void TimestampQuery()
{
CUI.Headline("Vor Änderung");
Print();
using (var ctx = new WWWingsContext())
{
ctx.Log();
var flightSet = ctx.FlightSet.Where(f => f.Departure == "Paris").OrderBy(x => x.Timestamp).Take(1);
foreach (var f in flightSet)
{
f.FreeSeats--;
}
ctx.SaveChanges();
CUI.Headline("Nach Änderung");
Print();
CUI.Headline("Abfrage: TS > " + ts.ByteArrayToString());
var geänderteflightSet = ctx.FlightSet.Where(x => x.Timestamp.Compare(ts) > 0).ToList();
foreach (var f in flightSet)
{
Console.WriteLine(f.FlightNo + ": " + f.FreeSeats + "/" + f.Timestamp.ByteArrayToString());
}
}
}
public static void TrackingMode_AsNoTracking()
{
CUI.MainHeadline(nameof(TrackingMode_AsNoTracking));
CUI.Headline("Tracking mode");
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log(null);
var flightSet = ctx.FlightSet.ToList();
var flight = flightSet[0];
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Unchanged
flight.FreeSeats--;
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Modified
int count = ctx.SaveChanges();
Console.WriteLine($"Saved changes: {count}"); // 1
}
CUI.Headline("No-Tracking mode");
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log(null);
var flightSet = ctx.FlightSet.AsNoTracking().ToList();
var flight = flightSet[0];
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Detached
flight.FreeSeats--;
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Detached
int count = ctx.SaveChanges();
Console.WriteLine($"Saved changes: {count}"); // 0
}
}
public static void ColumnsAddedAfterCompilation(bool logging = true)
{
CUI.MainHeadline(nameof(ColumnsAddedAfterCompilation));
// List of additional columns can be read from a config file or the database schema
//List<string> additionalColumnSet = new List<string>() { "BO.Airline;Address;System.String", "BO.Airline;FoundingYear;System.Nullable`1[System.Int32]", "BO.Airline;Bunkrupt;System.Nullable`1[System.Boolean]", "BO.AircraftType;Role;System.String" };
var fileContent = File.ReadAllLines("AddedColumnsConfig.txt");
var additionalColumnSet = fileContent.Where(x => !x.StartsWith("#")).ToList();
// List of additional columns must be set before creating the first instance of the context!
WWWingsContext.AdditionalColumnSet = additionalColumnSet;
using (WWWingsContext ctx = new WWWingsContext())
{
if (logging)
{
ctx.Log();
}
// read any Airline object
var a = ctx.AirlineSet.SingleOrDefault(x => x.Code == "WWW");
if (a == null)
{
throw new ApplicationException("No Airline found!");
}
Console.WriteLine(a);
Console.WriteLine("Extra columns:");
foreach (var col in additionalColumnSet.Where(x => x.StartsWith("BO.Airline")))
{
string columnname = col.Split(';')[1];
Console.WriteLine(col + "=" + ctx.Entry(a).Property(columnname).CurrentValue);
}
}
}
private static List <Flight> GetFlight2Internal(object[] param)
{
using (var ctx = new WWWingsContext())
{
ctx.Log();
string departure = param[0] as string;
// Load flights
return(ctx.FlightSet.Where(x => x.Departure == departure).ToList());
}
}
public static void ExpressionTreeNumerousConditions()
{
CUI.MainHeadline(nameof(ExpressionTreeNumerousConditions));
// Input data
var filters = new SortedDictionary <string, object>()
{
{ "Departure", "Berlin" }, { "Destination", "Rome" }, { "PilotID", 57 }
};
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log();
// Base query
var baseQuery = from flight in ctx.FlightSet where flight.FlightNo < 1000 select flight;
ParameterExpression param = Expression.Parameter(typeof(BO.Flight), "f");
Expression completeCondition = null;
foreach (var filter in filters)
{
// Define condition
Expression left = Expression.Property(param, filter.Key);
Expression right = Expression.Constant(filter.Value);
Expression condition = Expression.Equal(left, right);
// Add to existing conditions using AND operator
if (completeCondition == null)
{
completeCondition = condition;
}
else
{
completeCondition = Expression.And(completeCondition, condition);
}
}
// Create query from expression tree
MethodCallExpression whereCallExpression = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { baseQuery.ElementType },
baseQuery.Expression,
Expression.Lambda <Func <BO.Flight, bool> >(completeCondition, new ParameterExpression[] { param }));
// Create query from expression tree
var Q_Endgueltig = baseQuery.Provider.CreateQuery <BO.Flight>(whereCallExpression);
// Print the result set
Console.WriteLine("Flights found:");
foreach (var f in Q_Endgueltig)
{
Console.WriteLine($"Flight Nr {f.FlightNo} from {f.Departure} to {f.Destination}: {f.FreeSeats} free seats! Pilot: {f.PilotId} ");
}
}
}
// Nun prüfen, ob Timestamp noch stimmt (sonst müsste ich mein altes Objekt noch haben, um All Werte zu vergleichen!
// Achtung: Vergleich mit == geht hier nicht, weil Byte-Array!
//if (!flightOrginal.Timestamp.SequenceEqual(flight.Timestamp))
//{
// throw new System.Data.DBConcurrencyException("Flight " + flight.FlightNo + " wurde from jemand anderem geändert!");
//}
/// <summary>
/// Variante 3b fehlerhaft (Richtig: Original-Objekt noch haben)
/// </summary>
public static void Detached_Flight_ConcurrencyCheck_CloneOrg()
{
CUI.MainHeadline(nameof(Detached_Flight_ConcurrencyCheck_CloneOrg));
Flight flight;
Flight flightOrginal;
CUI.Print("Lade Objekt in Kontextinstanz #1");
using (WWWingsContext ctx1 = new WWWingsContext())
{
ctx1.Log();
//ctx1.Configuration.LazyLoadingEnabled = false;
flight = ctx1.FlightSet.Find(110);
// Kopie anlegen
flightOrginal = Cloner.Clone <Flight>(flight);
}
CUI.Print("Objekt ändern");
CUI.Print(flight.ToString());
flight.FreeSeats--;
flight.Date = flight.Date.AddHours(2);
CUI.Print(flight.ToString());
CUI.Print("Objekt nun speichern mit Kontextinstanz #2");
using (WWWingsContext ctx2 = new WWWingsContext())
{
ctx2.Log();
ctx2.FlightSet.Attach(flight);
var entry = ctx2.Entry(flight);
CUI.Print(ctx2.Entry(flight).State.ToString());
// Nun Originalwerte im Kontext ablegen. Besser als die aktuellen Werte aus DB laden!
// Variante 1:
ctx2.Entry(flight).OriginalValues.SetValues(flightOrginal);
// Variante 2:
//var flightOrgDic = ObjectToDictionaryHelper.ToDictionary(flightOrginal);
//foreach (string propertyName in entry.OriginalValues.PropertyNames)
//{
// entry.Property(propertyName).OriginalValue = flightOrgDic[propertyName];
//}
CUI.Print(ctx2.Entry(flight).State.ToString());
var count = ctx2.SaveChanges();
Console.WriteLine("Saved changes: " + count.ToString());
if (count != 1)
{
Debugger.Break();
}
}
}
public static void ConvertStringToBoolean()
{
CUI.MainHeadline(nameof(ConvertStringToBoolean));
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log();
// Add new Passenger
CUI.Headline("Add new Passenger");
var p = new Passenger();
p.GivenName = "Max";
p.Surname = "Müller";
p.Birthday = new DateTime(2070, 2, 3);
p.Status = 'A';
p.FrequentFlyer = "Yes";
ctx.PassengerSet.Add(p);
var count = ctx.SaveChanges();
Console.WriteLine("Saved Changes: " + count);
Console.WriteLine("Added new Passenger #" + p.PersonID);
// Get raw data from Database as DataReader
CUI.Headline("Raw Data");
var r = ctx.Database.ExecuteSqlQuery("Select p.PersonID, p.Surname, p.Birthday, p.FrequentFlyer from Passenger as p where p.personID= " + p.PersonID);
DbDataReader dr = r.DbDataReader;
while (dr.Read())
{
Console.WriteLine("{0}\t{1}\t{2}\t{3} \n", dr[0], dr[1], dr[2], dr[3]);
}
dr.Dispose();
// Get all Frequent Travellers
CUI.Headline("All Frequent Travellers:");
var ft = ctx.PassengerSet.Where(x => x.FrequentFlyer == "Yes").ToList();
foreach (var pas in ft)
{
Console.WriteLine(pas);
}
// Get all Frequent Travellers
CUI.Headline("All Frequent Travellers: -> StartsWith() does not work!");
var ft2 = ctx.PassengerSet.Where(x => x.FrequentFlyer.StartsWith("Y")).ToList();
if (ft2.Count == 0)
{
CUI.PrintError("No passengers :-(");
}
foreach (var pas in ft2)
{
Console.WriteLine(pas);
}
}
}
public static void ExpressionTreeTwoConditions()
{
CUI.MainHeadline(nameof(ExpressionTreeTwoConditions));
string destination = "Rome";
short? MindestzahlFreierPlaetze = 10;
using (WWWingsContext ctx = new WWWingsContext())
{
// Base query
IQueryable <BO.Flight> query = from flight in ctx.FlightSet where flight.FlightNo < 300 select flight;
// Optional conditions
if (!String.IsNullOrEmpty(destination) && MindestzahlFreierPlaetze > 0)
{
// Laufvariable definieren
ParameterExpression f = Expression.Parameter(typeof(BO.Flight), "f");
// Add first condition
Expression left = Expression.Property(f, "Destination");
Expression right = Expression.Constant(destination);
Expression condition1 = Expression.Equal(left, right);
// Add seconds condition
left = Expression.Property(f, "FreeSeats");
right = Expression.Constant((short?)MindestzahlFreierPlaetze, typeof(short?));
Expression condition2 = Expression.GreaterThan(left, right);
// Connect conditions mit AND operator
Expression predicateBody = Expression.And(condition1, condition2);
// Build expression tree
MethodCallExpression whereCallExpression = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { query.ElementType },
query.Expression,
Expression.Lambda <Func <BO.Flight, bool> >(predicateBody, new ParameterExpression[] { f }));
// Create query from expression tree
query = query.Provider.CreateQuery <BO.Flight>(whereCallExpression);
}
ctx.Log();
// Print the result set
Console.WriteLine("Flights found:");
foreach (BO.Flight f in query.ToList())
{
Console.WriteLine($"Flight Nr {f.FlightNo} from {f.Departure} to {f.Destination}: {f.FreeSeats} free seats! Pilot: {f.PilotId} ");
}
}
}
internal static void UsePrivateField()
{
Console.WriteLine(nameof(UsePrivateField));
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log();
var m = ctx.PilotSet.Where(x => x.PassportNumber == null).FirstOrDefault();
Console.WriteLine("Pilot: " + m.ToString());
m.SetPassportNumber("WW123");
var anz = ctx.SaveChanges();
Console.WriteLine("Saved changes: " + anz);
var m2 = ctx.PilotSet.Find(m.PersonID);
Console.WriteLine("PassportNumber: " + m2.PassportNumber);
}
}
public static void Demo_TVF()
{
CUI.MainHeadline(nameof(Demo_TVF));
using (var ctx = new WWWingsContext())
{
ctx.Log();
var flightSet = ctx.FlightSet.FromSql("Select * from GetFlightsFromTVF({0})", "Berlin").Where(x => x.FreeSeats > 10).ToList();
Console.WriteLine(flightSet.Count());
foreach (var flight in flightSet)
{
Console.WriteLine(flight);
}
Console.WriteLine(flightSet.Count());
}
}
public static void ChangePilotUsingFKShadowProperty()
{
CUI.Headline(nameof(ChangePilotUsingFKShadowProperty));
var flightNo = 102;
var neuerPilotNr = 123;
using (var ctx = new WWWingsContext())
{
ctx.Log();
Flight flight = ctx.FlightSet.Find(flightNo);
ctx.Entry(flight).Property("PilotId").CurrentValue = neuerPilotNr;
var count = ctx.SaveChanges();
Console.WriteLine("Number of saved changes: " + count);
}
}
public static void ChangePilotUsingFK()
{
CUI.MainHeadline(nameof(ChangePilotUsingFK));
var flightNo = 102;
var newPilotID = 55;
using (var ctx = new WWWingsContext())
{
ctx.Log();
Flight flight = ctx.FlightSet.Find(flightNo);
flight.PilotId = newPilotID;
var count = ctx.SaveChanges();
Console.WriteLine("Number of saved changes: " + count);
}
}
public static void Demo_Paging()
{
using (var ctx = new WWWingsContext())
{
ctx.Log();
CUI.MainHeadline("Query with Skip() without OrderBy / with OFFSET and FETCH");
var q5 = (from p5 in ctx.FlightSet
where p5.Departure.StartsWith("M")
select p5).Skip(5).Take(10);
var l5 = q5.ToList();
Console.WriteLine("Count: " + l5.Count());
}
}
/// <summary>
/// Caching with EFPlus FromCache() / 5 seconds
/// </summary>
/// <param name="departure"></param>
/// <returns></returns>
public static List <Flight> GetFlight4(string departure)
{
using (var ctx = new WWWingsContext())
{
ctx.Log();
var options = new MemoryCacheEntryOptions()
{
AbsoluteExpiration = DateTime.Now.AddSeconds(5)
};
// optional: QueryCacheManager.DefaultMemoryCacheEntryOptions = options;
Console.WriteLine("Lade Flights from " + departure + "...");
var flightSet = ctx.FlightSet.Where(x => x.Departure == departure).FromCache(options).ToList();
Console.WriteLine(flightSet.Count + " Flights im RAM!");
return(flightSet);
}
}
public static void GroupBy()
{
// Simple Group by (Number of flights per destination)
using (var ctx = new WWWingsContext())
{
Console.WriteLine(ctx.Database.GetType().FullName);
ctx.Log();
var groups = (from p in ctx.FlightSet
group p by p.Departure into g
select new { City = g.Key, Count = g.Count() }).Where(x => x.Count > 5).OrderBy(x => x.Count);
// First roundtrip to the database
Console.WriteLine("Number of groups: " + groups.Count());
// Second roundtrip to the database
foreach (var g in groups.ToList())
{
Console.WriteLine(g.City + ": " + g.Count);
}
}
return;
#region nested Groupy by
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("Number of groups: " + groupSet.Count());
foreach (var g in groupSet)
{
Console.WriteLine(g.Key + ": " + g.Count());
foreach (var f in g)
{
// do something...
// Console.WriteLine(" " + f.FlightNo);
}
}
}
#endregion
}
public static void LocalClear()
{
using (var ctx = new WWWingsContext())
{
ctx.Log();
CUI.Headline("Lade die ersten 5 Flights und Pilots...");
ctx.FlightSet.Take(5).ToList();
ctx.PilotSet.Take(5).ToList();
Console.WriteLine("Flights im Cache: " + ctx.FlightSet.Local.Count);
Console.WriteLine("Pilots im Cache: " + ctx.PilotSet.Local.Count);
CUI.Headline("Lade die ersten 10 Flights und Pilots...");
ctx.FlightSet.Take(10).ToList();
ctx.PilotSet.Take(10).ToList();
Console.WriteLine("Flights im Cache: " + ctx.FlightSet.Local.Count);
Console.WriteLine("Pilots im Cache: " + ctx.PilotSet.Local.Count);
PrintCache(ctx.FlightSet);
CUI.Headline("Löschen Flight-Cache...");
ctx.FlightSet.Local.Clear();
Console.WriteLine("Flights im Cache: " + ctx.FlightSet.Local.Count);
Console.WriteLine("Pilots im Cache: " + ctx.PilotSet.Local.Count);
foreach (EntityEntry entry in ctx.ChangeTracker.Entries())
{
Console.WriteLine(entry.Entity.ToString() + ": " + entry.State);
}
//ctx.SaveChanges();
CUI.Headline("Lade die ersten 15 Flights und Pilots...");
var flightSet = ctx.FlightSet.Take(15).ToList();
Console.WriteLine("Geladene Flights: " + ctx.FlightSet.Local.Count);
Console.WriteLine(flightSet[0]);
CUI.Headline("Zugriff auf einen Flight via Find()...");
ctx.FlightSet.Find(100);
Console.WriteLine("Flights im Cache: " + ctx.FlightSet.Local.Count);
Console.WriteLine(ctx.FlightSet.Local.ElementAt(0));
ctx.PilotSet.Take(15).ToList();
Console.WriteLine("Pilots im Cache: " + ctx.PilotSet.Local.Count);
PrintCache(ctx.FlightSet);
}
}
public static void Batching_Change10Flights()
{
CUI.MainHeadline(nameof(Batching_Change10Flights));
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log();
var list = ctx.FlightSet.Take(10).ToList();
foreach (var f in list)
{
Console.WriteLine("Before: " + f.ToString());
f.FreeSeats -= 1; //Änderung
Console.WriteLine("After: " + f.ToString());
}
var anz = ctx.SaveChanges();
CUI.MainHeadline("Number of saved changes: " + anz);
}
}
public static void Demo_SQLDirect4()
{
CUI.MainHeadline(nameof(Demo_SQLDirect4));
string departure = "Berlin";
using (var ctx = new WWWingsContext())
{
ctx.Log();
List <Flight> flightSet = ctx.FlightSet.FromSql($@"Select top 100 * from Flight where Departure={departure}").ToList();
Console.WriteLine(flightSet.Count());
foreach (var flight in flightSet)
{
Console.WriteLine(flight);
}
Console.WriteLine(flightSet.Count());
}
}
public static void Demo_SQLDirect2()
{
CUI.MainHeadline(nameof(Demo_SQLDirect2));
string departure = "Berlin";
using (var ctx = new WWWingsContext())
{
ctx.Log();
IQueryable <Flight> flightSet = ctx.FlightSet.FromSql("Select * from Flight where Departure={0}", departure);
Console.WriteLine(flightSet.Count());
foreach (var flight in flightSet)
{
Console.WriteLine(flight);
}
Console.WriteLine(flightSet.Count());
}
}
public static void Demo_SQLDirectAndLINQComposition()
{
CUI.MainHeadline(nameof(Demo_SQLDirectAndLINQComposition));
string departure = "Berlin";
using (var ctx = new WWWingsContext())
{
ctx.Log();
var flightSet = ctx.FlightSet.FromSql("Select top 100 * from Flight where Departure={0}", departure).Include(f => f.Pilot).Where(x => x.FreeSeats > 10).OrderBy(x => x.FreeSeats).ToList();
Console.WriteLine(flightSet.Count());
foreach (var flight in flightSet)
{
Console.WriteLine(flight);
}
Console.WriteLine(flightSet.Count());
}
}
public static void LINQComposition()
{
CUI.MainHeadline(nameof(LINQComposition));
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log();
string departure = "Paris";
string destination = "";
bool nurMitFreienPlaetzen = true;
bool sortieren = true;
// Base query
IQueryable <Flight> flightQuery = (from f in ctx.FlightSet select f);
// Adding optional condition
if (!String.IsNullOrEmpty(departure))
{
flightQuery = from f in flightQuery where f.Departure == departure select f;
}
if (!String.IsNullOrEmpty(destination))
{
flightQuery = from f in flightQuery where f.Destination == destination select f;
}
// Adding optional condition using a method
if (nurMitFreienPlaetzen)
{
flightQuery = FreeSeatsMustBeGreaterZero(flightQuery);
}
// Optional sorting
if (sortieren)
{
flightQuery = flightQuery.OrderBy(f => f.Date);
}
// Send to the database now!
List <Flight> flightSet = flightQuery.ToList();
// Print the result set
Console.WriteLine("Flights found:");
foreach (var f in flightSet)
{
Console.WriteLine($"Flight Nr {f.FlightNo} from {f.Departure} to {f.Destination}: {f.FreeSeats} free seats! Pilot: {f.PilotId} ");
}
}
}