I'm writing an application where we call 20+ APIs to add or update values in our database with Entity Framework. The models can have keys that varies between 1-6 properties. I have written a generic method for Add or Update to avoid duplicating code but I'm stuck calling DbSet.Find()
dynamically with several keys. If every model would have the same number of keys it would not be a problem but that is unfortunately not the case.
Example models:
public class TPClassification
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public Classifications ClassificationId { get; set; }
[MaxLength(100)]
public string Description { get; set; }
public bool IsCyclical { get; set; }
[MaxLength(400)]
public string Comment { get; set; }
}
public class TPFeeCalculation
{
[Key, Column(Order = 0)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[MaxLength(15)]
public string BusinessSystemId { get; set; }
[Key, Column(Order = 1)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int CaseId { get; set; }
[ForeignKey("BusinessSystemId,CaseId")]
public virtual TPCase TPCase { get; set; }
[Key, Column(Order = 2)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[MaxLength(5)]
public string Action { get; set; }
[Key, Column(Order = 3)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Cycle { get; set; }
[ForeignKey("BusinessSystemId,CaseId,Action,Cycle")]
public virtual TPRenewalCycle TPRenewalCycle { get; set; }
[Key, Column(Order = 4)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int RateNo { get; set; }
[MaxLength(100)]
public string RateDescription { get; set; }
}
API calls:
public ICollection<TPFeeCalculation> GetFeeCalculation(string businessSystemId, int caseId)
{
var url = $"/api/feecalculations/{businessSystemId}/{caseId}";
var result = GetRequest<ICollection<TPFeeCalculation>>(url);
return result;
}
public ICollection<TPClassification> GetClassifications()
{
var url = $"/api/classifications/";
var result = GetRequest<ICollection<TPClassification>>(url);
return result;
}
AddOrUpdate methods:
// All methods could be called like this but it will result in a lof of code that is nearly duplicated
//internal void AddOrUpdateClassifications()
//{
// var result = _IntegrationApiService.GetClassifications();
//
// //foreach (var value in result)
// //{
// // try
// // {
// // var current = _DbContext.Classifications.Find(value.ClassificationId);
// // if (current == null)
// // {
// // _DbContext.Classifications.Add(value);
// // }
// // else
// // {
// // _DbContext.Entry(current).CurrentValues.SetValues(value);
// // }
// // }
// // catch (Exception e)
// // {
// // throw;
// // }
// //}
// //_DbContext.SaveChanges();
//}
internal void AddOrUpdateClassifications()
{
var result = _IntegrationApiService.GetClassifications();
GenericAddOrUpdate(result, "ClassificationId");
}
internal void AddOrUpdateFeeCalculations(string businessSystemId, int caseId)
{
var result = _IntegrationApiService.GetFeeCalculation(businessSystemId, caseId);
//This does not work
//GenericAddOrUpdate(result, "BusinessSystemId", "CaseId", "Action", "Cycle", "RateNo");
}
private void GenericAddOrUpdate<T>(ICollection<T> values, params string[] keyValues) where T : class
{
foreach (var value in values)
{
try
{
var keyList = new List<object>();
foreach (var keyValue in keyValues)
{
var propertyInfo = value.GetType().GetProperty(keyValue);
var propertyValue = propertyInfo.GetValue(value);
keyList.Add(propertyValue);
}
var someDbSet = _DbContext.Set(typeof(T));
//I need to add all the values from keyList here. I can't add the list directly since I will get the error below. Example with keyList[0] to show working code
//The specified parameter type 'System.Collections.Generic.List`1[[System.Object, mscorlib, Version=4.0.0.0, Culture=neutral, //PublicKeyToken=b77a5c561934e089]]' is not valid. Only scalar types, such as System.Int32, System.Decimal, System.DateTime, and System.Guid, are supported.
var current = someDbSet.Find(keyList[0]);
if (current == null)
{
someDbSet.Add(value);
}
else
{
_DbContext.Entry(current).CurrentValues.SetValues(value);
}
}
catch (Exception e)
{
throw;
}
}
_DbContext.SaveChanges();
}