ExecuteSqlCommand on seed to create trigger with EntityFramework throws SqlException

c# entity-framework entity-framework-6 sql

Question

I'm trying to create a trigger to update a document number using the Seed() method in a ContextInitalizer with Entity Framework 6.0.2 and .Net 4. When I run the SQL separately the trigger is created; during context initialization an SqlException is thrown stating:

Incorrect syntax near the word 'TRIGGER'.

My sql script -- contained in /SQL/CreateOrderNumber.sql -- is:

CREATE TRIGGER [dbo].[CreateOrderNum] 
ON [dbo].[Orders] 
AFTER INSERT AS 
BEGIN 
SET NOCOUNT ON; 
DECLARE @MaximumNumber int; 
SET @MaximumNumber = (SELECT ISNULL(MAX(RIGHT([DocNumber],6)),0) FROM [Orders]); 
UPDATE [Orders] 
SET [DocNumber] = 'ORD-' + RIGHT('000000' + CAST((@MaximumNumber + 1) AS VARCHAR(8)), 6) 
FROM inserted 
WHERE [Orders].[Id] = inserted.Id; 
END

And the following code recreates the error:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;

namespace TriggerCreationTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create the context, add an order, and save...
            using (var context = new Context())
            {
                context.Orders.Add(new Order());
                context.SaveChanges();
            }
        }
    }

    public class Context : DbContext
    {
        public Context() : base("TestDatabase")
        {
            Database.SetInitializer<Context>(new ContextInitializer());
        }

        public DbSet<Order> Orders { get; set; }
    }

    public class ContextInitializer : DropCreateDatabaseAlways<Context>
    {
        protected override void Seed(Context context)
        {
            // Get the file and read the text
            var execPath = Assembly.GetExecutingAssembly().Location;
            var createOrderNumPath = Path.Combine(execPath, @"..\SQL\CreateOrderNumber.sql");
            var sql = File.ReadAllText(createOrderNumPath);

            // Execute the CREATE TRIGGER on the database.
            var emptyparams = new SqlParameter[] { new SqlParameter() };
            context.Database.ExecuteSqlCommand(sql, emptyparams);

            base.Seed(context);
        }
    }

    public class Order
    {
        public Order() { }

        [Key]
        public int Id { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string DocNumber { get; set; }
    }
}

At this point I've attempted rewriting the CREATE TRIGGER script according to various sources around the web, but haven't had any success getting it to run.

1
1
2/23/2014 7:51:16 PM

Accepted Answer

After having worked around this, and paying attention to other projects, I returned to see whether upgrading to EntityFramework 6.1.0 would make a difference. It did not!

I did, however, find my mistake. I was passing an empty SqlParameter to the SqlCommand and this was causing it to fail. If instead I change the SqlParameter array to contain no elements:

public class ContextInitializer : DropCreateDatabaseAlways<Context> 
{
     protected override void Seed(Context context)
     {
         // Get the file and read the text
         var execPath = Assembly.GetExecutingAssembly().Location;
         var createOrderNumPath = Path.Combine(execPath, @"..\SQL\CreateOrderNumber.sql");
         var sql = File.ReadAllText(createOrderNumPath);

         // Execute the CREATE TRIGGER on the database.
         // CHANGE emptyparams TO CONTAIN NO ELEMENTS
         var emptyparams = new SqlParameter[] { };
         context.Database.ExecuteSqlCommand(sql, emptyparams);

         base.Seed(context);
     }

}

The command executes as expected and the trigger is created.

1
6/7/2014 1:14:06 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow