将通用子代码从存储过程结果映射到通用父项 [英] Map generic child to generic parent from stored procedure results

问题描述

我使用与这个帖子，使用Entity Framework将多个存储过程结果集映射到父和子实体。然而，我所看到的这种方法的每个实现都将后续结果集的结果（第一个）直接分配给父实体的属性;我想以一种通用的方式来实现这一点。

到目前为止，我已经能够构建一个成功地将结果映射成一对多关系的包装器。在这种情况下，我不需要关心执行外键关系，因为存储过程会处理这个问题，但是在选择很多父实体时，我需要以某种方式执行关系。

如何利用实体框架中的外键关系（使用反思，推测）强制映射？

这是我所拥有的：

实体

  class FooEntity 
 {
 [Key] 
 int Id {get; set;} 
 
 ICollection< BarEntity>酒吧{get;组; 
} 
 
 class BarEntity 
 {
 [Key] 
 int Id {get; set;} 
 
 int FooId {get;组; } 
 
 [ForeignKey（FooId）] 
 virtual FooEntity Foo {get;组; 
} 
  

通用存储过程翻译器：

  public class StoredProcedureTranslator< TDbContext> ：IDisposable其中TDbContext：DbContext，new（）
 {
 private DbCommand command; 
 
 public StoredProcedureTranslator（）
 {
 this.Context = new TDbContext（）; 
} 
 
 public DbContext上下文{get;组; } 
 
 public DbDataReader Reader {get;组; } 
 
 public StoredProcedureResult< T，TDbContext>翻译< T>（string procedureName，SqlParameter []参数）其中T：class 
 {
 this.command = this.Context.Database.Connection.CreateCommand（）; 
 this.command.CommandText = procedureName; 
 if（parameters！= null）
 {
 this.command.Parameters.AddRange（parameters）; 
} 
 
 this.command.CommandType = CommandType.StoredProcedure; 
 
 this.Context.Database.Connection.Open（）; 
 
 this.Reader = this.command.ExecuteReader（）; 
 
 ObjectResult< T> results =（（IObjectContextAdapter）this.Context）.ObjectContext.Translate< T>（this.Reader）; 
返回新的StoredProcedureResult< T，TDbContext>（results.ToList（），this）; 
} 
 
 public void Dispose（）
 {
 this.Context.Dispose（）; 
} 
} 
  

通用存储过程结果：

  public class StoredProcedureResult< T，TDbContext> ：IEnumerable< T> 
其中T：class其中TDbContext：DbContext，new（）
 {
 private readonly IEnumerable< T>结果; 
 
 private readonly StoredProcedureTranslator< TDbContext>翻译; 
 
 public StoredProcedureResult（IEnumerable< T>结果，StoredProcedureTranslator< TDbContext>翻译器）
 {
 this.results = results; 
 this.translator = translator; 
} 
 
 public StoredProcedureResult< T，TDbContext>包含< TChild>（表达式< Func< T，IEnumerable< TChild>>>>成员）其中TChild：class 
 {
 T result = this.results.FirstOrDefault（）; 
 
 if（result == null）
 {
 return null; 
} 
 
 if（！this.translator.Reader.NextResult（））
 {
返回新的StoredProcedureResult< T，TDbContext>（new List< T& result}，this.translator）; 
} 
 
 // TODO：用fks 
做一些东西ObjectResult< TChild> childResults =（（IObjectContextAdapter）this.translator.Context）.ObjectContext.Translate< TChild>（this.translator.Reader）; 
 var prop = member.Body as MemberExpression; 
 if（prop == null）
 {
 throw new Exception（blah blah）; 
} 
 
 var property = prop.Member as PropertyInfo; 
 if（property == null）
 {
 throw new Exception（blah blah）; 
} 
 
 property.SetValue（result，childResults.ToList（），null）; 
 
 
返回新的StoredProcedureResult< T，TDbContext>（new List< T> {result}，this.translator）; 
} 
 
 #region IEnumerable Impl 
 
 public IEnumerator GetEnumerator（）
 {
 return this.results.GetEnumerator（）; 
} 
 
 IEnumerator< T> IEnumerable< T> .GetEnumerator（）
 {
 return（IEnumerator< T>）this.GetEnumerator（）; 
} 
 
 #endregion 
} 
  

使用方法

  using（var spt = new StoredProcedureTranslator< DbContext>（））
 {
 FooEntity foo = spt .Translate< FooEntity>（[dbo]。[foo_get]，null）.Include（f => f.Bars）.FirstOrDefault（）; 
} 
  

解决方案

如果这是打算仅用于实体类型，您可以让EF上下文跟踪基础设施和关系修复为您工作（与EF实体查询相似匹配）。

诀窍是使用以下翻译方法重载

  public virtual ObjectResult< TEntity>翻译< TEntity>（DbDataReader reader，string entitySetName，MergeOption mergeOption）
  

并传递

这是一个小助手实用工具类（加上一些其他有用的方法）：

  public static class EntityUtils 
 {
 public static string GetEntitySetName< T>（此IObjectContextAdapter dbContext）其中T：class 
 {
 return dbContext.ObjectContext.CreateObjectSet< T>（）。EntitySet.Name; 
} 
 
 public static ObjectResult< T> ReadSingleResult< T>（此IObjectContextAdapter dbContext，DbDataReader dbReader）
其中T：class 
 {
 return dbContext.ObjectContext.Translate< T>（dbReader，dbContext.GetEntitySetName& MergeOption.AppendOnly）; 
} 
 
 public static void Load< T>（此ObjectResult< T>源）其中T：class 
 {
 //通过迭代枚举枚举枚举
使用（var en = source.GetEnumerator（））
 while（en.MoveNext（））{} 
} 
} 
  / pre> 
 
 
可以修改 StoredProcedureTranslator 和 StoredProcedureResult 如下（我发现设计有点有缺陷，但这不在此问题的范围之内）。
 
 
 
结果：
  public class StoredProcedureResult< T> ：IEnumerable< T> 
其中T：class 
 {
 private readonly DbContext context; 
私有readonly DbDataReader阅读器; 
 private readonly IEnumerable< T>结果; 
 
 public StoredProcedureResult（DbContext context，DbDataReader reader）
 {
 this.context = context; 
 this.reader = reader; 
 this.results = this.context.ReadSingleResult< T>（this.reader）.ToList（）; 
} 
 
 public StoredProcedureResult< T>包含< TChild>（）其中TChild：class 
 {
 if（this.reader.NextResult（））
 this.context.ReadSingleResult< TChild>（this.reader）.Load（） ; 
返回这个; 
} 
 
 public IEnumerator< T> GetEnumerator（）
 {
 return this.results.GetEnumerator（）; 
} 
 
 IEnumerator IEnumerable.GetEnumerator（）
 {
 return this.GetEnumerator（）; 
} 
} 
  
请注意，它不需要 TDbContext 通用参数，它所做的就是保持根结果集，同时提供流畅的界面来读取下一个结果。结果只是翻译和消费，不需要存储，因为在消费部分EF将绑定到已加载的相关实体。
 
 
 
内部的翻译方法 StoredProcedureTranslator  class：
  public StoredProcedureResult< T>翻译< T>（string procedureName，SqlParameter []参数）其中T：class 
 {
 this.command = this.Context.Database.Connection.CreateCommand（）; 
 this.command.CommandText = procedureName; 
 if（parameters！= null）
 this.command.Parameters.AddRange（parameters）; 
 this.command.CommandType = CommandType.StoredProcedure; 
 this.Context.Database.Connection.Open（）; 
 this.Reader = this.command.ExecuteReader（）; 
返回新的StoredProcedureResult< T>（this.Context，this.Reader）; 
} 
  
用法：
  using（var spt = new StoredProcedureTranslator< DbContext>（））
 {
 var foo = spt.Translate&FooEntity>（[dbo]。[foo_get] ，null）
 .Include< BarEntity>（）
 .FirstOrDefault（）; 
} 
  
 
I use a similar approach to the one described in this post to map multiple stored procedure result sets to a parent and child entity using Entity Framework. However, every implementation of this approach that I've seen assigns the results of subsequent result sets (after the first) to properties of the parent entity directly; I'd like to accomplish this in a generic fashion.

So far I've been able to build a wrapper that successfully maps results in a one-to-many relationship. I don't need to care about enforcing the foreign key relationship in this case because the stored procedure handles that, but when selecting many parent entities, I need to somehow enforce the relationship.

How can I leverage the foreign key relationship in Entity Framework (using reflection, presumably) to enforce the mapping?

Here's what I have:

Entities
class FooEntity
{
     [Key]
     int Id { get; set;}

     ICollection<BarEntity> Bars { get; set; }
}

class BarEntity
{
     [Key]
     int Id { get; set;}

     int FooId { get; set; }

     [ForeignKey("FooId")]
     virtual FooEntity Foo { get; set; }
}
Generic stored procedure translator:
public class StoredProcedureTranslator<TDbContext> : IDisposable where TDbContext : DbContext, new()
{
    private DbCommand command;

    public StoredProcedureTranslator()
    {
        this.Context = new TDbContext();
    }

    public DbContext Context { get; set; }

    public DbDataReader Reader { get; set; }

    public StoredProcedureResult<T, TDbContext> Translate<T>(string procedureName, SqlParameter[] parameters) where T : class
    {
        this.command = this.Context.Database.Connection.CreateCommand();
        this.command.CommandText = procedureName;
        if (parameters != null)
        {
             this.command.Parameters.AddRange(parameters);
        }

        this.command.CommandType = CommandType.StoredProcedure;

        this.Context.Database.Connection.Open();

        this.Reader = this.command.ExecuteReader();

        ObjectResult<T> results = ((IObjectContextAdapter)this.Context).ObjectContext.Translate<T>(this.Reader);
        return new StoredProcedureResult<T, TDbContext>(results.ToList(), this);
    }

    public void Dispose()
    {
        this.Context.Dispose();
    }
}
Generic stored procedure result:
public class StoredProcedureResult<T, TDbContext> : IEnumerable<T>
    where T : class where TDbContext : DbContext, new()
{
    private readonly IEnumerable<T> results;

    private readonly StoredProcedureTranslator<TDbContext> translator;

    public StoredProcedureResult(IEnumerable<T> results, StoredProcedureTranslator<TDbContext> translator)
    {
        this.results = results;
        this.translator = translator;
    }

    public StoredProcedureResult<T, TDbContext> Include<TChild>(Expression<Func<T, IEnumerable<TChild>>> member) where TChild : class
    {
        T result = this.results.FirstOrDefault();

        if (result == null)
        {
            return null;
        }

        if (!this.translator.Reader.NextResult())
        {
            return new StoredProcedureResult<T, TDbContext>(new List<T> { result }, this.translator);
        }

        // TODO: do some stuff with the fks
        ObjectResult<TChild> childResults = ((IObjectContextAdapter)this.translator.Context).ObjectContext.Translate<TChild>(this.translator.Reader);
        var prop = member.Body as MemberExpression;
        if (prop == null)
        {
            throw new Exception("blah blah");
        }

        var property = prop.Member as PropertyInfo;
        if (property == null)
        {
            throw new Exception("blah blah");
        }

        property.SetValue(result, childResults.ToList(), null);


        return new StoredProcedureResult<T, TDbContext>(new List<T> { result }, this.translator);
    }

    #region IEnumerable Impl

    public IEnumerator GetEnumerator()
    {
        return this.results.GetEnumerator();
    }

    IEnumerator<T> IEnumerable<T>.GetEnumerator()
    {
        return (IEnumerator<T>)this.GetEnumerator();
    }

    #endregion
}
Usage
using (var spt = new StoredProcedureTranslator<DbContext>())
{
     FooEntity foo = spt.Translate<FooEntity>("[dbo].[foo_get]", null).Include(f => f.Bars).FirstOrDefault();
}

解决方案
If this is intended to be used for entity types only, you can let EF context tracking infrastructure and relationship fix up do the work for you (pretty match like the EF materializes entity queries).

The trick is to use the following Translate method overload
public virtual ObjectResult<TEntity> Translate<TEntity>(DbDataReader reader, string entitySetName, MergeOption mergeOption)
and pass the entitySetName of the entity type.

Here is small helper utility class which does that (plus some other useful methods):
public static class EntityUtils
{
    public static string GetEntitySetName<T>(this IObjectContextAdapter dbContext) where T : class
    {
        return dbContext.ObjectContext.CreateObjectSet<T>().EntitySet.Name;
    }

    public static ObjectResult<T> ReadSingleResult<T>(this IObjectContextAdapter dbContext, DbDataReader dbReader)
        where T : class
    {
        return dbContext.ObjectContext.Translate<T>(dbReader, dbContext.GetEntitySetName<T>(), MergeOption.AppendOnly);
    }

    public static void Load<T>(this ObjectResult<T> source) where T : class
    {
        // Consume the enumerable by iterating it
        using (var en = source.GetEnumerator())
            while (en.MoveNext()) { }
    }
}
The StoredProcedureTranslator and StoredProcedureResult can be modified as follows (I find the design a bit flawed, but that's outside the scope of the question).

The result:
public class StoredProcedureResult<T> : IEnumerable<T>
    where T : class
{
    private readonly DbContext context;
    private readonly DbDataReader reader;
    private readonly IEnumerable<T> results;

    public StoredProcedureResult(DbContext context, DbDataReader reader)
    {
        this.context = context;
        this.reader = reader;
        this.results = this.context.ReadSingleResult<T>(this.reader).ToList();
    }

    public StoredProcedureResult<T> Include<TChild>() where TChild : class
    {
        if (this.reader.NextResult())
            this.context.ReadSingleResult<TChild>(this.reader).Load();
        return this;
    }

    public IEnumerator<T> GetEnumerator()
    {
        return this.results.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }
}
Note that it doesn't need TDbContext generic parameter anymore, and all it does is to hold the root result set while providing fluent interface for reading the next results. The results are just translated and consumed, there is no need to be stored because during the consuming part EF will bind them to the already loaded related entities.

The translate method inside the StoredProcedureTranslator class:
public StoredProcedureResult<T> Translate<T>(string procedureName, SqlParameter[] parameters) where T : class
{
    this.command = this.Context.Database.Connection.CreateCommand();
    this.command.CommandText = procedureName;
    if (parameters != null)
        this.command.Parameters.AddRange(parameters);
    this.command.CommandType = CommandType.StoredProcedure;   
    this.Context.Database.Connection.Open();    
    this.Reader = this.command.ExecuteReader();    
    return new StoredProcedureResult<T>(this.Context, this.Reader);
}
Usage:
using (var spt = new StoredProcedureTranslator<DbContext>())
{
     var foo = spt.Translate<FooEntity>("[dbo].[foo_get]", null)
         .Include<BarEntity>()
         .FirstOrDefault();
}


                        
这篇关于将通用子代码从存储过程结果映射到通用父项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！