0

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();
}
Ogglas
  • 62,132
  • 37
  • 328
  • 418
  • 1
    Without going too much into details. Wouldn't it be better to have single ID column that would constitue a PK on its own? I always make sure that all of my entities can be identified by one ID column... – Michal B. May 15 '18 at 11:19
  • @MichalB. It would be possible but some updates that we do are transferred back and then we need the keys separately. We could of course store an extra column that is the primary key but this would affect many other keys as well. If you look at `TPFeeCalculation` you will see that a subset of the primary key is also two foreign keys for other classes. All in all I think it would hurt the application to not have the same primary key as the systems we are talking to. – Ogglas May 15 '18 at 11:26
  • 1
    Use `someDbSet.Find(keyList.ToArray())` – Evk May 15 '18 at 11:29
  • @Evk Thanks! I just found the answer myself https://stackoverflow.com/questions/42923480/convert-list-to-params-c-sharp – Ogglas May 15 '18 at 11:35
  • It's easier and much better for performance to get the key values right from the EF metadata: https://stackoverflow.com/a/8083687/861716 – Gert Arnold May 15 '18 at 11:41
  • @GertArnold Since we are getting information from an API it is not sure that the information is stored in the database to begin with and it is not an entity loaded in `DbContext`. I have read your answer for getting a entity key for DbEntityEntry here https://stackoverflow.com/a/15896086/3850405 but I don't think it will be much faster. If I try to load the key from `value` above it will be null since the object is not attached to `DbContext`. – Ogglas May 15 '18 at 13:18
  • OK, in that case I think you could gain some performance by caching reflection results (like PropertyInfos per type). Reflection is really slow. – Gert Arnold May 15 '18 at 13:29

0 Answers0