如何在linqc#中使用实体框架代码优先的方法调用Sql用户定义函数

本文关键字：方法 调用 Sql 函数 定义 用户 代码 linqc# 框架 实体 | 更新日期: 2023-09-27 18:16:11

我使用实体框架6.0.0.0。我有一个sql函数返回3列2个guid和一个varchar (userid,ticketid,number)。而是如何在不使用ADO.net组件的情况下调用这个自定义sql函数。我使用实体框架工作(代码优先方法)

如何在linqc#中使用实体框架代码优先的方法调用Sql用户定义函数

我有相同的:一个从DbContext派生的类，具有几个DbSet属性和一个存储过程。我增加了三个功能:

  • 检查存储过程是否存在
  • 创建或更改存储过程(在迁移时需要)
  • 调用存储过程。
你的问题是关于最后一部分的。您可能还需要其他的，所以这里有三个函数 
public class InvoiceContext : DbContext
{
    // DbSet properties left out
    #region stored procedures
    private const string StoredProcedureNameProcessUsageCosts = "processusagecosts";
    public void CallStoredProcedureProcessUsageCosts(UsageCosts usageCosts)
    {
        object[] functionParameters = new object[]
        {
            new SqlParameter(@"ReportDate", usageCosts.ReportPeriod),
            new SqlParameter(@"CustomerContractId", usageCosts.CustomerContractId),
            new SqlParameter(@"CallType", usageCosts.CallType),
            new SqlParameter(@"TariffGroup", usageCosts.TariffGroup),
            new SqlParameter(@"VatValue", usageCosts.VatValue),
            new SqlParameter(@"PurchaseCosts", usageCosts.PurchaseCosts),
            new SqlParameter(@"RetailCosts", usageCosts.RetailCosts),
        };
        const string sqlCommand = @"Exec " + StoredProcedureNameProcessUsageCosts
            + " @ReportDate, @CustomerContractId, @CallType, @TariffGroup, @VatValue,"
            + " @PurchaseCosts, @RetailCosts";
        this.Database.ExecuteSqlCommand(sqlCommand, functionParameters);
    }
    public bool StoredProcedureProcessUsageCostsExists()
    {
        return this.Exists(StoredProcedureNameProcessUsageCosts);
    }
    public void CreateProcedureProcessUsageCosts(bool forceRecreate)
    {
        bool storedProcedureExists = this.Exists (StoredProcedureNameUpdateUsageCosts);
        // only create (or update) if not exists or if forceRecreate:
        if (!storedProcedureExists || forceRecreate)
        {   // create or alter:
            var x = new StringBuilder();
            // ALTER or CREATE?
            if (!storedProcedureExists)
            {
                x.Append(@"CREATE");
            }
            else
            {
                x.Append(@"ALTER");
            }
            // procedure name:
            x.Append(@" procedure ");
            x.AppendLine(StoredProcedureNameProcessUsageCosts);
            // parameters:
            x.AppendLine(@"@ReportPeriod int,");
            x.AppendLine(@"@CustomerContractId bigint,");
            x.AppendLine(@"@CallType nvarChar(80),");
            x.AppendLine(@"@TariffGroup nvarChar(80),");
            x.AppendLine(@"@VatValue decimal(18, 2),");
            x.AppendLine(@"@PurchaseCosts decimal(18, 2),");
            x.AppendLine(@"@RetailCosts decimal(18, 2)");
            // code
            x.AppendLine(@"as");
            x.AppendLine(@"begin");
            x.AppendLine(@"Merge [usagecosts]");
            x.AppendLine(@"Using (Select @ReportPeriod as reportperiod,");
            x.AppendLine(@"              @CustomerContractId as customercontractId,");
            x.AppendLine(@"              @CallType as calltype,");
            x.AppendLine(@"              @TariffGroup as tariffgroup,");
            x.AppendLine(@"              @VatValue as vatvalue)");
            x.AppendLine(@"              As tmp ");
            x.AppendLine(@"On ([usagecosts].[reportperiod] = tmp.reportperiod");
            x.AppendLine(@"AND [usagecosts].[customercontractId] = tmp.customercontractid");
            x.AppendLine(@"AND [usagecosts].[calltype] = tmp.calltype");
            x.AppendLine(@"AND [usagecosts].[tariffgroup] = tmp.tariffgroup");
            x.AppendLine(@"AND [usagecosts].[vatvalue] = tmp.Vatvalue)");
            x.AppendLine(@"When Matched Then ");
            x.AppendLine(@"    Update Set [usagecosts].[purchasecosts] = [usagecosts].[purchasecosts] + @purchasecosts,");
            x.AppendLine(@"               [usagecosts].[retailcosts] = [usagecosts].[retailcosts] + @retailcosts");
            x.AppendLine(@"When Not Matched Then");
            x.AppendLine(@"    Insert (ReportPeriod, CustomerContractId, calltype, tariffgroup, vatvalue, purchasecosts, retailcosts)");
            x.AppendLine(@"    Values (@reportPeriod, @CustomerContractId, @CallType, @TariffGroup, @VatValue, @PurchaseCosts, @RetailCosts);");
            x.AppendLine(@"end");
            this.Database.ExecuteSqlCommand(x.ToString());
        }
        // else: procedure exists and no forced recreate, nothing to do
    }
    #endregion stored procedures
}

用法:

调用存储过程

using (var dbContext - new InvoiceContext(...))
{
    UsageCosts params = new UsageCosts()
    {
        ...
    };
    dbContext.CallStoredProcedureUsageCost(params);
}
创建存储过程

在InitializeDataBase中执行此操作，例如在数据库的初始播种期间，或在您的某个迁移函数中

public override void InitializeDatabase(InvoiceContext context)
{
     // if not exists, create the stored procedure
     context.CreateProcedureProcessUsageCosts(false);
}
相关文章：