How to use Entity Framework to map results of a st

问题:

I am trying to create a basic example using Entity Framework to do the mapping of the output of a SQL Server Stored procedure to an entity in C#, but the entity has differently (friendly) names parameters as opposed to the more cryptic names. I am also trying to do this with the Fluent (i.e. non edmx) syntax.

---

What works ....

The stored procedure returns values called: UT_ID, UT_LONG_NM, UT_STR_AD, UT_CITY_AD, UT_ST_AD, UT_ZIP_CD_AD, UT_CT

If I create an object like this ...

public class DBUnitEntity
{
    public Int16 UT_ID { get; set; }
    public string UT_LONG_NM { get; set; }
    public string UT_STR_AD { get; set; }
    public string UT_CITY_AD { get; set; }
    public string UT_ST_AD { get; set; }
    public Int32 UT_ZIP_CD_AD { get; set; }
    public string UT_CT { get; set; } 
}

and an EntityTypeConfiguration like this ...

public class DbUnitMapping: EntityTypeConfiguration<DBUnitEntity>
{
        public DbUnitMapping()
        {
            HasKey(t => t.UT_ID);
        }
}

... which I add in the OnModelCreating of the DbContext, then I can get the entities just fine out of the database, which is nice, using this ....

var allUnits = _context.Database.SqlQuery<DBUnitEntity>(StoredProcedureHelper.GetAllUnitsProc);

---

BUT, What Doesn't Work

If I want an entity like this, with friendlier names ....

public class UnitEntity : IUnit
{
    public Int16 UnitId { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public Int32 Zip { get; set; }
    public string Category { get; set; }
}

and an EntityTypeConfiguration like this ...

    public UnitMapping()
    {
        HasKey(t => t.UnitId);

        Property(t => t.UnitId).HasColumnName("UT_ID");
        Property(t => t.Name).HasColumnName("UT_LONG_NM");
        Property(t => t.Address).HasColumnName("UT_STR_AD");
        Property(t => t.City).HasColumnName("UT_CITY_AD");
        Property(t => t.State).HasColumnName("UT_ST_AD");
        Property(t => t.Zip).HasColumnName("UT_ZIP_CD_AD");
        Property(t => t.Category).HasColumnName("UT_CT");
    }

When I try to get the data I get a System.Data.EntityCommandExecutionException with the message ....

"The data reader is incompatible with the specified 'DataAccess.EFCodeFirstSample.UnitEntity'. A member of the type, 'UnitId', does not have a corresponding column in the data reader with the same name."

If I add the "stored procedure named" property to the entity, it goes and complains about the next "unknown" property.

Does "HasColumnName" not work as I expect/want it to in this code-first stored procedure fluent style of EF?

---

Update:

Tried using DataAnnotations (Key from ComponentModel, and Column from EntityFramework) ... ala

public class UnitEntity : IUnit
{
    [Key]
    [Column("UT_ID")]
    public Int16 UnitId { get; set; }
    public string Name { get; set; }

That did remove the need for any EntityTypeConfiguration at all for the DBUnitEntity with the database-identical naming (i.e. just adding the [Key] Attribute), but did nothing for the entity with the property names that don't match the database (same error as before).

I don't mind using the ComponentModel Annotations in the Model, but I really don't want to use the EntityFramework Annotations in the model if I can help it (don't want to tie the Model to any specific data access framework)

回答1:

From Entity Framework Code First book (page 155):

The SQLQuery method always attempts the column-to-property matching based on property name... None that the column-to-property name matching does not take any mapping into account. For example, if you had mapped the DestinationId property to a column called Id in the Destination table, the SqlQuery method would not use this mapping.

So you cannot use mappings when calling stored procedure. One workaround is to modify your stored procedure to return result with aliases for each column that will match your object properties' names.

Select UT_STR_AD as Address From SomeTable etc

回答2:

This isn't using Entity Framework but it is stemming from dbcontext. I have spent hours upon hours scouring the internet and using dot peek all for nothing. I read some where that the ColumnAttribute is ignored for SqlQueryRaw. But I have crafted up something with reflection, generics, sql datareader, and Activator. I am going to be testing it on a few other procs. If there is any other error checking that should go in, comment.

public static List<T> SqlQuery<T>( DbContext db, string sql, params object[] parameters)
    {

        List<T> Rows = new List<T>();
        using (SqlConnection con = new SqlConnection(db.Database.Connection.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (var param in parameters)
                    cmd.Parameters.Add(param);
                con.Open();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.HasRows)
                    {
                        var dictionary = typeof(T).GetProperties().ToDictionary(
                   field => CamelCaseToUnderscore(field.Name), field => field.Name);
                        while (dr.Read())
                        {
                            T tempObj = (T)Activator.CreateInstance(typeof(T));
                            foreach (var key in dictionary.Keys)
                            {
                                PropertyInfo propertyInfo = tempObj.GetType().GetProperty(dictionary[key], BindingFlags.Public | BindingFlags.Instance);
                                if (null != propertyInfo && propertyInfo.CanWrite)
                                    propertyInfo.SetValue(tempObj, Convert.ChangeType(dr[key], propertyInfo.PropertyType), null);
                            }
                            Rows.Add(tempObj);
                        }
                    }
                    dr.Close();
                }
            }
        }
        return Rows;
    }

