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.
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.