DbContextExtensions.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using My.Dal.Core;

namespace Dal
{
	public static class DbContextExtensions
	{
		public static void UpdateRecord(this EntityEntry entity, EntityBase newRecord)
		{
			//DatabaseGenerated
			var props = entity.Properties
				.Where(p =>
					p != null
					&& p.Metadata.PropertyInfo.GetAccessors().All(x => !x.IsVirtual)
					&& !p.Metadata.IsKey()
					&& p.Metadata.FieldInfo?.DeclaringType != typeof(EntityBase)
					&& p.Metadata.PropertyInfo.CanWrite
					&& p.Metadata.PropertyInfo.CustomAttributes.All(a => a.AttributeType != typeof(DatabaseGeneratedAttribute)));
			foreach (var entityProp in props)
			{
				var modifiedProp = newRecord.GetType().GetProperties()
					.FirstOrDefault(p => p.Name == entityProp.Metadata.Name);
				if (modifiedProp == null
					|| modifiedProp.GetValue(newRecord) == null
					|| modifiedProp.GetMethod != null && modifiedProp.GetMethod.ReturnType == typeof(Guid) && (Guid)modifiedProp.GetValue(newRecord) == Guid.Empty)
				{
					continue;
				}

				var newValue = modifiedProp.GetValue(newRecord);
				var existingValue = entityProp.CurrentValue;
				if (existingValue == newValue)
				{
					continue;
				}

				entityProp.CurrentValue = newValue;
			}
		}

		public static async Task<IEnumerable<T>> FromSqlCommand<T>(this DbContext context, string procName, params SqlParameter[] parameters) where T : new()
		{
			var connStr = context.Database.GetDbConnection().ConnectionString;
			using (var conn = new SqlConnection(connStr))
			{
				try
				{
					await conn.OpenAsync();
					var command = new SqlCommand(procName, conn) { CommandType = CommandType.StoredProcedure };
					if (parameters != null && parameters.Any())
					{
						command.Parameters.AddRange(parameters);
					}

					IEnumerable<T> results = await (await command.ExecuteReaderAsync()).MapResults<T>();
					return results;
				}
				finally
				{
					conn.Close();
				}
			}

		}

		private static async Task<IEnumerable<T>> MapResults<T>(this SqlDataReader reader) where T : new()
		{
			var results = new List<T>();
			while (await reader.ReadAsync())
			{
				var result = new T();
				foreach (var prop in typeof(T).GetProperties())
				{
					try
					{
						if (!prop.CanWrite || !reader.HasColumn(prop.Name))
						{
							continue;
						}
						switch (prop.PropertyType.Name)
						{
							case "string":
								prop.SetValue(result, reader[prop.Name] is DBNull ? null : (string)reader[prop.Name]);
								break;
							case "int":
								prop.SetValue(result, reader[prop.Name] is DBNull ? null : (int?)int.Parse(reader[prop.Name].ToString()));
								break;
							case "decimal":
								prop.SetValue(result, reader[prop.Name] is DBNull ? null : (decimal?)decimal.Parse(reader[prop.Name].ToString()));
								break;
							case "bool":
								prop.SetValue(result, reader[prop.Name] is DBNull ? null : (bool?)bool.Parse(reader[prop.Name].ToString()));
								break;
							case "DateTime":
								prop.SetValue(result, reader[prop.Name] is DBNull ? null : (DateTime?)DateTime.Parse(reader[prop.Name].ToString()));
								break;
							default:
								prop.SetValue(result, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
								break;
						}
					}
					catch (Exception ex)
					{
						throw new Exception($"Unable to map property {prop.Name}.", ex.InnerException);
					}
				}
				results.Add(result);
			}
			return results;
		}

		private static bool HasColumn(this IDataRecord dr, string columnName)
		{
			for (var i = 0; i < dr.FieldCount; i++)
			{
				if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
					return true;
			}
			return false;
		}

		// ToDo: Figure out something better than `object`.
		internal static SqlParameter[] BuildParameterList(this object model)
		{
			return BuildParameterList(model, null);
		}

		// ToDo: I don't think we actually need or use Comments. Investigate any relevant requirements. 
		internal static SqlParameter[] BuildParameterList(this object model, string auditUser)
		{
			List<SqlParameter> paramList = new List<SqlParameter>();
			var entityName = model.GetType().Name.Replace("Model", "");
			foreach (var prop in model.GetType()
				.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public))
			{
				if (!model.GetType().Name.Contains("AnonymousType")
					&& (!prop.CanWrite || !IsPrimitive(prop.PropertyType)))
				{
					continue;
				}

				var value = prop.GetValue(model, null);
				if (value is DateTime d && d < new DateTime(1900, 1, 1))
				{
					value = null;
				}

				paramList.Add(new SqlParameter($"@{prop.Name}", value ?? DBNull.Value));
			}

			if (!string.IsNullOrEmpty(auditUser))
			{
				paramList.Add(new SqlParameter("@AuditUser", auditUser));
			}

			var paramToChange = paramList.SingleOrDefault(x => x.ParameterName == $"@{entityName}Id");
			if (paramToChange != null && (int)paramToChange.Value == 0)
			{
				paramToChange.Value = -1;
			}

			return paramList.ToArray();
		}

		private static bool IsPrimitive(Type type)
		{
			if (type == typeof(string) || type == typeof(DateTime) || type == typeof(decimal) || type.IsPrimitive)
			{
				return true;
			}

			if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
			{
				foreach (var t in type.GetGenericArguments())
				{
					if (!t.IsGenericType && t.IsValueType && t.IsPrimitive || t == typeof(string) ||
						t == typeof(DateTime) || t == typeof(decimal))
					{
						return true;
					}
				}
			}

			return false;
		}
	}
}