调用 Entity Framework 7 中的存储过程时映射返回类型

huangapple go评论67阅读模式
英文:

Mapping return types when calling Stored Procedures from Entity Framework 7

问题

我已经使用 EF 7 映射了基本表,目前运行良好。

现在我想调用一个存储过程并返回一个定制模型的列表。这个模型包括从多个表中提取的数据。

在我的上下文中,我映射了我的模型 'ReportSummary':

public class MyDbContext : DbContext
{
    :
    public DbSet<ReportSummary> ReportSummary { get; set; }
    :
}

我这样调用存储过程:

public async Task<IEnumerable<ReportSummary>> GetUserReportSummaries(Guid userId, bool showDeleted)
{
    return await _context
        .ReportSummary
        .FromSql($"EXECUTE dbo.GetReportSummaries {userId}, {showDeleted}")
        .ToListAsync();
}

以这种方式映射我的模型可以正常工作,并且调用返回了预期的结果。

但我的问题是,通过映射到一个 DbSet,当我生成更新数据库的 SQL 时,现在为我的 'ReportSummary' 模型创建了一个表 - 这不是我想要的。

我是不是映射错了?如何使用我的模型而不创建表?
如果我不添加 DBSet,那么对存储过程的调用就无法工作。

Entity Framework 能否使用不是表的模型?肯定有一种方法可以从存储过程中返回非平凡类型。

我想能够使用模型作为存储过程的返回类型,但不必为此模型创建一个表。

我可以编辑迁移代码以删除为 'ReportSummary' 生成的表定义,但这似乎有点取巧...

英文:

I have basic tables mapped using EF 7 and this is working well.

Now I want to call a stored procedure and return a list of a customised model. This model consists of data extracted from several tables.

In my context I map my model 'ReportSummary':

public class MyDbContext : DbContext
{
	:
	public DbSet&lt;ReportSummary&gt; ReportSummary { get; set; }
	:
}

I call the stored procedure like so:

 	public async Task&lt;IEnumerable&lt;ReportSummary&gt;&gt; GetUserReportSummaries(Guid userId, bool showDeleted)
	{
	    return await _context
	    	.ReportSummary
	        .FromSql($&quot;EXECUTE dbo.GetReportSummaries {userId}, {showDeleted}&quot;)
	        .ToListAsync();
	}

Mapping my model this way works fine and the call returns expected results.

But my issue is that by mapping to a DbSet, when I generate the SQL to update my database, I now have a table created for my 'ReportSummary' model - which I don't want.

Am I mapping this correctly? How can I use my model and not create a table?
If I don't add the DBSet, then the call to the stored procedure does not work.

Can entity framework use models that are not tables? There must be a way to return non trivial types from a stored procedure.

I want to be able to use a model for the stored procedure return type, but not have to create a table for this model.

I can edit the migration code to delete the generated table definition for 'ReportSummary', but this seems a bit hacky...

答案1

得分: 2

感谢 ErikEJ

如建议所示,ExcludeFromMigrations 将会做到这一点。

在数据库上下文对象中,我现在有:

    :

    public DbSet&lt;ReportSummary&gt; ReportSummary { get; set; }

    :

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

        modelBuilder.Entity&lt;ReportSummary&gt;().ToTable(nameof(ReportSummary), t =&gt; t.ExcludeFromMigrations());
    }

现在迁移和生成的 SQL 将不再包括对 'ReportSummary' 表的定义,
但 DBSet 仍可用于在 FromSql() 调用中使用。

另一种选择是使用 Dapper 进行存储过程调用。这可以与 Entity Framework 一起使用 alongside Entity Framework

代码稍微简单,因为您不需要记得从迁移中排除模型。
对于 Dapper,调用存储过程所需的唯一代码将是:

    using (var connection = new SqlConnection( dbConnectionString ))
    {
        var sql = &quot;EXECUTE dbo.GetReportSummaries @userId, @showDeleted&quot;;
        return await connection.QueryAsync&lt;ReportSummary&gt;(sql, new { userId, showDeleted });
    }

缺点是您需要包含另一个包,但如果您有大量存储过程调用,这可能是值得的。

英文:

Thank you ErikEJ

As suggested ExcludeFromMigrations will do this.

In the db context object I now have:

    :

    public DbSet&lt;ReportSummary&gt; ReportSummary { get; set; }

    :

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

        modelBuilder.Entity&lt;ReportSummary&gt;().ToTable(nameof(ReportSummary), t =&gt; t.ExcludeFromMigrations());
    }

The migration and generated SQL will now not include a definition for a 'ReportSummary' table,
but the DBSet is still available to be used in a FromSql() call.

Another alternative is to use Dapper for stored procedure calls. This can be used alongside Entity Framework

The code is slightly simpler as you don't need to remember to exclude the model from the migration.
For Dapper the only code needed to call the stored procedure would be:

    using (var connection = new SqlConnection( dbConnectionString ))
    {
        var sql = &quot;EXECUTE dbo.GetReportSummaries @userId, @showDeleted&quot;;
        return await connection.QueryAsync&lt;ReportSummary&gt;(sql, new { userId, showDeleted });
    }

The disadvantage is you need to include another package, but this may be worthwhile if you have lots of stored proc calls.

答案2

得分: 0

在你的代码库中创建你自定义的模型(例如,GetCustomersSP),并在你的上下文中按照以下方式配置该类 =>
modelBuilder.Entity&lt;GetCustomersSP&gt;().HasNoKey()

在GetCustomersSP类内部,你需要拥有存储过程的返回列。

英文:

Create your custom model in your codebase(e.g. GetCustomersSP) and inside you context
configure that class in the following way =>
modelBuilder.Entity&lt;GetCustomersSP&gt;().HasNoKey()

Inside the GetCustomersSP class you will need to have the return columns from the Stored Procedure.

huangapple
  • 本文由 发表于 2023年6月22日 14:05:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/76528982.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定