public static void TrackingMode_AsNoTracking()
{
CUI.MainHeadline(nameof(TrackingMode_AsNoTracking));
CUI.Headline("Tracking mode");
using (WWWingsContext ctx = new WWWingsContext())
{
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())
{
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 Demo_Projection_OneFlight()
{
using (var ctx = new WWWingsContext())
{
CUI.MainHeadline("Projection");
var q = from x in ctx.FlightSet
where x.FlightNo == 101
orderby x.FlightNo
select new Flight()
{
FlightNo = x.FlightNo,
Date = x.Date,
Departure = x.Departure,
Destination = x.Destination,
Seats = x.Seats,
FreeSeats = x.FreeSeats,
};
var f = q.FirstOrDefault();
Console.WriteLine(f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp));
ctx.Attach(f);
Console.WriteLine(f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp));
f.FreeSeats++;
Console.WriteLine(f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp));
var anz = ctx.SaveChanges();
Console.WriteLine("Number of saved changes: " + anz);
}
}
// 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 ChangeTracking_OneObject()
{
CUI.MainHeadline(nameof(ChangeTracking_OneObject));
Flight flight;
EntityEntry <BO.Flight> entryObj;
PropertyEntry propObj;
using (var ctx = new WWWingsContext())
{
CUI.Headline("Loading Object...");
flight = (from y in ctx.FlightSet select y).FirstOrDefault();
// Access Change Tracker
entryObj = ctx.Entry(flight);
propObj = entryObj.Property(f => f.FreeSeats);
Console.WriteLine(" Object state: " + entryObj.State);
Console.WriteLine(" Is FreeSeats modified?: " + propObj.IsModified);
CUI.Headline("Chanhing Object...");
flight.FreeSeats--;
// Access Change Tracker again
entryObj = ctx.Entry(flight);
propObj = entryObj.Property(f => f.FreeSeats);
Console.WriteLine(" Object state: " + entryObj.State);
Console.WriteLine(" Is FreeSeats modified?: " + propObj.IsModified);
// Print old and new values
if (entryObj.State == EntityState.Modified)
{
foreach (PropertyEntry p in entryObj.Properties)
{
if (p.IsModified)
{
Console.WriteLine(" " + p.Metadata.Name + ": " + p.OriginalValue + "->" + p.CurrentValue +
" / State in database: " + entryObj.GetDatabaseValues()[p.Metadata.Name]);
}
}
}
CUI.Headline("Save...");
int anz = ctx.SaveChanges();
Console.WriteLine(" Number of changes: " + anz);
// aktualisieren der Objecte des Change Trackers
entryObj = ctx.Entry(flight);
propObj = entryObj.Property(f => f.FreeSeats);
Console.WriteLine(" Object state: " + entryObj.State);
Console.WriteLine(" Is FreeSeats modified?: " + propObj.IsModified);
}
}
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 async Task <ActionResult <Customer> > CreateCustomerAndBook(Customer customer, int flightId, int purchasePrice)
{
_context.CustomerSet.Add(customer);
await _context.SaveChangesAsync();
Booking booking = new Booking();
booking.CustomerID = customer.CustomerID;
booking.FlightID = flightId;
booking.PurchasePrice = purchasePrice;
_context.BookingSet.Add(booking);
await _context.SaveChangesAsync();
Flight flight = await _context.FlightSet.FindAsync(flightId);
var count = flight.BookingSet.Count;
if (count >= booking.Flight.Seats)
{
flight.Full = true;
_context.Entry(flight).State = EntityState.Modified;
await _context.SaveChangesAsync();
}
return(CreatedAtAction("GetCustomer", new { id = customer.CustomerID }, customer));
}
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);
}
}
}
public static void Projection_Change()
{
using (var ctx = new WWWingsContext())
{
CUI.MainHeadline(nameof(Projection_Change));
var q = (from f in ctx.FlightSet
where f.FlightNo > 100
orderby f.FlightNo
select new Flight()
{
FlightNo = f.FlightNo,
Date = f.Date,
Departure = f.Departure,
Destination = f.Destination,
FreeSeats = f.FreeSeats,
Timestamp = f.Timestamp
}).Take(2);
var flightSet = q.ToList();
foreach (var f in flightSet)
{
Console.WriteLine($"Flight Nr {f.FlightNo} from {f.Departure} to {f.Destination} has {f.FreeSeats} free seats!");
}
Console.WriteLine("Number of flights: " + flightSet.Count);
foreach (var f in flightSet)
{
Console.WriteLine("Before attach: " + f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp));
ctx.Attach(f);
Console.WriteLine("After attach: " + f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp));
f.FreeSeats--;
Console.WriteLine("After Änderung: " + f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp));
var anz = ctx.SaveChanges();
Console.WriteLine("Number of saved changes: " + anz);
Console.WriteLine("After saving: " + f + " State: " + ctx.Entry(f).State + " Timestamp: " + ByteArrayToString(f.Timestamp));
}
}
}
/// <summary>
/// In dieser Überladung wird geprüft, ob es in der Liste Objekte gibt, die nicht dem Kontext angehören. Diese werden mit Add() ergänzt.
/// </summary>
public int Save(List <Flight> flightSet)
{
foreach (Flight f in flightSet)
{
if (ctx.Entry(f).State == EntityState.Detached)
{
ctx.FlightSet.Add(f);
}
}
return(Save()); // ruft ctx.SaveChanges();
}
/// <summary>
/// Variante 3 UNSINNIG !!!
/// Wenn Concurrency Check nicht auf Timestamp, sondern auf Wertespalten (außer Timestamp) aktiv, gehen Varianten 1 und 2 nicht, weil EF die neuen Werte auch als Originalwerte annehmen wird und die nicht in DB finden kann
/// Hier wird aber fehlerhafterweise der Inhalt der DB nochmal gelesen, weil den Concurreny Check absurd macht
/// </summary>
public static void Detached_Flight_ConcurrencyCheck_AusDB()
{
CUI.MainHeadline(nameof(Detached_Flight_ConcurrencyCheck_AusDB));
Flight flight;
CUI.Print("Lade Objekt in Kontextinstanz #1");
using (WWWingsContext ctx1 = new WWWingsContext())
{
ctx1.Log();
//ctx1.Configuration.LazyLoadingEnabled = false;
flight = ctx1.FlightSet.Find(110);
}
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();
// Wegen des Concurreny Checks brauchen wir die Originalwerte entweder aus dem Objekt selbst oder der DB
// Aus DB ist aber keine gute Lösung, denn damit ist der Concurreny Check auf den Spalten unsinnig
// hier aus DB
var flightOrg = ctx2.FlightSet.Find(110);
CUI.Print(ctx2.Entry(flightOrg).State.ToString());
// Nun Werte des alten Objekts auf das neue kopieren
ctx2.Entry(flightOrg).CurrentValues.SetValues(flight);
CUI.Print(ctx2.Entry(flightOrg).State.ToString());
var count = ctx2.SaveChanges();
Console.WriteLine("Saved changes: " + count.ToString());
if (count != 1)
{
Debugger.Break();
}
}
}
public static void TrackingMode_QueryTrackingBehavior()
{
CUI.MainHeadline("Default setting: TrackAll. Use AsNoTracking()");
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.TrackAll; // is default
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}"); // 1
}
CUI.MainHeadline("Default setting: NoTracking.");
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; // NoTracking
var flightSet = ctx.FlightSet.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}"); // 1
}
CUI.MainHeadline("Default setting: NoTracking. Use AsTracking()");
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; // NoTracking
var flightSet = ctx.FlightSet.AsTracking().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
}
}
/// <summary>
/// Nur geänderte Spalten zurückschreiben
/// Attach()/IsModified=true
/// achtung: Geht nicht wenn Concurrency Check auf FreeSeats, da da EF den neuen Wert auch als alten Wert annimmt!
/// </summary>
public static void Detached_Flight_EinzelneProperties()
{
CUI.MainHeadline(nameof(Detached_Flight_EinzelneProperties));
Flight flight;
CUI.Print("Lade Objekt in Kontextinstanz #1");
using (WWWingsContext ctx1 = new WWWingsContext())
{
ctx1.Log();
//ctx1.Configuration.LazyLoadingEnabled = false;
flight = ctx1.FlightSet.Find(110);
}
CUI.Print("Objekt ändern");
CUI.Print(flight.ToString());
flight.Memo = "last changed at " + DateTime.Now;
flight.FreeSeats--;
CUI.Print(flight.ToString());
CUI.Print("Objekt nun speichern mit Kontextinstanz #2");
using (WWWingsContext ctx2 = new WWWingsContext())
{
ctx2.Log();
ctx2.FlightSet.Attach(flight);
CUI.Print(ctx2.Entry(flight).State.ToString());
// Zustand einzelner Properties ändern
ctx2.Entry(flight).Property(x => x.Memo).IsModified = true;
ctx2.Entry(flight).Property(x => x.FreeSeats).IsModified = true;
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 Demo_SQLDirect_Projection()
{
CUI.MainHeadline(nameof(Demo_SQLDirect_Projection));
using (var ctx = new WWWingsContext())
{
var flightSet = ctx.FlightSet.FromSql("Select top 100 FlightNo, Departure, Destination, strikebound, FlightDate,CopilotId,pilotid, AircraftTypeID, AirlineCode, memo, Seats, Freeseats, Timestamp, LastChange, NonSmokingFlight, Price, Utilization from Flight where Departure={0}", "Berlin"); //memo fehlt --> The required column 'Memo' was not present in the results of a 'FromSql' operation.
Console.WriteLine(flightSet.Count());
foreach (var flight in flightSet)
{
Console.WriteLine(flight + ": " + ctx.Entry(flight).State);
}
Console.WriteLine(flightSet.Count());
}
}
/// <summary>
/// Attach()/EntityState.Modified --> All Spalten zurückschreiben
/// </summary>
public static void Detached_Flight()
{
CUI.MainHeadline(nameof(Detached_Flight));
Flight f;
CUI.Print("Lade Objekt in Kontextinstanz #1");
using (WWWingsContext ctx1 = new WWWingsContext())
{
ctx1.Log();
f = ctx1.FlightSet.Find(101);
}
// Flight ist nun "Detached"!
CUI.Print("Objekt ändern");
CUI.Print(f.ToString());
f.Memo = "last changed at " + DateTime.Now;
f.FreeSeats--;
CUI.Print(f.ToString());
CUI.Print("Objekt nun speichern mit Kontextinstanz #2");
using (WWWingsContext ctx2 = new WWWingsContext())
{
CUI.Print(ctx2.Entry(f).State.ToString());
ctx2.FlightSet.Attach(f);
CUI.Print(ctx2.Entry(f).State.ToString());
ctx2.Entry(f).State = EntityState.Modified;
CUI.Print(ctx2.Entry(f).State.ToString());
var count = ctx2.SaveChanges();
Console.WriteLine("Saved changes: " + count.ToString());
if (count != 1)
{
Debugger.Break();
}
}
}
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 ExplicitTransactionTwoSaveChanges()
{
CUI.MainHeadline(nameof(ExplicitTransactionTwoSaveChanges));
using (var ctx = new WWWingsContext())
{
// Start transaction. Default is System.Data.IsolationLevel.ReadCommitted
using (var t = ctx.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
// Print isolation level
RelationalTransaction rt = t as RelationalTransaction;
DbTransaction dbt = rt.GetDbTransaction();
Console.WriteLine("Transaction with Level: " + dbt.IsolationLevel);
// Read data
int flightNo = ctx.FlightSet.OrderBy(x => x.FlightNo).FirstOrDefault().FlightNo;
var f = ctx.FlightSet.Where(x => x.FlightNo == flightNo).SingleOrDefault();
Console.WriteLine("Before: " + f.ToString());
// Change data and save
f.FreeSeats--;
var count1 = ctx.SaveChanges();
Console.WriteLine("Number of saved changes: " + count1);
// Change data again and save
f.Memo = "last changed at " + DateTime.Now.ToString();
var count2 = ctx.SaveChanges();
Console.WriteLine("Number of saved changes: " + count2);
Console.WriteLine("Commit or Rollback? 1 = Commit, other = Rollback");
var eingabe = Console.ReadKey().Key;
if (eingabe == ConsoleKey.D1)
{
t.Commit(); Console.WriteLine("Commit done!");
}
else
{
t.Rollback(); Console.WriteLine("Rollback done!");
}
Console.WriteLine("After in RAM: " + f.ToString());
ctx.Entry(f).Reload();
Console.WriteLine("After in DB: " + f.ToString());
}
}
}
public static void ClearCache()
{
using (var ctx = new WWWingsContext())
{
ctx.Log();
CUI.Headline("Load 5 Flights and Pilots...");
ctx.FlightSet.Take(5).ToList();
ctx.PilotSet.Take(5).ToList();
Console.WriteLine("Flights in Cache: " + ctx.FlightSet.Local.Count);
Console.WriteLine("Pilots in Cache: " + ctx.PilotSet.Local.Count);
foreach (var f in ctx.FlightSet.Local.ToList())
{
ctx.Entry(f).State = EntityState.Detached;
}
Console.WriteLine("Flights in Cache: " + ctx.FlightSet.Local.Count);
Console.WriteLine("Pilots in Cache: " + ctx.PilotSet.Local.Count);
}
}
public static void ReadAndChangeShadowProperty()
{
int flightNo = 101;
CUI.MainHeadline(nameof(ReadAndChangeShadowProperty));
using (WWWingsContext ctx = new WWWingsContext())
{
var flight = ctx.FlightSet.SingleOrDefault(x => x.FlightNo == flightNo);
CUI.Headline("List of all shadow property of type Flight");
foreach (var p in ctx.Entry(flight).Properties)
{
Console.WriteLine(p.Metadata.Name + ": " + p.Metadata.IsShadowProperty);
}
CUI.Print("Before: " + flight.ToString() + " / " + ctx.Entry(flight).State, ConsoleColor.Cyan);
Console.WriteLine("Free seats: " + ctx.Entry(flight).Property("FreeSeats").CurrentValue);
Console.WriteLine("Last change: " + ctx.Entry(flight).Property("LastChange").CurrentValue);
CUI.PrintWarning("Changing object...");
flight.FreeSeats += 1;
ctx.Entry(flight).Property("LastChange").CurrentValue = DateTime.Now;
CUI.Print("After: " + flight.ToString() + " / " + ctx.Entry(flight).State, ConsoleColor.Cyan);
Console.WriteLine("Free seats: " + ctx.Entry(flight).Property("FreeSeats").CurrentValue);
Console.WriteLine("Last change: " + ctx.Entry(flight).Property("LastChange").CurrentValue);
var count = ctx.SaveChanges();
Console.WriteLine("Number of saved changes: " + count);
}
CUI.Headline("LINQ query using a Shadow Property");
using (WWWingsContext ctx = new WWWingsContext())
{
var date = ctx.FlightSet
.Where(c => EF.Property <DateTime>(c, WWWingsContext.ShadowPropertyName) > DateTime.Now.AddDays(-2))
.OrderByDescending(c => EF.Property <DateTime>(c, WWWingsContext.ShadowPropertyName))
.Select(x => EF.Property <DateTime>(x, WWWingsContext.ShadowPropertyName))
.FirstOrDefault();
Console.WriteLine("Last change: " + date);
}
CUI.Headline("Retest: Access shadow property column using SQL");
using (WWWingsContext ctx = new WWWingsContext())
{
string CONNSTRING = ctx.Database.GetDbConnection().ConnectionString;
SqlConnection conn = new SqlConnection(CONNSTRING);
conn.Open();
string SQL = "Select LastChange from Flight where FlightNo = " + flightNo;
SqlDataReader dr = new SqlCommand(SQL, conn).ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr["LastChange"]);
}
dr.Close();
conn.Close();
}
}
public static void GlobalFilter()
{
CUI.MainHeadline(nameof(GlobalFilter));
using (var ctx = new WWWingsContext(GlobalQueryFilter: true))
{
ctx.Log();
//-------------------------------------------------------
CUI.Headline("LINQ query");
List <Flight> flightSet = (from f in ctx.FlightSet
where f.Departure == "Berlin"
select f).ToList();
CUI.Print(flightSet.Count + " Results", ConsoleColor.Yellow);
foreach (var f in flightSet)
{
Console.WriteLine(f);
}
CUI.Headline("SQL query");
List <Flight> flightSet2 = ctx.FlightSet.FromSql("select top 100 * from Flight where Departure = 'Berlin'").ToList();
CUI.Print(flightSet2.Count + " results", ConsoleColor.Yellow);
foreach (var f in flightSet2)
{
Console.WriteLine(f);
}
CUI.Headline("TVF");
List <Flight> flightSet3 = ctx.FlightSet.FromSql("Select top 100 * from GetFlightsFromTVF({0})", "Berlin").Where(f => f.NonSmokingFlight == true).ToList();
CUI.Print(flightSet3.Count + " results", ConsoleColor.Yellow);
foreach (var flight in flightSet3)
{
Console.WriteLine(flight);
}
CUI.Headline("SP");
List <Flight> flightSet4 = ctx.FlightSet.FromSql("EXEC GetFlightsFromSP {0}", "Berlin").ToList();
CUI.Print(flightSet4.Count + " results", ConsoleColor.Yellow);
foreach (var flight in flightSet4)
{
Console.WriteLine(flight);
}
//-------------------------------------------------------
List <Flight> flightAlleSet = (from f in ctx.FlightSet.IgnoreQueryFilters()
where f.Departure == "Berlin"
select f).ToList();
CUI.Print(flightAlleSet.Count + " results", ConsoleColor.Yellow);
foreach (var f in flightAlleSet)
{
Console.WriteLine(f);
}
//-------------------------------------------------------
Console.WriteLine("Filtered: " + ctx.FlightSet.Count());
Console.WriteLine("All: " + ctx.FlightSet.IgnoreQueryFilters().Count());
//-------------------------------------------------------
CUI.Headline("Pilots (Eager Loading)");
var pilotWithFlights = ctx.PilotSet.Include(x => x.FlightAsPilotSet).Take(10).ToList();
foreach (var p in pilotWithFlights)
{
Console.WriteLine(p);
foreach (var f in p.FlightAsPilotSet.ToList())
{
Console.WriteLine(" - " + f.ToString());
}
}
//-------------------------------------------------------
CUI.Headline("Pilots (Explicit Loading)");
var pilotenSet = ctx.PilotSet.Take(10).ToList();
foreach (var p in pilotenSet)
{
Console.WriteLine(p);
ctx.Entry(p).Collection(x => x.FlightAsPilotSet).Load();
foreach (var f in p.FlightAsPilotSet.ToList())
{
Console.WriteLine(" - " + f.ToString());
}
}
}
}
public static void TrackingMode_NoTracking_Attach()
{
CUI.MainHeadline(nameof(TrackingMode_NoTracking_Attach));
CUI.Headline("Attach() before change");
using (WWWingsContext ctx = new WWWingsContext())
{
var flightSet = ctx.FlightSet.AsNoTracking().ToList();
var flight = flightSet[0];
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Detached
ctx.Attach(flight);
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}"); // 0
}
CUI.Headline("Attach() after change (change state per property)");
using (WWWingsContext ctx = new WWWingsContext())
{
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
ctx.Attach(flight);
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Unchanged
// geändertes Attribut bei EFC melden
ctx.Entry(flight).Property(f => f.FreeSeats).IsModified = true;
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Modified
int count = ctx.SaveChanges();
Console.WriteLine($"Saved changes: {count}"); // 1
}
CUI.Headline("Attach() after change (change state per object)");
using (WWWingsContext ctx = new WWWingsContext())
{
ctx.Log();
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
ctx.Attach(flight);
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Unchanged
ctx.Entry(flight).State = EntityState.Modified;
Console.WriteLine(flight + " object state: " + ctx.Entry(flight).State); // Modified
int count = ctx.SaveChanges();
Console.WriteLine($"Saved changes: {count}"); // 1
}
CUI.Headline("Attach() with non entity class -- not allowed!!!");
using (WWWingsContext ctx = new WWWingsContext())
{
var obj = new FileInfo(@"c:\temp\daten.txt");
try
{
ctx.Attach(obj); // Error: The entity type 'FileInfo' was not found. Ensure that the entity type has been added to the model.
}
catch (Exception e)
{
CUI.PrintError(e);
}
}
}
public static void ConflictWhileChangingFlight()
{
CUI.MainHeadline(nameof(ConflictWhileChangingFlight));
Console.WriteLine("Process.PersonID=" + Process.GetCurrentProcess().Id);
Console.Title = nameof(ConflictWhileChangingFlight) + ": Process-PersonID=" + Process.GetCurrentProcess().Id;
// Flight, where the conflict should arise
int flightNo = 151;
using (WWWingsContext ctx = new WWWingsContext())
{
// that is not possible: Read-only!
//ctx.Model.FindEntityType(typeof(Flight)).FindProperty("FreeSeats").IsConcurrencyToken = true;
// --- Load flight
Flight flight = ctx.FlightSet.Find(flightNo);
Console.WriteLine(DateTime.Now.ToLongTimeString() + ": free seats Before: " + flight.FreeSeats);
short seats = 0;
string input = "";
do
{
Console.WriteLine("How many seats do you need at this flight?");
input = Console.ReadLine(); // wait (time to start another process)
} while (!Int16.TryParse(input, out seats));
// --- change the free seats
flight.FreeSeats -= seats;
Console.WriteLine(DateTime.Now.ToLongTimeString() + ": free seats NEW: " + flight.FreeSeats);
try
{
// --- try to save
EFC_Util.PrintChangedProperties(ctx.Entry(flight));
var anz = ctx.SaveChanges();
Console.WriteLine("SaveChanges: Number of saved changes: " + anz);
}
catch (DbUpdateConcurrencyException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
CUI.PrintError(DateTime.Now.ToLongTimeString() + ": Error: another user has already changed the flight!");
CUI.Print("Conflicts with the following properties:");
EFC_Util.PrintChangedProperties(ex.Entries.Single());
// --- Ask the user
Console.WriteLine("What do you want to do?");
Console.WriteLine("Key 1: Accept the values of the other user");
Console.WriteLine("Key 2: Override the values of the other user");
Console.WriteLine("Key 3: Calculate new value from both records");
ConsoleKeyInfo key = Console.ReadKey();
switch (key.Key)
{
case ConsoleKey.D1: // Accept the values of the other user
{
Console.WriteLine("You have chosen: Option 1: Accept");
ctx.Entry(flight).Reload();
break;
}
case ConsoleKey.D2: // Override the values of the other user
{
Console.WriteLine("You have chosen: Option 2: Override");
ctx.Entry(flight).OriginalValues.SetValues(ctx.Entry(flight).GetDatabaseValues());
// wie RefreshMode.ClientWins bei ObjectContext
EFC_Util.PrintChangeInfo(ctx);
int anz = ctx.SaveChanges();
Console.WriteLine("SaveChanges: Saved changes: " + anz);
break;
}
case ConsoleKey.D3: // Calculate new value from both records
{
Console.WriteLine("You have chosen: Option 3: Calculate");
var FreeSeatsOrginal = ctx.Entry(flight).OriginalValues.GetValue <short?>("FreeSeats");
var FreeSeatsNun = flight.FreeSeats.Value;
var FreeSeatsInDB = ctx.Entry(flight).GetDatabaseValues().GetValue <short?>("FreeSeats");
flight.FreeSeats = (short)(FreeSeatsOrginal -
(FreeSeatsOrginal - FreeSeatsNun) -
(FreeSeatsOrginal - FreeSeatsInDB));
EFC_Util.PrintChangeInfo(ctx);
ctx.Entry(flight).OriginalValues.SetValues(ctx.Entry(flight).GetDatabaseValues());
int anz = ctx.SaveChanges();
Console.WriteLine("SaveChanges: Saved changes: " + anz);
break;
}
}
}
Console.WriteLine(DateTime.Now.ToLongTimeString() + ": free seats after: " + flight.FreeSeats);
// --- Cross check the final state in the database
using (WWWingsContext ctx2 = new WWWingsContext())
{
var f = ctx.FlightSet.Where(x => x.FlightNo == flightNo).SingleOrDefault();
Console.WriteLine(DateTime.Now.ToLongTimeString() + ": free seats cross check: " + f.FreeSeats);
} // End using-Block -> Dispose()
}
}
public static void AddFlight()
{
CUI.MainHeadline(nameof(AddFlight));
using (WWWingsContext ctx = new WWWingsContext())
{
// Create flight in RAM
var f = new Flight();
f.FlightNo = 123456;
f.Departure = "Essen";
f.Destination = "Sydney";
f.AirlineCode = "WWW";
f.PilotId = ctx.PilotSet.FirstOrDefault().PersonID;
f.Seats = 100;
f.FreeSeats = 100;
Console.WriteLine($"Before adding: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the Flight object: " + ctx.Entry(f).State);
// Add flight to Kontext
ctx.FlightSet.Add(f);
// or: ctx.Add(f);
Console.WriteLine($"After adding: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the Flight object: " + ctx.Entry(f).State);
try
{
var anz = ctx.SaveChanges();
if (anz == 0)
{
Console.WriteLine("Problem: No changes saved!");
}
else
{
Console.WriteLine("Number of saved changes: " + anz);
}
Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the Flight object: " + ctx.Entry(f).State);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.ToString());
}
}
}
public static void AddFlightWithDetails()
{
Console.WriteLine(nameof(AddFlightWithDetails));
using (WWWingsContext ctx = new WWWingsContext())
{
//var p = ctx.PilotSet.SingleOrDefault(x => x.PersonID == 234);
// Flight im RAM anlegen
var f = new Flight();
f.FlightNo = 123456;
//f.Pilot = p;
f.Departure = "Essen";
f.Destination = "Sydney";
f.AirlineCode = "WWW";
f.PilotId = 23;
f.Copilot = null; // kein Copilot :-(
f.Seats = 100;
f.FreeSeats = 100;
Console.WriteLine($"Vor dem Anfügen: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
// Flight dem Kontext "hinzufügen"
ctx.FlightSet.Add(f);
ctx.FlightSet.Add(f);
ctx.Add(f);
Console.WriteLine($"Nach dem Anfügen: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
try
{
var anz = ctx.SaveChanges();
if (anz == 0)
{
Console.WriteLine("Problem: No changes saved!");
}
else
{
Console.WriteLine("Number of saved changes: " + anz);
}
Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.ToString());
}
}
}
public static void ChangeFlightManyProperties()
{
CUI.MainHeadline(nameof(ChangeFlightManyProperties));
int flightNo = 101;
using (WWWingsContext ctx = new WWWingsContext())
{
var f = ctx.FlightSet.SingleOrDefault(x => x.FlightNo == flightNo);
Console.WriteLine($"After loading: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the Flight object: " + ctx.Entry(f).State);
f.FreeSeats -= 2;
//f.Departure = "sdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhsdhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh";
f.Memo = $"Last change durch Benutzer {System.Environment.UserName} on {DateTime.Now}.";; // Änderung 2
Console.WriteLine($"After changes: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
try
{
var anz = ctx.SaveChanges();
if (anz == 0)
{
Console.WriteLine("Problem: No changes saved!");
}
else
{
Console.WriteLine("Number of saved changes: " + anz);
}
Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the Flight object: " + ctx.Entry(f).State);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.ToString());
}
}
}
public static void Demo_ExplizitLoading()
{
CUI.MainHeadline(nameof(Demo_ExplizitLoading));
using (var ctx = new WWWingsContext())
{
// Load only the flight
var f = ctx.FlightSet
.SingleOrDefault(x => x.FlightNo == 101);
Console.WriteLine($"Flight Nr {f.FlightNo} from {f.Departure} to {f.Destination} has {f.FreeSeats} free seats!");
// Now load the pilot and copilot
if (!ctx.Entry(f).Reference(x => x.Pilot).IsLoaded)
{
ctx.Entry(f).Reference(x => x.Pilot).Load();
}
if (!ctx.Entry(f).Reference(x => x.Copilot).IsLoaded)
{
ctx.Entry(f).Reference(x => x.Copilot).Load();
}
// Check if loaded
if (ctx.Entry(f).Reference(x => x.Pilot).IsLoaded)
{
Console.WriteLine("Pilot is loaded!");
}
if (ctx.Entry(f).Reference(x => x.Copilot).IsLoaded)
{
Console.WriteLine("Copilot is loaded!");
}
if (f.Pilot != null)
{
Console.WriteLine($"Pilot: {f.Pilot.Surname} has {f.Pilot.FlightAsPilotSet.Count} flights as pilot!");
}
else
{
Console.WriteLine("No pilot assigned!");
}
if (f.Copilot != null)
{
Console.WriteLine($"Copilot: {f.Copilot.Surname} has {f.Copilot.FlightAsCopilotSet.Count} flights as copilot!");
}
else
{
Console.WriteLine("No copilot assigned!");
}
// No download the booking list
if (!ctx.Entry(f).Collection(x => x.BookingSet).IsLoaded)
{
ctx.Entry(f).Collection(x => x.BookingSet).Load();
}
Console.WriteLine("Number of passengers on this flight: " + f.BookingSet.Count);
Console.WriteLine("Passengers on this flight:");
foreach (var b in f.BookingSet)
{
// Now load the passenger object for this booking
if (!ctx.Entry(b).Reference(x => x.Passenger).IsLoaded)
{
ctx.Entry(b).Reference(x => x.Passenger).Load();
}
Console.WriteLine("- Passenger #{0}: {1} {2}", b.Passenger.PersonID, b.Passenger.GivenName, b.Passenger.Surname);
}
}
}
public static void RemoveFlight()
{
CUI.MainHeadline(nameof(RemoveFlight));
using (WWWingsContext ctx = new WWWingsContext())
{
var f = ctx.FlightSet.SingleOrDefault(x => x.FlightNo == 123456);
if (f == null)
{
return;
}
Console.WriteLine($"After loading: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
// Remove flight
ctx.FlightSet.Remove(f);
// or: ctx.Remove(f);
Console.WriteLine($"After deleting: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
try
{
var anz = ctx.SaveChanges();
if (anz == 0)
{
Console.WriteLine("Problem: No changes saved!");
}
else
{
Console.WriteLine("Number of saved changes: " + anz);
}
Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.ToString());
}
}
}
public static void ExplicitTransactionTwoContextInstances()
{
CUI.MainHeadline(nameof(ExplicitTransactionTwoContextInstances));
// Open shared connection
using (var connection = new SqlConnection(Program.CONNSTRING))
{
connection.Open();
// Start transaction. Default is System.Data.IsolationLevel.ReadCommitted
using (var t = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
// Print isolation level
Console.WriteLine("Transaction with Level: " + t.IsolationLevel);
int flightNo;
using (var ctx = new WWWingsContext(connection))
{
ctx.Database.UseTransaction(t);
var all = ctx.FlightSet.ToList();
var flight = ctx.FlightSet.Find(111);
flightNo = flight.FlightNo;
ctx.Database.ExecuteSqlCommand("Delete from booking where flightno= " + flightNo);
var pasID = ctx.PassengerSet.FirstOrDefault().PersonID;
// Create and persist booking
var b = new BO.Booking();
b.FlightNo = flightNo;
b.PassengerID = pasID;
ctx.BookingSet.Add(b);
var count1 = ctx.SaveChanges();
Console.WriteLine("Numer of bookings saved: " + count1);
}
using (var ctx = new WWWingsContext(connection))
{
ctx.Database.UseTransaction(t);
// Change free seats and save
var f = ctx.FlightSet.Find(flightNo);
Console.WriteLine("BEFORE: " + f.ToString());
f.FreeSeats--;
f.Memo = "last changed at " + DateTime.Now.ToString();
Console.WriteLine("AFTER: " + f.ToString());
var count2 = ctx.SaveChanges();
Console.WriteLine("Number of saved changes: " + count2);
Console.WriteLine("Commit or Rollback? 1 = Commit, other = Rollback");
var eingabe = Console.ReadKey().Key;
Console.WriteLine();
if (eingabe == ConsoleKey.D1)
{
t.Commit(); Console.WriteLine("Commit done!");
}
else
{
t.Rollback(); Console.WriteLine("Rollback done!");
}
Console.WriteLine("After in RAM: " + f.ToString());
ctx.Entry(f).Reload();
Console.WriteLine("After in DB: " + f.ToString());
}
}
}
}
public static void RemoveFlightWithKey()
{
Console.WriteLine(nameof(RemoveFlightWithKey));
using (WWWingsContext ctx = new WWWingsContext())
{
// Create a dummy object
var f = new Flight();
f.FlightNo = 123456;
Console.WriteLine($"After creation: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
// Append dummy object to context
ctx.Attach(f);
Console.WriteLine($"After attach: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
// Delete flight
ctx.FlightSet.Remove(f);
// or: ctx.Remove(f);
Console.WriteLine($"After remove: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
try
{
var anz = ctx.SaveChanges();
if (anz == 0)
{
Console.WriteLine("Problem: No changes saved!");
}
else
{
Console.WriteLine("Number of saved changes: " + anz);
}
Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.ToString());
}
}
}
public static void ChangeFlightOneProperty()
{
CUI.MainHeadline(nameof(ChangeFlightOneProperty));
int flightNo = 101;
using (WWWingsContext ctx = new WWWingsContext())
{
// Load flight
var f = ctx.FlightSet.Find(flightNo);
Console.WriteLine($"Before changes: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
// Change object in RAM
f.FreeSeats -= 2;
Console.WriteLine($"After changes: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! State of the flight object: " + ctx.Entry(f).State);
// Persist changes
try
{
var anz = ctx.SaveChanges();
if (anz == 0)
{
Console.WriteLine("Problem: No changes saved!");
}
else
{
Console.WriteLine("Number of saved changes: " + anz);
Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats! Zustand des Flight-Objekts: " + ctx.Entry(f).State);
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.ToString());
}
}
}
public static void ChangeFlightAndPilot()
{
CUI.MainHeadline(nameof(ChangeFlightAndPilot));
int flightNo = 101;
using (WWWingsContext ctx = new WWWingsContext())
{
var f = ctx.FlightSet.Include(x => x.Pilot).SingleOrDefault(x => x.FlightNo == flightNo);
Console.WriteLine($"After loading: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats!\nState of the flight object: " + ctx.Entry(f).State + " / State of the Pilot object: " + ctx.Entry(f.Pilot).State);
f.FreeSeats -= 2;
f.Pilot.FlightHours = (f.Pilot.FlightHours ?? 0) + 10;
f.Memo = $"Changed by User {System.Environment.UserName} on {DateTime.Now}.";
Console.WriteLine($"After changes: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats!\nState of the flight object: " + ctx.Entry(f).State + " / State of the Pilot object: " + ctx.Entry(f.Pilot).State);
try
{
var anz = ctx.SaveChanges();
if (anz == 0)
{
Console.WriteLine("Problem: No changes saved!");
}
else
{
Console.WriteLine("Number of saved changes: " + anz);
}
Console.WriteLine($"After saving: Flight #{f.FlightNo}: {f.Departure}->{f.Destination} has {f.FreeSeats} free seats!\nState of the flight object: " + ctx.Entry(f).State + " / State of the Pilot object: " + ctx.Entry(f.Pilot).State);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.ToString());
}
}
}