public static void Benchmark1(int total_parents, List <long> testTimes, List <long> testTimes2, ref int rowcount, ref int rowcount2)
{
var watch = new System.Diagnostics.Stopwatch();
watch.Start();
long cnt1 = 0;
string connstring = @"Data Source=(local)\sql2016;Database=CodexMicroORMTest;Integrated Security=SSPI;MultipleActiveResultSets=true";
ConcurrentBag <PersonWrapped> people = new ConcurrentBag <PersonWrapped>();
Parallel.For(1, total_parents + 1, (parentcnt) =>
{
using (IDbConnection db = new SqlConnection(connstring))
{
var parent = new PersonWrapped()
{
Name = $"NP{parentcnt}", Age = (parentcnt % 60) + 20, Gender = (parentcnt % 2) == 0 ? "F" : "M", LastUpdatedBy = Environment.UserName, LastUpdatedDate = DateTime.UtcNow
};
parent.PersonID = db.ExecuteScalar <int>("INSERT CEFTest.Person ([Name],Age,Gender,LastUpdatedBy,LastUpdatedDate) VALUES (@Name,@Age,@Gender,@LastUpdatedBy,@LastUpdatedDate); SELECT SCOPE_IDENTITY();", parent);
Interlocked.Add(ref cnt1, 4);
var ph1 = new Phone()
{
Number = "888-7777", PhoneTypeID = PhoneType.Mobile
};
parent.Phones.Add(ph1);
db.Execute("INSERT CEFTest.Phone (Number,PhoneTypeID,PersonID,LastUpdatedBy,LastUpdatedDate) VALUES (@Number,@PhoneTypeID,@PersonID,@LastUpdatedBy,@LastUpdatedDate)", new { ph1.Number, PhoneTypeID = (int)ph1.PhoneTypeID, parent.PersonID, LastUpdatedBy = Environment.UserName, LastUpdatedDate = DateTime.UtcNow });
var ph2 = new Phone()
{
Number = "777-6666", PhoneTypeID = PhoneType.Work
};
parent.Phones.Add(ph2);
db.Execute("INSERT CEFTest.Phone (Number,PhoneTypeID,PersonID,LastUpdatedBy,LastUpdatedDate) VALUES (@Number,@PhoneTypeID,@PersonID,@LastUpdatedBy,@LastUpdatedDate)", new { ph2.Number, PhoneTypeID = (int)ph2.PhoneTypeID, parent.PersonID, LastUpdatedBy = Environment.UserName, LastUpdatedDate = DateTime.UtcNow });
if ((parentcnt % 12) == 0)
{
db.Execute($"INSERT CEFTest.Phone (Number,PhoneTypeID,LastUpdatedBy,LastUpdatedDate) VALUES (@Number,@PhoneTypeID,@LastUpdatedBy,@LastUpdatedDate)", new { Number = "666-5555", PhoneTypeID = PhoneType.Home, LastUpdatedBy = Environment.UserName, LastUpdatedDate = DateTime.UtcNow });
}
else
{
var ph3 = new Phone()
{
Number = "777-6666", PhoneTypeID = PhoneType.Home
};
parent.Phones.Add(ph3);
db.Execute("INSERT CEFTest.Phone (Number,PhoneTypeID,PersonID,LastUpdatedBy,LastUpdatedDate) VALUES (@Number,@PhoneTypeID,@PersonID,@LastUpdatedBy,@LastUpdatedDate)", new { ph3.Number, PhoneTypeID = (int)ph3.PhoneTypeID, parent.PersonID, LastUpdatedBy = Environment.UserName, LastUpdatedDate = DateTime.UtcNow });
}
for (int childcnt = 1; childcnt <= (parentcnt % 4); ++childcnt)
{
var child = new PersonWrapped()
{
Name = $"NC{parentcnt}{childcnt}", Age = parent.Age - 20, Gender = (parentcnt % 2) == 0 ? "F" : "M", LastUpdatedBy = Environment.UserName, LastUpdatedDate = DateTime.UtcNow, ParentPersonID = parent.PersonID
};
child.PersonID = db.ExecuteScalar <int>("INSERT CEFTest.Person ([Name],Age,Gender,ParentPersonID,LastUpdatedBy,LastUpdatedDate) VALUES (@Name,@Age,@Gender,@ParentPersonID,@LastUpdatedBy,@LastUpdatedDate); SELECT SCOPE_IDENTITY();", child);
parent.Kids.Add(child);
var ph4 = new Phone()
{
Number = "999-8888", PhoneTypeID = PhoneType.Mobile
};
child.Phones = new Phone[] { ph4 };
db.Execute("INSERT CEFTest.Phone (Number,PhoneTypeID,LastUpdatedBy,LastUpdatedDate) VALUES (@Number,@PhoneTypeID,@LastUpdatedBy,@LastUpdatedDate)", new { ph4.Number, PhoneTypeID = (int)ph4.PhoneTypeID, LastUpdatedBy = Environment.UserName, LastUpdatedDate = DateTime.UtcNow });
Interlocked.Add(ref cnt1, 2);
}
people.Add(parent);
}
});
rowcount += (int)cnt1;
testTimes.Add(watch.ElapsedMilliseconds);
watch.Restart();
long cnt2 = 0;
// For everyone who's a parent of at least 30 yo, if at least 2 children of same sex, remove work phone, increment age
using (IDbConnection db = new SqlConnection(connstring))
{
var people2 = db.Query("CEFTest.up_Person_SummaryForParents", new { RetVal = 1, Msg = "", MinimumAge = 30 }, commandType: CommandType.StoredProcedure);
Parallel.ForEach((from d in people2 where d.MaleChildren > 1 || d.FemaleChildren > 1 select d).ToList(), (p) =>
{
using (IDbConnection db2 = new SqlConnection(connstring))
{
p.Age += 1;
db2.Execute("UPDATE CEFTest.Person SET Age = @Age, LastUpdatedBy = @LastUpdatedBy, LastUpdatedDate = @LastUpdatedDate WHERE PersonID = @PersonID", new { p.PersonID, p.Age, LastUpdatedBy = Environment.UserName, LastUpdatedDate = DateTime.UtcNow });
Interlocked.Add(ref cnt2, 1);
var ph2 = db2.Query("CEFTest.up_Phone_ByPersonID", new { RetVal = 1, Msg = "", p.PersonID, PhoneTypeID = (int)PhoneType.Work }, commandType: CommandType.StoredProcedure).FirstOrDefault();
if (ph2 != null)
{
db2.Execute("DELETE CEFTest.Phone WHERE PhoneID=@PhoneID", new { ph2.PhoneID });
Interlocked.Add(ref cnt2, 1);
}
}
});
}
rowcount2 += (int)cnt2;
watch.Stop();
testTimes2.Add(watch.ElapsedMilliseconds);
}
public static void Benchmark1(int total_parents, List <long> testTimes, List <long> testTimes2, ref int rowcount, ref int rowcount2)
{
var watch = new System.Diagnostics.Stopwatch();
watch.Start();
using (CEF.NewServiceScope())
{
var people = new EntitySet <PersonWrapped>();
for (int parentcnt = 1; parentcnt <= total_parents; ++parentcnt)
{
var parent = new PersonWrapped()
{
Name = $"NP{parentcnt}", Age = (parentcnt % 60) + 20, Gender = (parentcnt % 2) == 0 ? "F" : "M"
};
parent.Phones.Add(new Phone()
{
Number = "888-7777", PhoneTypeID = PhoneType.Mobile
});
parent.Phones.Add(new Phone()
{
Number = "777-6666", PhoneTypeID = PhoneType.Work
});
if ((parentcnt % 12) == 0)
{
CEF.NewObject(new Phone()
{
Number = "666-5555", PhoneTypeID = PhoneType.Home
});
}
else
{
parent.Phones.Add(new Phone()
{
Number = "777-6666", PhoneTypeID = PhoneType.Home
});
}
rowcount += 4;
for (int childcnt = 1; childcnt <= (parentcnt % 4); ++childcnt)
{
var child = CEF.NewObject(new PersonWrapped()
{
Name = $"NC{parentcnt}{childcnt}",
Age = parent.Age - 20,
Gender = (parentcnt % 2) == 0 ? "F" : "M"
,
Phones = new Phone[] { new Phone()
{
Number = "999-8888", PhoneTypeID = PhoneType.Mobile
} }
});
parent.Kids.Add(child);
rowcount += 2;
}
people.Add(parent);
}
CEF.DBSave();
}
testTimes.Add(watch.ElapsedMilliseconds);
watch.Restart();
// For purposes of benchmarking, treat this as a completely separate operation
using (var ss = CEF.NewServiceScope())
{
// For everyone who's a parent of at least 30 yo, if at least 2 children of same sex, remove work phone, increment age
var people = new EntitySet <Person>().DBRetrieveSummaryForParents(30);
Parallel.ForEach((from a in people let d = a.AsDynamic() where d.MaleChildren > 1 || d.FemaleChildren > 1 select a).ToList(), (p) =>
{
using (CEF.UseServiceScope(ss))
{
p.Age += 1;
var phones = new EntitySet <Phone>().DBRetrieveByOwner(p.PersonID, PhoneType.Work);
if (phones.Any())
{
CEF.DeleteObject(phones.First());
}
}
});
rowcount2 += CEF.DBSave().Count();
}
watch.Stop();
testTimes2.Add(watch.ElapsedMilliseconds);
}
private void StartTests_Click(object sender, RoutedEventArgs e)
{
try
{
ConsoleList.Items.Clear();
StartTests.IsEnabled = false;
RunBenchmark.IsEnabled = false;
// If did a prior run, clear out ambient scope which is bound to the UI thread
CEF.CurrentServiceScope.Dispose();
// Execute tear-down/set-up of test SQL objects from script
CEF.CurrentDBService().ExecuteRaw("DELETE CEFTest.Phone; UPDATE CEFTest.Person SET IsDeleted=1, LastUpdatedDate=GETUTCDATE(), LastUpdatedBy='Test';");
var random = new Random();
var watch = new System.Diagnostics.Stopwatch();
watch.Start();
// Creates and saves a person in 2 lines of code!
// Of note: no need for a context, we're using the implicit one created in TLS (great for a simple console app, recommended is to use explicit scopes)
var tristan = CEF.NewObject(new Person()
{
Name = "Tristan", Age = 4, Gender = "M"
});
ConsoleWriteLine($"Rows saved: {CEF.DBSave().Count()}");
ConsoleWriteLine($"A PersonID key as assigned by the database has been round-tripped back to us: {tristan.PersonID}");
ConsoleWriteLine($"And LastUpdatedDate as assigned by the database too, despite not being in our base POCO: {((PersonWrapped)tristan).LastUpdatedDate}");
// Creates and saves a person similar to above, but using wrapper object directly is fine too - and we've got an extension method that lets us save in 1 line of code!
var zella = CEF.NewObject(new PersonWrapped()
{
Name = "Zella", Age = 7, Gender = "F"
}).DBSave();
ConsoleWriteLine($"Similar to above, but already working with genned wrapper so no need to cast it: {zella.LastUpdatedDate}");
// We have the option to indicate whether the object should be considered new or not with respect to db, when adding to scope (CreateObject on the other hand is always "new") - in reality we could have used NewObject here too
var sally = new Person()
{
Name = "Sally", Age = 34, Gender = "F"
};
sally = CEF.IncludeObject(sally, ObjectState.Added);
ConsoleWriteLine($"Should be 1: {CEF.DBSave().Count()}");
// Now make a change: we're changing the source model in a simple way - associating already saved kids to a person - should turn into 3 updates and 2 inserts (we can't stop people from adding non-wrapped items, so we watch for this and Billy gets replaced by a PersonWrapped on addition here, plus his phone gets accounted for as well)
// Also of note, sally.Kids was previously null, and still is, so we initialize it with a trackable list (EntitySet is prefect) - Global.ReplaceNullCollections could have been set to true to do this automatically for us at the expense of performance
sally.Kids = CEF.CreateList <Person>(sally, nameof(Person.Kids));
sally.Kids.Add(tristan);
sally.Kids.Add(zella);
var billy = new Person()
{
Name = "Billy",
Age = 1,
Gender = "M"
};
ConsoleWriteLine($"Row state for Tristan: {tristan.AsInfraWrapped().GetRowState()}");
billy.Phones = new Phone[] { new Phone()
{
Number = "707-555-1236", PhoneTypeID = PhoneType.Mobile, Owner = billy
} };
sally.Kids.Add(CEF.IncludeObject(billy, ObjectState.Added));
sally.Age += 1;
billy.Age += 1;
// On saving here, of note we're inserting a new person, getting their id back, carrying this down to the child (phone as the owner), saving the child - all this despite the Phone class not even *having* a PersonID on it (it's just the Owner property which assumes this relationship exists, and we established that in the setup)
dynamic billyPhone = billy.Phones.First().AsDynamic();
ConsoleWriteLine($"Row state for Billy's phone: {billyPhone.GetRowState()}");
CEF.DBSave();
ConsoleWriteLine($"Billy's phone has a PhoneID tracked despite the POCO object model not containing this field: {billyPhone.PhoneID}");
// Remove Zella from Sally's kids - should just nullify the ParentPersonID
sally.Kids.Remove(zella);
CEF.DBSave();
// Put it back now
sally.Kids.Add(zella);
CEF.DBSave();
// Swap ownership of Billy's phone to Zella - saving should reflect Zella's new ownership (and Billy's non-ownership)
// Note: our POCO here does not implement INotifyPropertyChanged, so this change in row state is not reflected until we do something meaningful (e.g. save)
var phone = billy.Phones.First();
phone.Owner = zella;
ConsoleWriteLine($"Row state for phone in question (unchanged): {phone.AsInfraWrapped().GetRowState()}");
CEF.DBSave();
// Ok, we're done..? If not, the local scope will be rebuilt next time it's used. In this case, all our prior work is wiped out, we're "starting fresh"
CEF.CurrentServiceScope.Dispose();
// One way to repopulate our service scope is to load people with 2 retrievals: start with the parent (by key), then a second call (by parent id) for children - merge into the same set (extension method names help make that clearer)
// The "by parent" retrieval is done as an extension method that we propose can and should be code generated based on the db object - forms a stronger contract with the db so if say a parameter changes, we could get a compile-time error to track down and fix
// (We could also have created a procedure to "load family" in one call - a union for parent and children. In many cases, reducing DB round-trips helps performance at cost of a slightly more complex data layer.)
// Next, delete parent (with cascade option), save (notice it properly deletes children first, then parent)
// Framework has automtically wired up the relationships between parent and child such that marking the parent for deletion has automatically marked children for deletion as well
// Note that removal from collection is not considered deletion - there could be other reasons you're removing from a collection, but might offer a way to interpret this as deletion on a one-off basis in future
// Also note that we use Tristan's PersonID not "tristan" itself - scope was disposed above, no longer has wrappers, etc.
// And what about Billy's phone? If it had audit history, we'd prefer to have the framework manage/delete it too (versus say leaving it to cascaded deletes in the database) - and we do achieve that because of an extra call to load Phones for a parent and all their kids
// Important question: does a phone *require* an owner? This will be left to key service in vnext as it has an important implication on deletion here: cascade deletes or set to null where able to (for this example, cascades the deletion to the Phone)
var sallysFamily = new EntitySet <Person>().DBRetrieveByKey(sally.PersonID).DBAppendByParentID(sally.PersonID);
var sallysFamilyPhones = new EntitySet <Phone>().DBRetrieveAllForFamily(sally.PersonID);
var newSally = (from a in sallysFamily where a.Kids != null && a.Kids.Any() select a).First();
var newTristan = (from a in sallysFamily where a.PersonID == tristan.PersonID select a).First();
ConsoleWriteLine($"Row state for Tristan (unchanged): {newTristan.AsInfraWrapped().GetRowState()}");
CEF.DeleteObject(newSally);
ConsoleWriteLine($"Row state for Tristan (deleted): {newTristan.AsInfraWrapped().GetRowState()}");
ConsoleWriteLine($"Saved rows: {CEF.DBSave().Count()}");
ConsoleWriteLine("Please wait, starting background process.");
Exception toReport = null;
var backgroundTask = new Task(() =>
{
try
{
using (CEF.NewServiceScope(new ServiceScopeSettings()
{
InitializeNullCollections = true
}))
{
// Create an entire object graph using POCO's
// Note that our arrays will end up getting coverted to EntitySet's automatically when we add the root (greatgrandpa) to the scope later
// We can also intermix POCO and wrapper types at will
var greatgrandpa = new Person()
{
Name = "Zeke", Age = 92, Gender = "M"
};
var grandpa = new Person()
{
Name = "Zeke Jr.", Age = 70, Gender = "M"
};
var mom = new PersonWrapped()
{
Name = "Wilma", Age = 48, Gender = "F"
};
var me = new Person()
{
Name = "Joe", Age = 29, Gender = "M"
};
// Notice, using linkages here that could/should be recognized either way as related data - this is testing the relationship existing *both* ways, should not fail
var myphone = new Phone()
{
Owner = me, Number = "707-555-1919", PhoneTypeID = PhoneType.Home
};
me.Phones = new Phone[] { myphone };
var auntie = new Person()
{
Name = "Betty", Age = 50, Gender = "F", Phones = new Phone[] { new Phone()
{
Number = "707-555-1240", PhoneTypeID = PhoneType.Home
} }
};
var cuz1 = new Person {
Name = "Knarf", Age = 40, Gender = "M", Phones = new Phone[] { new Phone()
{
Number = "510-555-5555", PhoneTypeID = PhoneType.Mobile
} }
};
var cuz2 = new Person {
Name = "Hazel", Age = 40, Gender = "F", Phones = new Phone[] { new Phone()
{
Number = "510-555-8888", PhoneTypeID = PhoneType.Mobile
} }
};
greatgrandpa.Kids = new Person[] { grandpa };
grandpa.Kids = new Person[] { auntie, mom };
auntie.Kids = new Person[] { cuz1, cuz2 };
CEF.IncludeObject(greatgrandpa, ObjectState.Added);
myphone.PhoneTypeID = PhoneType.Mobile;
// But wait, we didn't initialize mom.Kids with a collection instance! - some of the details of the current scope can be adjusted, such as above where we use InitializeNullCollections=true
mom.Kids.Add(me);
mom.Phones.Add(CEF.NewObject(new Phone()
{
Number = "510-555-2222", PhoneTypeID = PhoneType.Work
}));
// Creates 3 records in DB with parent-child relationship - we'll use an explict new scope (no visibility to any pending changes above)
// Also demonstrates using 2 different connection scopes - default is transactional = true which is why we call CanCommit a la System.Transactions
using (var ss = CEF.NewServiceScope())
{
var joel = CEF.NewObject(new Person()
{
Name = "Joel", Age = 44, Gender = "M"
});
var cellnum = CEF.NewObject(new Phone()
{
Owner = joel, PhoneTypeID = PhoneType.Mobile, Number = "707-555-1234"
});
var worknum = CEF.NewObject(new Phone()
{
Owner = joel, PhoneTypeID = PhoneType.Work, Number = "707-555-1235"
});
using (var cs = CEF.NewConnectionScope())
{
// We could also use CEF.DBSave() since current scope will be "ss" now
ConsoleWriteLine($"Should be 3: {ss.DBSave().Count()}");
// This should do nothing - nothing is actually dirty!
ConsoleWriteLine($"Should be 0: {CEF.DBSave().Count()}");
// Updates 2 records (parent, child), delete other record, saves
// Of note: Phone class is NOT wrapped by a code genned object - but it still gets saved properly (we miss out on notifications, etc. unless we explicitly ask for its infra wrapper which has these)
// Also, we've updated the POCO for Joel - which has no notifications - this might be "lost" during save, but we do check for updates done in this manner and catch it
joel.Age += 1;
cellnum.Number = "707-555-7777";
CEF.DeleteObject(worknum);
CEF.DBSave();
// This *does* reflect a change in the row state prior to saving since the wrapper class implements INotifyPropertyChanged... HOWEVER, we are in a transaction, and the initial row state of "added" remains
joel.AsDynamic().Age += 1;
ConsoleWriteLine($"Row state for Joel: {joel.AsInfraWrapped().GetRowState()}");
CEF.DBSave();
// A catch handler not calling this allows the transaction to naturally roll back
cs.CanCommit();
}
using (var cs = CEF.NewConnectionScope())
{
// Finally, we can use this as a dynamic object as well and expect the same results... notice here, using our INotifyPropertyChanged wrapper does automatically change the row state...
((PersonWrapped)joel).Age += 1;
ConsoleWriteLine($"Row state for Joel: {joel.AsInfraWrapped().GetRowState()}");
ConsoleWriteLine($"Initial saves, time: {watch.ElapsedMilliseconds} ms");
watch.Restart();
// This approach creates 10000 people with 2 phone numbers each. Saving is done using BULK INSERT, and is expectedly very fast. One constraint: we lose round-tripping of key assignment that was illustrated before.
// Couple of options here, but in this case, I will save people, do a direct query to *just* retrieve people ID's (also illustrates partial loading), populate phone numbers based on that list and save again.
EntitySet <Person> city = new EntitySet <Person>();
for (int i = 0; i < 10000; ++i)
{
city.Add(new Person()
{
Name = $"N{i}", Age = random.Next(1, 90), Gender = random.Next(2) == 0 ? "F" : "M"
});
}
// Default insert threshold to revert to BULK INSERT is 100,000 rows, so we use an explicit option to do this with 10,000
CEF.DBSave(new DBSaveSettings()
{
BulkInsertMinimumRows = 10000
});
ConsoleWriteLine($"Saved 10,000 new people, time: {watch.ElapsedMilliseconds} ms");
watch.Restart();
// Here's an example of using raw SQL: yes, we can do this even in the 0.2 release! Points to the fact we should expect more LINQ to SQL type of enhancements in the future
// The fact we're loading a very lean Person entity set isn't a problem: we're not updating people here, we're just adding phones and it's nice to have people available to identify ownership (just assuming the ID's are sequential isn't a great idea, the database may have had other ideas!)
using (CEF.NewServiceScope())
{
EntitySet <Phone> phones = new EntitySet <Phone>();
foreach (var p in new EntitySet <Person>().DBRetrieveByQuery(CommandType.Text, "SELECT PersonID FROM CEFTest.Person"))
{
phones.Add(new Phone()
{
Owner = p, PhoneTypeID = PhoneType.Home, Number = $"{random.Next(10)}{random.Next(10)}{random.Next(10)}-{random.Next(10)}{random.Next(10)}{random.Next(10)}{random.Next(10)}"
});
phones.Add(new Phone()
{
Owner = p, PhoneTypeID = PhoneType.Mobile, Number = $"{random.Next(10)}{random.Next(10)}{random.Next(10)}-{random.Next(10)}{random.Next(10)}{random.Next(10)}{random.Next(10)}"
});
}
// This method of saving limits to this specific set
phones.DBSave(new DBSaveSettings()
{
BulkInsertMinimumRows = 10000, RowSavePreview = (row) => { return(true, ObjectState.Added); }
});
}
ConsoleWriteLine($"Saved 20,000 new phones, time: {watch.ElapsedMilliseconds} ms");
watch.Restart();
cs.CanCommit();
}
}
