Longing to know

One deficiency with Entity Framework is the ability to load data from a stored procedure or a direct query when the column names don’t match your model property names exactly. In one of my previous posts, I detailed how the EF API can be used to retrieve column mappings. These column mappings can be used in conjunction with a SqlDataReader to map a query result, properly, to your EF model.

Why would someone want to do this? Well, in my case, I use EF to query a few models and it works fine in most cases. However, I also had another stored procedure for other operations to improve performance. Effectively, the underlying model that I wanted to use was the exact same, and the return column names were going to be the exact same. The model’s properties, though, didn’t match the return column names. Hence, there are entity mappings.

The previous code that I mentioned would retrieve all columns as a Dictionary<string, string> with property names relating to column names. Reading data from the SqlDataReader becomes relatively easy with this information since Refleciton can be used to set the value from the DataRow to the item’s property.

I think I’ve posted some of the code from my repository, previously, that will execute a stored procedure with parameters specified as a dynamic. That method would take the dynamic, the sproc name, and the schema name. Using that information, it would execute the DBContext.Database.SqlQuery<T> to get a list of items. That’s the happy path when the model property names match DB column names. I modified this method to take the dictionary column mappings as an optional parameter. When this parameter is present, a SqlDataReader is used:

private List<V> GetSproc<V>(dynamic parameters, string sproc, string schema, Dictionary<string, string> columnMappings = null)
{
    var dictionary = parameters as IDictionary<string, object>;
    List<SqlParameter> sqlParams = DynamicToSqlParameters(parameters);
    //var executeParams = sqlParams.Select(x => string.Format("@{0}", x.ParameterName)).ToList();
    // Modify this to explicitly put in placeholders for each parameter - otherwise, order in the sproc inputs for params matters
    var executeParams = sqlParams.Select((v, i) =>
    {
        var paramName = v.ParameterName; v.ParameterName = $"{paramName}{i}"; return $"@{paramName} = @{v.ParameterName}";
    }).ToList();
    var executeStr = $"EXEC [{schema}].[{sproc}] {string.Join(", ", executeParams)}";

    // Entity framework does not support mapping when using SqlQuery.  We'll support it, though, with SQL reader.
    if (columnMappings == null)
    {
        var retSet = _dbContext.Database.SqlQuery<V>(executeStr, sqlParams.ToArray());
        return retSet.ToList();
    }
    else
    {

This is where the new code starts. I initialize a new typed return list, set up the SqlConnection, Transaction, and the SqlCommand. The SqlParameters still come from the dynamic.

        var retSet = new List<V>();
        var conn = _dbContext.Database.Connection as SqlConnection;

        var transaction = _dbContext.Transaction != null && _dbContext.Transaction.UnderlyingTransaction != null ?
            _dbContext.Transaction.UnderlyingTransaction as SqlTransaction : null;

        using (SqlCommand cmd = new SqlCommand(executeStr, conn, transaction))
        {
            cmd.CommandType = CommandType.Text;

            sqlParams.Select(x => { cmd.Parameters.Add(x); return x; }).ToList();

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

Here’s where the SqlDataReader comes into play. Once the SqlDataReader is executed, we interetate over the return sets and rows reported by the data reader. We’re only interested in the first return set, but this process could, obviously, be used to additionally populate child collections on an EF model. Iterating over the rows allows us to create the object, We use reflection to populate the property mapped to the specific column in each row. You can see I have a bit of code to handle nullables and ensure that we get a “safe” value converted to the appropriate CLR type.

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                var type = typeof(V);
                if (dr.HasRows)
                {
                    List<string> excludedProps = null;
                    var allProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                        .Where(x => (excludedProps == null || !excludedProps.Contains(x.Name)) && (x.PropertyType == typeof(string) || x.PropertyType == typeof(decimal) ||
                        (!x.PropertyType.GetInterfaces().Contains(typeof(IEnumerable)) &&
                        (AuditAndMappingExtensions.IsPrimitiveType(x.PropertyType) || AuditAndMappingExtensions.IsNullablePrimitive(x.PropertyType) || AuditAndMappingExtensions.IsNullableEnum(x.PropertyType)))))
                        .ToList();

                    while (dr.Read())
                    {
                        V item = (V)Activator.CreateInstance(type);
                        foreach (var kvp in columnMappings)
                        {
                            var prop = allProps.FirstOrDefault(x => x.Name == kvp.Key);
                            var value = dr[kvp.Value];
                            var safeType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                            var safeValue = value == null || value == DBNull.Value ?
                                (safeType == typeof(string) ? string.Empty : Activator.CreateInstance(safeType)) :
                                Convert.ChangeType(value, safeType);
                            if ((value == null || value == DBNull.Value) && prop.PropertyType.IsNullablePrimitive())
                            {
                                // Do nothing if it's a nullable primitive.
                            }
                            else
                            {
                                prop.SetValue(item, safeValue, null);
                            }
                        }

                        retSet.Add(item);
                    }
                }

                dr.Close();
            }
        }

        return retSet;
    }
}

