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