如何使用Entity Framework将存储过程的结果映射到具有不同命名参数的实体 [英] How to use Entity Framework to map results of a stored procedure to entity with differently named parameters
问题描述
..
存储过程返回值：UT_ID，UT_LONG_NM，UT_STR_AD，UT_CITY_AD，UT_ST_AD，UT_ZIP_CD_AD，UT_CT
如果我创建一个这样的对象...
public class DBUnitEntity
{
public Int16 UT_ID {get;组; }
public string UT_LONG_NM {get;组; }
public string UT_STR_AD {get;组; }
public string UT_CITY_AD {get;组; }
public string UT_ST_AD {get;组; }
public Int32 UT_ZIP_CD_AD {get;组; }
public string UT_CT {get;组; }
}
和一个这样的EntityTypeConfiguration ...
public class DbUnitMapping：EntityTypeConfiguration< DBUnitEntity>
{
public DbUnitMapping（）
{
HasKey（t => t.UT_ID）;
}
}
...我添加的OnModelCreating DbContext，那么我可以从数据库中获取实体，这是很好的，使用这个....
var allUnits = _context.Database.SqlQuery< DBUnitEntity>（StoredProcedureHelper.GetAllUnitsProc）;
但是什么不起作用
如果我想要一个这样的实体，有更友好的名字....
public class UnitEntity：IUnit
{
public Int16 UnitId {get;组; }
public string Name {get;组; }
public string Address {get;组; }
public string City {get;组; }
public string State {get;组; }
public Int32 Zip {get;组; }
public string Category {get;组; }
}
和一个这样的EntityTypeConfiguration ...
public UnitMapping（）
{
HasKey（t => t.UnitId）;
属性（t => t.UnitId）.HasColumnName（UT_ID）;
属性（t => t.Name）.HasColumnName（UT_LONG_NM）;
属性（t => t.Address）.HasColumnName（UT_STR_AD）;
属性（t => t.City）.HasColumnName（UT_CITY_AD）;
属性（t => t.State）.HasColumnName（UT_ST_AD）;
属性（t => t.Zip）.HasColumnName（UT_ZIP_CD_AD）;
属性（t => t.Category）.HasColumnName（UT_CT）;
}
当我尝试获取数据时，我得到一个System.Data.EntityCommandExecutionException与消息....
数据读取器与指定的DataAccess.EFCodeFirstSample.UnitEntity不兼容。类型UnitId的成员不会在数据读取器中有一个相同的列，名称相同。
如果我将存储过程命名属性添加到实体，它会向下一个投诉未知属性。
HasColumnName是否无法正常工作？我希望/希望在此代码优先存储过程中使用流动的EF？
更新：
使用DataAnnotations（来自ComponentModel的键和EntityFramework的列） ）... ala
public class UnitEntity：IUnit
{
[Key]
[Column（UT_ID）]
public Int16 UnitId {get;组; }
public string Name {get;组; }
根据数据库相同的命名，DBUnitEntity完全不需要任何EntityTypeConfiguration（即只添加[Key] Attribute），但对于具有与数据库不匹配的属性名称的实体（与之前相同的错误）没有任何作用。
I不要在模型中使用ComponentModel注释，但是如果我可以帮助它，我真的不想使用模型中的EntityFramework Annotations（不想将Model绑定到任何特定的数据访问框架） p>
从实体框架代码第一的书籍（第155页）：
SQLQuery方法总是尝试根据属性名称进行列到属性的匹配。 。
没有匹配的列到属性名称不会占用任何映射。例如，如果您将DestinationId属性映射到目标表中称为Id的列，则SqlQuery方法将不会使用此映射。
所以在调用存储过程时不能使用映射。一个解决方法是修改存储过程，以返回结果，并为每个列匹配对象属性名称的别名返回结果。
从SomeTable中选择UT_STR_AD作为地址等
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)
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
这篇关于如何使用Entity Framework将存储过程的结果映射到具有不同命名参数的实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