    private static string CamelCaseToUnderscore(string str)
    {
        return Regex.Replace(str, @"(?<!_)([A-Z])", "_$1").TrimStart('_').ToLower();
    }

Also something to know is that all of our stored procs return lowercase underscore delimited. The CamelCaseToUnderscore is built specifically for it.

Now BigDeal can map to big_deal

You should be able to call it like so

Namespace.SqlQuery<YourObj>(db, "name_of_stored_proc", new SqlParameter("@param",value),,,,,,,);

回答3:

The example posted by "DeadlyChambers" is great but I would like to extend the example to include the ColumnAttribute that you can use with EF to add to a properties to map a SQL field to a Class property.

Ex.

[Column("sqlFieldName")]
public string AdjustedName { get; set; }

Here is the modified code.
This code also include a parameter to allow for custom mappings if needed by passing a dictionary.
You will need a Type Converter other than Convert.ChangeType for things like nullable types.
Ex. If you have a field that is bit in the database and nullable boolean in .NET you will get a type convert issue.

/// <summary>
/// WARNING: EF does not use the ColumnAttribute when mapping from SqlQuery. So this is a "fix" that uses "lots" of REFLECTION
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="database"></param>
/// <param name="sqlCommandString"></param>
/// <param name="modelPropertyName_sqlPropertyName">Model Property Name and SQL Property Name</param>
/// <param name="sqlParameters">SQL Parameters</param>
/// <returns></returns>
public static List<T> SqlQueryMapped<T>(this System.Data.Entity.Database database, 
    string sqlCommandString, 
    Dictionary<string,string> modelPropertyName_sqlPropertyName, 
    params System.Data.SqlClient.SqlParameter[] sqlParameters)
{
    List<T> listOfT = new List<T>();

    using (var cmd = database.Connection.CreateCommand())
    {
        cmd.CommandText = sqlCommandString;
        if (cmd.Connection.State != System.Data.ConnectionState.Open)
        {
            cmd.Connection.Open();
        }

        cmd.Parameters.AddRange(sqlParameters);

        using (var dataReader = cmd.ExecuteReader())
        {
            if (dataReader.HasRows)
            {
                // HACK: you can't use extension methods without a type at design time. So this is a way to call an extension method through reflection.
                var convertTo = typeof(GenericExtensions).GetMethods(BindingFlags.Static | BindingFlags.Public).Where(mi => mi.Name == "ConvertTo").Where(m => m.GetParameters().Count() == 1).FirstOrDefault();

                // now build a new list of the SQL properties to map
                // NOTE: this method is used because GetOrdinal can throw an exception if column is not found by name
                Dictionary<string, int> sqlPropertiesAttributes = new Dictionary<string, int>();
                for (int index = 0; index < dataReader.FieldCount; index++)
                {
                    sqlPropertiesAttributes.Add(dataReader.GetName(index), index);
                }

                while (dataReader.Read())
                {
                    // create a new instance of T
                    T newT = (T)Activator.CreateInstance(typeof(T));

                    // get a list of the model properties
                    var modelProperties = newT.GetType().GetProperties();

                    // now map the SQL property to the EF property
                    foreach (var propertyInfo in modelProperties)
                    {
                        if (propertyInfo != null && propertyInfo.CanWrite)
                        {
                            // determine if the given model property has a different map then the one based on the column attribute
                            string sqlPropertyToMap = (propertyInfo.GetCustomAttribute<ColumnAttribute>()?.Name ?? propertyInfo.Name);
                            string sqlPropertyName;
                            if (modelPropertyName_sqlPropertyName!= null && modelPropertyName_sqlPropertyName.TryGetValue(propertyInfo.Name, out sqlPropertyName))
                            {
                                sqlPropertyToMap = sqlPropertyName;
                            }

                            // find the SQL value based on the column name or the property name
                            int columnIndex;
                            if (sqlPropertiesAttributes.TryGetValue(sqlPropertyToMap, out columnIndex))
                            {
                                var sqlValue = dataReader.GetValue(columnIndex);

                                // ignore this property if it is DBNull
                                if (Convert.IsDBNull(sqlValue))
                                {
                                    continue;
                                }

                                // HACK: you can't use extension methods without a type at design time. So this is a way to call an extension method through reflection.
                                var newValue = convertTo.MakeGenericMethod(propertyInfo.PropertyType).Invoke(null, new object[] { sqlValue });

                                propertyInfo.SetValue(newT, newValue);
                            }
                        }
                    }

                    listOfT.Add(newT);
                }
            }
        }
    }

    return listOfT;
}