Geek Answers Handbook

EF6: create a stored procedure. Use Fluent API or DBMigrations?

First, I use EF6 code to create my database. I understand the syntax, DbContext and modelbuilder. I use LINQ for a few comprehensive queries, and everything works fine.

But now I need to do something that cannot be done in a single query using linq. I need to execute a Merge statement using a stored procedure.

I saw several questions about how to create a stored procedure, for example: First, create stored procedures using Entity Framework code?

Most answers talk about creating a derived class for DbMigrations and overriding the Up () function. I understand what I have to write in the Up function to make sure the stored procedure is created.

But what should I do to do this while creating the database for this Up function?

Do I have to do something in DbContext.OnModelCreating?

I don't think I should create an instance of a subclass of DbMigrations and call Up ().

The link mentioned above says "Open Package Manager Manager." What is it? Or do you really use this method when upgrading from an older version to a newer one?

+8
source share
1 answer

, , . , . . , .

, :

  • , , IDataBaseInitializer. , , DropCreateDatabaseIfModelChanges . Seed , context.Database.ExecuteSqlCommand(...).
  • Entity Framework .

. , , Seed . , , , .

DbMigration -, , , , .

. Hello World! .

. , , , , , , . , . , linq IQueryable . linq, , SaveChanges (1), - (2) . .

IDatabaseInitializer

DbContext DbSet , .

:

public class UsageCosts
{
    public int Id {get; set; }
    public DateTime InvoicePeriod { get; set; }
    public long CustomerContractId { get; set; }
    public string TypeA { get; set; }
    public string TypeB { get; set; }
    public decimal VatValue { get; set; }

    // the value to invoice
    public decimal PurchaseCosts { get; set; }
    public decimal RetailCosts { get; set; }
}

public class DemoContext : DbContext
{
    public DemoContext(string nameOrConnectionString) : base(nameOrConnectionString) {}

    public DbSet<UsageCosts> UsageCosts { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        // add entity framework fluent api statements here          
    }
}

, . Seed, .

internal class DataBaseInitializer : DropCreateDatabaseIfModelChanges<DemoContext>
{
    protected override void Seed(DemoContext context)
    {
        base.Seed(context);

        // create stored procedures here
        this.CreateStoredProcedureHelloWorld(context)
        this.CreateStoredProcedureUpdateUsageCosts(context)
    }

, , (Hello World!)

    private void CreateStoredProcedureHelloWorld(DemoContext context)
    {
        context.Database.ExecuteSqlCommand("create procedure HelloWorld as begin Select 'Hello World' end;");
    }

:

    private void CreateStoredProcedureUpdateUsageCosts(DemoContext context)
    {
        var x = new StringBuilder();
        x.AppendLine(@"create procedure updateusagecosts");
        x.AppendLine(@"@InvoicePeriod datetime,");
        x.AppendLine(@"@CustomerContractId bigint,");
        x.AppendLine(@"@TypeA nvarChar(80),");
        x.AppendLine(@"@TypeB nvarChar(80),");
        x.AppendLine(@"@VatValue decimal(18, 2),");
        x.AppendLine(@"@PurchaseCosts decimal(18, 2),");
        x.AppendLine(@"@RetailCosts decimal(18, 2)");
        x.AppendLine(@"as");
        x.AppendLine(@"begin");
        x.AppendLine(@"Merge [usagecosts]");
        x.AppendLine(@"Using (Select @InvoicePeriod as invoicePeriod,");
        x.AppendLine(@"              @CustomerContractId as customercontractId,");
        x.AppendLine(@"              @TypeA as typeA,");
        x.AppendLine(@"              @TypeB as typeB,");
        x.AppendLine(@"              @VatValue as vatvalue)");
        x.AppendLine(@"              As tmp ");
        x.AppendLine(@"On ([usagecosts].[invoiceperiod] = tmp.invoiceperiod");
        x.AppendLine(@"AND [usagecosts].[customercontractId] = tmp.customercontractid");
        x.AppendLine(@"AND [usagecosts].[typeA] = tmp.typeA");
        x.AppendLine(@"AND [usagecosts].[typeB] = tmp.typeB");
        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 (InvoicePeriod, CustomerContractId, typea, typeb, vatvalue, purchasecosts, retailcosts)");
        x.AppendLine(@"    Values (@invoiceperiod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts);");
        x.AppendLine(@"end");
        context.Database.ExecuteSqlCommand(x.ToString());
    }
}

