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()); } } }