EFCore 2.1存储过程加载相关数据

Question

我遇到了一个本应在EFCore 2.1中更正的问题。我知道这在2.0中是不可行的。我的项目是MVC Core 2.1应用程序，EFCore也是2.1版本。

我在网上搜索了几种不同的措辞方式，我得到的信息并不能完成使代码正确工作所需的整个代码过程。

我有一个模型"母亲"，一个母亲有一个子模型"邮寄地址"one_answers"物理地址"。我正在使用一个带有FromSql的存储过程，以便使我能够在过程中拥有多个联接。存储过程几乎不可能用Linq编写。

我将包括我所编码的符合我已经阅读的例子的所有内容请注意：我在代码中使用区域，因为我正在编写一个具有多个功能区域的系统

型号-Mother.cs

namespace Birth.Models
{
public class Mother
{
[Key]
public int MomId { get; set; }
public string MomFirst { get; set; }
public string MomLast { get; set; }
//[ForeignKey("MomId")]
public MotherAddress Physical { get; set; }
//      public MotherAddress Mailing { get; set; }
}
//[Owned]
public class MotherAddress
{
public string pType { get; set; }
public string Street { get; set; }
public string PreDir { get; set; }
[Key]
public int MomId { get; set; }
public Mother Mother { get; set; }
}
}

接口-IbirthRepository.cs

public interface IBirthRepository
{
IQueryable<Mother> Mothers { get; }
IQueryable<MotherAddress> MotherAddresses { get; }
}

存储库-BirthRepository.cs

public class BirthRepository : IBirthRepository
{
private BirthDbContext context;
public BirthRepository(BirthDbContext ctx)
{
context = ctx;
}
public IQueryable<Mother> Mothers => context.Mothers;
public IQueryable<MotherAddress> MotherAddresses => context.MotherAddresses;
}

控制器-GetMotherController.cs

namespace VSMaintenance.Areas.Birth.Controllers
{
[Area("Birth")]
[Route("Birth/GetMother")]
public class GetMotherController : Controller
{
private BirthDbContext context;
private IBirthRepository repository;
public GetMotherController(BirthDbContext ctx, IBirthRepository repo)
{
repository = repo;
context = ctx;
}
public IActionResult Load()
{
return View("Index");
}
[HttpPost]
public async Task<ActionResult> Retrieve(Mother mother)
{
var gbd = new GetBirthData(repository, context);
var mom = new Mother();
mom = await gbd.GetMotherData(mother.MomId);
return View("Mother", mom);
}
}
}

主视图-Mother.chtml

@model Birth.Models.Mother
@{
ViewData["Title"] = "Mother";
}
<h2>Mother</h2>
@Html.DisplayNameFor(model => model.MomId)
@Html.DisplayFor(model => model.MomId)
<br />
@Html.DisplayNameFor(model => model.MomFirst)
@Html.DisplayFor(model => model.MomFirst)
<br />
@Html.DisplayNameFor(model => model.MomLast)
@Html.DisplayFor(model => model.MomLast)
<br /><br /><br />
@Html.RenderPartialAsync("Birth/_MotherAddress",  Model.Physical)
@*@Html.RenderPartialAsync("Birth/_MotherAddress", Model.Mailing)*@

部分视图-MotherAddress.cshtml

@model Birth.Models.MotherAddress
<div class="container">
<div class="row">
<div class="col-sm-3">
@Html.DisplayNameFor(model => model.pType)
</div>
<div class="col-sm-3">
@Html.DisplayNameFor(model => model.Street)
</div>
<div class="col-sm-4">
@Html.DisplayNameFor(model => model.PreDir)
</div>
<div class="col-sm-2">&nbsp;</div>
</div>
<div class="row">
<div class="col-sm-3">
@Html.TextBoxFor(model => model.pType, new { Style = "width:100%" })
</div>
<div class="col-sm-3">
@Html.TextBoxFor(model => model.Street, new { Style = "width:100%" })
</div>
<div class="col-sm-4">
@Html.TextBoxFor(model => model.PreDir, new { Style = "width:80%" })
</div>
<div class="col-sm-2">&nbsp;</div>
</div>
</div>