Using this methodology, in my service layer, I can, effectively, replace any queries in my service layer to utilize procs without creating new models. This is convenient since anytime a slow-performing query is discovered, and replaced, no other code has to be changed to accommodate domain or business logic.

For completenes, the methods for creating SqlParameters from a dynamic are below.

private List<SqlParameter> DynamicToSqlParameters(dynamic parameters)
{
    var dictionary = parameters as IDictionary<string, object>;
    var sqlParams = new List<SqlParameter>();
    foreach (var param in parameters)
    {
        string name = param.Key;

        var valueDict = param.Value as IDictionary<string, object>;
        if (valueDict != null && param.Value.IsTable)
        {
            // Handle tablevalue parameters as IEnumerable inputs
            string columnName = param.Value.ColumnName;
            var parameter = new SqlParameter(name, CreateTableInput(columnName, param.Value.Values));
            parameter.TypeName = $"{_dbContext.DbSchema}.{param.Value.TableType}";
            parameter.SqlDbType = SqlDbType.Structured;
            sqlParams.Add(parameter);
        }
        else
        {
            sqlParams.Add(new SqlParameter(name, param.Value));
        }
    }

    return sqlParams;
}

private static IEnumerable<SqlDataRecord> CreateTableInput<T>(string name, IEnumerable<T> items, List<string> excludedProps = null)
{
    var type = typeof(T);
    var records = new List<SqlDataRecord>();

    if (AuditAndMappingExtensions.IsPrimitiveType(type))
    {
        var sqlType = TypeConvertor.ToSqlDbType(type);
        foreach (T value in items)
        {
            var metaData = new SqlMetaData[] { new SqlMetaData(name, sqlType) };
            SqlDataRecord record = new SqlDataRecord(metaData);
            record.SetValue(0, value);
            records.Add(record);
        }
    }
    else
    {
        var allProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .Where(x => (excludedProps == null || !excludedProps.Contains(x.Name)) && (x.PropertyType == typeof(string) || x.PropertyType == typeof(decimal) ||
            (!x.PropertyType.GetInterfaces().Contains(typeof(IEnumerable)) &&
            (AuditAndMappingExtensions.IsPrimitiveType(x.PropertyType) || AuditAndMappingExtensions.IsNullablePrimitive(x.PropertyType) || AuditAndMappingExtensions.IsNullableEnum(x.PropertyType)))))
            .ToList();

        var props = type.GetProperties()
            .Select(prop =>
            {
                var displayAttribute = (DisplayAttribute)prop.GetCustomAttributes(typeof(DisplayAttribute), false).FirstOrDefault();
                var maxLengthAttribute = (MaxLengthAttribute)prop.GetCustomAttributes(typeof(MaxLengthAttribute), false).FirstOrDefault();

                return new
                {
                    Name = prop.Name,
                    DisplayName = displayAttribute == null ? prop.Name : displayAttribute.Name,
                    Order = displayAttribute == null || !displayAttribute.GetOrder().HasValue ? 999 : displayAttribute.Order,
                    ShortName = displayAttribute == null ? string.Empty : displayAttribute.ShortName,
                    PropertyInfo = prop,
                    PropertyType = prop.PropertyType,
                    HasDisplayName = displayAttribute != null,
                    SqlDbType = TypeConvertor.ToSqlDbType(prop.PropertyType),
                    MaxLength = maxLengthAttribute?.Length ?? 200,
                    IsStringType = prop.PropertyType == typeof(string)
                };
            })
            .OrderBy(prop => prop.Order)
            .Where(prop => !string.IsNullOrEmpty(prop.DisplayName))
            .ToList();

        var metaData = props.Select(x => { var meta = x.IsStringType ? new SqlMetaData(x.Name, x.SqlDbType, x.MaxLength) : new SqlMetaData(x.Name, x.SqlDbType); return meta; }).ToArray();

        foreach (T item in items)
        {
            SqlDataRecord record = new SqlDataRecord(metaData);
            var position = 0;
            props.Select(p => { var propValue = p.PropertyInfo.GetValue(item, null); record.SetValue(position, propValue); position++; return propValue; }).ToList();
            records.Add(record);
        }
    }

    return records.AsEnumerable<SqlDataRecord>();
}