CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkcode-firstentity-framework-migrations
Blaise
Blaise

Reputation: 22222

How to Create Function in Code First?

Is it possible to use Code First to create a database function in sql server?

Like

CREATE FUNCTION [dbo].[fnIsPaid] ...

Here is how I have worked it out based on ChrFin's suggestion. I marked it as the answer. But let me keep a more detailed record here.

In the Package Manager Console,Add-Migration AddFnIsPaid`.

This will create a DbMigration class prefixed with timestamp.

In this migration class, I have a create script and drop script for Up() and Down() method:

public partial class AddFnIsPaid : DbMigration
{
    public override void Up()
    {
        Sql(_createFunctionScript);
    }

    public override void Down()
    {
        Sql(DropFunctionScript);
    }


    #region SQL Scripts
    private readonly string _createFunctionScript = string.Concat(
            "CREATE FUNCTION [dbo].[fnIsPaid] ",
            "(",
            ...
            ") ",
            "RETURNS bit ",
            "AS ",
            "BEGIN ",
            ...
            "END"
            );

    private const string DropFunctionScript = "DROP FUNCTION [dbo].[fnIsPaid]";  
    #endregion
}

Upvotes: 7

Views: 8096

Answers (2)

HaiNguyen
HaiNguyen

Reputation: 312

I know old question but in case anyone in need. if you prefer a more simple flexible way. create a method in the Configuration.cs file. Then put your sql script in \App_Data directory that way you can put any SQL script you like in the .SQL file. Works fine in MVC 5.0, EF6.0

    private void CreateProcedure(MvcAppDb context)
    {  
        string path = AppDomain.CurrentDomain.GetData("DataDirectory").ToString() + @"/Procedures.sql";
        Console.WriteLine(path);
        FileInfo file = new FileInfo(path);
        string script = file.OpenText().ReadToEnd();
        context.Database.ExecuteSqlCommand(script);  
    }

Then add a call to the Configuration.cs and you all set.

 protected override void Seed(MvcAppDb context)
    {

        CreateProcedure(context);

Upvotes: 5

Christoph Fink
Christoph Fink

Reputation: 23113

No, you can only tell it to map CRUD actions to stored procedures (EF 6+) with e.g.:

modelBuilder
   .Entity<Post>()
   .MapToStoredProcedures();

But you can execute custom SQL in your migration:

public override void Up()
{
    Sql("CREATE FUNCTION [dbo].[fnIsPaid] ...");
}

Upvotes: 5

Related Questions