模型。数据-GetBirthData.cs

namespace Birth.Models.Data
{
public class GetBirthData
{
private IBirthRepository repo;
pivate BirthDbContext _ctx;
public GetBirthData(IBirthRepository repository, BirthDbContext context)
{
repo = repository;
_ctx = context;
}
public async Task<Mother> GetMotherData(int MomId)
{
Mother list = new Mother();
try
{
string query = "exec MAINTGetMotherAddresses {0}";
list = await repo.Mothers
.FromSql(query, MomId)
.AsNoTracking()
.FirstOrDefaultAsync();
}
catch (Exception ex)
{
var msg = ex.Message;
}
return list;
}
}
}

DbContext-BirthDbContext.cs

namespace Birth.Models.Data
{
public class BirthDbContext : DbContext
{
public BirthDbContext(DbContextOptions<BirthDbContext> options)
:base(options) {
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Mother>(b =>
{
b.HasKey(e => e.MomId);
b.Property(e => e.MomId).ValueGeneratedNever();
//b.OwnsOne<MotherAddress>(e => e.Physical);
b.HasOne<MotherAddress>(e => e.Physical)
.WithOne(e => e.Mother)
.HasForeignKey<MotherAddress>(e => e.MomId);
b.ToTable("Table1");
});
}
public DbSet<Mother> Mothers { get; set; }
public DbSet<MotherAddress> MotherAddresses { get; set; }
}
}

SQLServer存储过程-MAINTGetMotherAddresses

ALTER PROCEDURE MAINTGetMotherAddresses
@MotherId NUMERIC(18,0)
AS
BEGIN
SELECT
CAST(md.MOTHER_DETAIL_ID AS INT) AS MomId,
md.FIRST_NAME AS MomFirst,
md.LAST_NAME AS MomLast,
'Physical' AS pType,
maP.STREET_ADDRESS AS Street,--PhysicalStreet,
maP.PRE_DIRECTION AS PreDir --PhysicalPreDir--,
--'Mailing' AS MailingType,
--maM.STREET_ADDRESS AS MailingStreet,
--maM.PRE_DIRECTION AS MailingPreDir

,CAST(@MotherId AS INT) AS PhysicalMomId
--,CAST(@MotherId AS INT) AS MailingMomId
--,CAST(@MotherId AS INT) AS MotherAddressMomId
FROM dbo.MOTHER_DETAIL md
JOIN dbo.MOTHER_ADDRESS maP
ON maP.MOTHER_DETAIL_ID = md.MOTHER_DETAIL_ID
JOIN dbo.MOTHER_ADDRESS maM
ON maM.MOTHER_DETAIL_ID = md.MOTHER_DETAIL_ID
WHERE md.MOTHER_DETAIL_ID = @MotherId
AND maP.ADDRESS_TYPE in (133, 176)
AND maM.ADDRESS_TYPE IN (132, 175)

END

Mother_Detail和Mother_Address表的字段比我在这里列出的要多得多，所以我不打算显示完整的表结构。如果您创建这些表并将地址添加到Mother_Address表中，即Address_Type为176，那么您将能够看到我当前尝试使用的结果集。我希望第二次加入地址，如存储过程中所示，并在一个结果集中返回邮件和物理地址，并让系统用这两个地址适当地填充MotherAddress模型。

请帮忙！这非常令人沮丧。

Randy

Answer 1

1. 您的存储过程将获得1个mom的所有交叉连接数据，以下是正确的sql:

   SELECT  m.Whatever, physical.Whatever, mailing.Whatever
   FROM    Mother m
   CROSS APPLY
   (
   SELECT  TOP 1 a.Name, a.Whatever
   FROM    Address a
   WHERE   a.momId= m.Id and a.pType in (1,2,3)
   ) physical
   CROSS APPLY
   (
   SELECT  TOP 1 b.Name, b.Whatever
   FROM    Address b
   WHERE   b.momId= m.Id and b.pType in (4,5,6)
   ) mailing
   

