I am saving a large amount of data that was received as models to various tables in a SQLite.net database. I currently have 15 methods for each DB type which are all in an identical format. I am trying to write a method using reflection which allows me to pass in expressions which retrieve the data from the model and data base object (dbo) which compiles fine but the model data is unable to be compiled to SQL.
The exception i receive is "Cannot compile: Parameter".
I have tried various formats of this code, including a combination of Expressions and Func set ups, separating the model from the SQLite query and getting the ID before in its own expression, however i believe that everything is being compiled together regardless.
This is an example of 1 of the 15 methods that are called for each model type. I want to replace this with 1 method that can save all types for me
private async Task SaveDevelopments(List<DevelopmentsModel> developmentModels)
{
var toInsert = new List<DevelopmentsDBO>();
var toUpdate = new List<DevelopmentsDBO>();
foreach (var developmentModel in developmentModels)
{
var developmentDBO = await DatabaseHelpers.Table<DevelopmentsDBO>().Where(d => d.DevelopmentID == developmentModel.DevelopmentID).FirstOrDefaultAsync();
if (developmentDBO == null)
{
developmentDBO = new DevelopmentsDBO();
ModelFactory.UpdateFromModel(developmentDBO, developmentModel);
toInsert.Add(developmentDBO);
}
else
{
ModelFactory.UpdateFromModel(developmentDBO, developmentModel);
toUpdate.Add(developmentDBO);
}
}
await DatabaseHelpers.InsertAllAsync(toInsert);
await DatabaseHelpers.UpdateAllAsync(toUpdate);
}
This was attempt 1 at using reflection and expressions to replace all 15 methods with a shared method
await SaveModels<DevelopmentDBO, DevelopmentsModel>(data.DevelopmentModels, (dbo, model) => dbo.DevelopmentID == model.DevelopmentID);
private async Task SaveModels<DBO, Model>(List<Model> models, Func<Model, DBO, bool> findSelf) where DBO : new()
{
var toInsert = new List<DBO>();
var toUpdate = new List<DBO>();
foreach (var model in models)
{
var dbo = await DatabaseHelpers.Table<DBO>().Where(x => findSelf.Invoke(x, model)).FirstOrDefaultAsync();
if (dbo == null)
{
dbo = (DBO)Activator.CreateInstance(typeof(DBO));
ModelFactory.UpdateFromModel(dbo, model);
toInsert.Add(dbo);
}
else
{
ModelFactory.UpdateFromModel(dbo, model);
toUpdate.Add(dbo);
}
}
await DatabaseHelpers.InsertAllAsync(toInsert);
await DatabaseHelpers.UpdateAllAsync(toUpdate);
}
This was attempt 2
await SaveModels<DevelopmentDBO, DevelopmentsModel>(data.DevelopmentModels, x => x.DevelopmentID, x => x.DevelopmentID);
private async Task SaveModels<DBO, Model>(List<Model> models, Func<Model, Guid> findModelID, Expression<Func<DBO, Guid>> findDBOID) where DBO : new()
{
var toInsert = new List<DBO>();
var toUpdate = new List<DBO>();
foreach (var model in models)
{
Guid modelID = findModelID.Invoke(model);
Expression<Func<DBO, bool>> findSelf = x => findDBOID.Compile().Invoke(x) == modelID;
var dbo = await DatabaseHelpers.Table<DBO>().Where(findSelf).FirstOrDefaultAsync();
if (dbo == null)
{
dbo = (DBO)Activator.CreateInstance(typeof(DBO));
ModelFactory.UpdateFromModel(dbo, model);
toInsert.Add(dbo);
}
else
{
ModelFactory.UpdateFromModel(dbo, model);
toUpdate.Add(dbo);
}
}
await DatabaseHelpers.InsertAllAsync(toInsert);
await DatabaseHelpers.UpdateAllAsync(toUpdate);
}
My goal as above is to make a method that I can use to replace 15 nearly identical methods that save each type of DBO. I am not keen on using SQL in string format as this is difficult to read for future developers and easy to make mistakes.
Is there another way of doing this that I'm not aware of?
