samedi 20 janvier 2018

EF Core 2.0: Determine which PropertyInfos of a generic TEntity are primary keys via reflection

I am writing a MySQL INSERT ... ON DUPLICATE KEY UPDATE implementation via a raw SQL command in EF Core 2.0. I am very close to a working solution, but the only problem that I have is determining which PropertyInfos read via reflection are primary keys. In the CreateUpdates() method below, I need to filter primary keys out from columnProperties so they are not a part of the update SQL statement. Can anyone suggest a method for doing this?

I have tried using EntityFramework.PrimaryKey , but I can't seem to get it to work with generics (TEntity).

I have included all of my relevant code, but the piece I am focused on in this question is the TODO in the last method, CreateUpdates(). Full source code is here. Thanks for any suggestions!

private static void InsertOnDuplicateKeyUpdate<TEntity>(DbContext dbContext) where TEntity : class
{
    var columnProperties = GetColumnPropertiesLessBaseEntityTimestamps<TEntity>();
    var tableName = GetTableName<TEntity>();
    var columns = string.Join(", ", columnProperties.Select(x => x.Name));
    var values = CreateValues<TEntity>(columnProperties);
    var updates = CreateUpdates(columnProperties);
    var rawSqlString = "INSERT INTO " + tableName + " (" + columns + ") VALUES " + values +
                        " ON DUPLICATE KEY UPDATE " + updates;
    dbContext.Set<TEntity>().FromSql(rawSqlString);
    dbContext.SaveChanges();
}

private static string GetTableName<TEntity>()
{
    return typeof(TEntity).Name.Pluralize().ToLower();
}

private static List<PropertyInfo> GetColumnPropertiesLessBaseEntityTimestamps<TEntity>()
{
    return typeof(TEntity).GetProperties().Where(x =>
        x.PropertyType.Namespace != "System.Collections.Generic" &&
        !new List<string> {"CreatedDateUtc", "ModifiedDateUtc"}.Contains(x.Name)).ToList();
}

private static string CreateValues<TEntity>(IReadOnlyCollection<PropertyInfo> columnProperties)
{
    return GetSeedRows<TEntity>().Select(row => CreateRowValues(columnProperties, row)).Aggregate("",
        (current, rowValues) => current == "" ? rowValues : current + ", " + rowValues);
}

private static string CreateRowValues<TEntity>(IEnumerable<PropertyInfo> columnProperties, TEntity row)
{
    return (from property in columnProperties
                let value = row.GetType().GetProperty(property.Name).GetValue(row)
                select WrapStringPropertyValueInSingleQuotes(property, value)).Aggregate("",
                (current, value) => current == "" ? "(" + value : current + ", " + value) + ")";
}

private static object WrapStringPropertyValueInSingleQuotes(PropertyInfo property, object value)
{
    if (property.PropertyType == typeof(string))
        value = "'" + value + "'";
    return value;
}

private static string CreateUpdates(IEnumerable<PropertyInfo> columnProperties)
{
    //TODO: filter keys out
    return columnProperties.Select(property => property.Name).Aggregate("", (current, column) => current == ""
        ? column + " = VALUES(" + column + ")"
        : current + ", " + column + " = VALUES(" + column + ")");
}





Aucun commentaire:

Enregistrer un commentaire