fooobar.com/questions/52770/...

StringBuilder - StackOverflow, , , .

DatabaseInitializer.Seed(...). SQL. . .

DbMigration

:

, Visual Studio DbManager, Up(). , .

Up() DbMigration.CreateStoredProcedure. , Entity ( -) , , : ?

, DbMigration Up() Seed().

, Up(), Visual Studio .

  • Nuget
  • DbContext DbSet
  • visual studio Nuget "".
  • Nuget, Enable-Migrations
  • Nuget, , InitialCreation, add-Migration InitialCreation.

, .

  • , DbMigratinConfiguration Seed()
  • InitialCreation DbMigration Up() ( Down(). Up CreateTable

, , DataBase.SetInitializer , , , Up() Seed() :

  • InitialCreation.Up()
  • DatabaseSeeder.Seed()

- Configuration.Seed() .

InitialCraeation.Up()

public override void Up()
{
    CreateTable("dbo.UsageCosts",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                InvoicePeriod = c.DateTime(nullable: false),
                CustomerContractId = c.Long(nullable: false),
                TypeA = c.String(),
                TypeB = c.String(),
                VatValue = c.Decimal(nullable: false, precision: 18, scale: 2),
                PurchaseCosts = c.Decimal(nullable: false, precision: 18, scale: 2),
                RetailCosts = c.Decimal(nullable: false, precision: 18, scale: 2),
            })
        .PrimaryKey(t => t.Id);

"Hello World" :

    base.CreateStoredProcedure("dbo.HelloWorld3", "begin Select 'Hello World' end;");

:

    base.CreateStoredProcedure("dbo.update2", p => new
    {
        InvoicePeriod = p.DateTime(),
        CustomerContractId = p.Long(),
        TypeA = p.String(maxLength: 80),
        TypeB = p.String(maxLength: 80),
        VatValue = p.Decimal(10, 8),
        PurchaseCosts = p.Decimal(10, 8),
        RetailCosts = p.Decimal(10, 8),
    },
    @"begin
        Merge [usagecosts]
        Using (Select
            @InvoicePeriod as invoicePeriod,
            @CustomerContractId as customercontractId,
            @TypeA as typeA,
            @TypeB as typeB,
            @VatValue as vatvalue)
            As tmp 
        On ([usagecosts].[invoiceperiod] = tmp.invoiceperiod
        AND [usagecosts].[customercontractId] = tmp.customercontractid
        AND [usagecosts].[typeA] = tmp.typeA
        AND [usagecosts].[typeB] = tmp.typeB
        AND [usagecosts].[vatvalue] = tmp.Vatvalue)
    When Matched Then 
        Update Set [usagecosts].[purchasecosts] = [usagecosts].[purchasecosts] + @purchasecosts, [usagecosts].[retailcosts] = [usagecosts].[retailcosts] + @retailcosts
    When Not Matched Then
        Insert (InvoicePeriod, CustomerContractId, typea, typeb, vatvalue, purchasecosts, retailcosts)
        Values (@invoiceperiod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts);
     end;");
}

:

using (var dbContext = new DemoContext())
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"InvoicePeriod", usageCosts.InvoicePeriod),
        new SqlParameter(@"CustomerContractId", usageCosts.CustomerContractId),
        new SqlParameter(@"TypeA", usageCosts.TypeA),
        new SqlParameter(@"TypeB", usageCosts.TypeB),
        new SqlParameter(@"VatValue", usageCosts.VatValue),
        new SqlParameter(@"PurchaseCosts", 20M),
        new SqlParameter(@"RetailCosts", 30M),
    };
    string sqlCommand = String.Format(@"Exec {0} @InvoicePeriod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts", functionName);
    dbContext.Database.ExecuteSqlCommand(sqlCommand, functionParameters);
    dbContext.SaveChanges();
}

, DbSet. , UsageCosts, .

+8

More articles:


All Articles