2. SP无法将结果映射到模型的原因是：SP返回的视图在您的上下文中不存在。当前EF&Core并不能很好地执行QuerySP，而只能用于插入、更新和删除。

   //call extension like this:
   _context.QueryStoredProcedureAsync<YourViewModel>("YourProcedureName", ("a", 1), ("b", 2), ("c", 3));
   //here is extension
   public static class ContextExtensions
   {
   public static async Task<List<T>> QueryStoredProcedureAsync<T>(
   this DbContext context, 
   string storedProcName, 
   params (string Key, object Value)[] args)//<==c# 7 new feature
   {
   using (var command = context.Database.GetDbConnection().CreateCommand())
   {
   command.CommandText = storedProcName;
   command.CommandType = System.Data.CommandType.StoredProcedure;
   foreach (var arg in args)
   {
   var param = command.CreateParameter();
   param.ParameterName = arg.Key;
   param.Value = arg.Value;
   command.Parameters.Add(param);
   }
   using (var reader = await command.ExecuteReaderAsync())
   {
   return reader.MapToList<T>();
   }
   }
   }
   private static List<T> MapToList<T>(this DbDataReader dr)
   {
   var result = new List<T>();
   var props = typeof(T).GetRuntimeProperties();
   var colMapping = dr.GetColumnSchema()
   .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
   .ToDictionary(key => key.ColumnName.ToLower());
   if (dr.HasRows)
   {
   while (dr.Read())
   {
   T obj = Activator.CreateInstance<T>();
   foreach (var prop in props)
   {
   var val =
   dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
   prop.SetValue(obj, val == DBNull.Value ? null : val);
   }
   result.Add(obj);
   }
   }
   return result;
   }
   }
   

3. 您的SP只是一个查询，我为您提供了两个替换选项：

   • 使用编译查询：https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/compiled-queries-linq-to-entities

   • 使用我的简单建议删除SP，SP不是为查询数据设计的，而是为过程数据设计的。

     public partial class Mother
     {
     public Mother()
     {
     MotherAddresses = new HashSet<MotherAddress>();
     }
     [Key, Column("ID")]
     public int Id { get; set; }
     [StringLength(50)]
     public string Name { get; set; }
     [InverseProperty("Mother")]
     public ICollection<MotherAddress> MotherAddresses { get; set; }
     }
     public partial class MotherAddress
     {
     [Key, Column(Order = 0)]
     public int MotherId { get; set; }
     [Key, Column(Order = 1)]
     public int AddressType { get; set; }
     [StringLength(50)]
     public string Address { get; set; }
     [ForeignKey("MotherId")]
     [InverseProperty("MotherAddress")]
     public Mother Mother { get; set; }
     }
     public enum AddressType
     {
     Physical,
     Mailing,
     }
     
     public static class MotherExtension
     {
     public static MotherAddress MailingAddress(this Mother mom)
     {
     return mom.Address(AddressType.Mailing);
     }
     public static MotherAddress PhysicalAddress(this Mother mom)
     {
     return mom.Address(AddressType.Physical);
     }
     public static MotherAddress Address(this Mother mom, AddressType addressType)
     {
     return mom.MotherAddresses.FirstOrDefault(x => x.AddressType == addressType);
     }
     }
     // here is in your DbContext
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
     modelBuilder.Entity<MotherAddress>(entity =>
     {
     entity.HasKey(e => new { e.MotherId, e.AddressType });
     entity.HasOne(d => d.Mother)
     .WithMany(p => p.MotherAddress)
     .HasForeignKey(d => d.MotherId)
     .OnDelete(DeleteBehavior.ClientSetNull)
     .HasConstraintName("FK_MotherAddress_Mother");
     });
     
     }
     

     然后在你的html:

     @Html.RenderPartialAsync("Birth/_MotherAddress",  Model.PhysicalAddress())
     @*@Html.RenderPartialAsync("Birth/_MotherAddress", Model.WhateverAddress())*@