0

I have a simple table and a simple generated model to go along with it.

CREATE TABLE dbo.Admin (
     Id int IDENTITY(1, 1) NOT NULL
    ,DomainLogin nvarchar(50) NOT NULL
    ,CONSTRAINT PK_Admin PRIMARY KEY CLUSTERED (DomainLogin ASC)
)

When I attempt to add multiple entries via AddRange method (code below), it causes an error Incorrect syntax near ','.

var admins = new List<Admin>()
{
    new Admin() {DomainLogin = "zzz1"},
    new Admin() {DomainLogin = "zzz2"},
    new Admin() {DomainLogin = "zzz3"}
};

ctx.Admin.AddRange(admins);
ctx.SaveChanges();

I've also tried using the Add method:

ctx.Admin.Add(new Admin() {DomainLogin = "zzz1"});
ctx.Admin.Add(new Admin() {DomainLogin = "zzz2"});
ctx.Admin.Add(new Admin() {DomainLogin = "zzz3"});
ctx.SaveChanges();

However, if I save each individual item, like below, it works, but as you can imagine, it really slows down when there is a lot of rows:

ctx.Admin.Add(new Admin() {DomainLogin = "zzz1"});
ctx.SaveChanges();
ctx.Admin.Add(new Admin() {DomainLogin = "zzz2"});
ctx.SaveChanges();
ctx.Admin.Add(new Admin() {DomainLogin = "zzz3"});
ctx.SaveChanges();

Why am I getting an error?

I should mention that I am using Entity Framework 7 with asp.net 5 / mvc 6. The version of Entity Framework 7 is "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final"

P.S. I profiled the code and it generates the following SQL, which predictably gives the same error:

exec sp_executesql N'SET NOCOUNT OFF;
INSERT INTO [Admin] ([DomainLogin])
OUTPUT INSERTED.[Id]
VALUES (@p0),
(@p1),
(@p2);
',N'@p0 nvarchar(4000),@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=N'zzz1',@p1=N'zzz2',@p2=N'zzz3'

P.P.S This is against SQL Server 2005

AngryHacker
  • 59,598
  • 102
  • 325
  • 594
  • I mean, from your description, it appears there is a bug when it sends the batch of insert commands. Might just be as simple as that. Can you get the SQL it is generating for the `.SaveChanges` call? – test Dec 10 '15 at 20:03
  • can you post full error? – Sandip Bantawa Dec 10 '15 at 20:04
  • Are you using SQL Server 2005? – ErikEJ Dec 10 '15 at 20:04
  • 1
    I would recommend piping out the sql that gets created with http://stackoverflow.com/a/20757916/299327 so that you can test it independently of entity framework to see where the issue is. – Ryan Gates Dec 10 '15 at 20:07
  • @ErikEJ Yes, SQL Server 2005. Is that an issue? – AngryHacker Dec 10 '15 at 21:20
  • @test Yes. I grabbed the SQL and predictably, it gives same error. I added it to the question at the bottom – AngryHacker Dec 10 '15 at 21:21
  • @brykneval The error is here: http://i.imgur.com/SbUg3dY.png – AngryHacker Dec 10 '15 at 21:24
  • @RyanGates The method used (e.g. context.Database.Log) is not available in EF7. I am assuming cause it's not completed yet. But I added the generated SQL to the question. – AngryHacker Dec 10 '15 at 21:26
  • FWIW that insert query works on a SQL Server 2008 database and a SQL Server 2008 database with compatibility set to SQL Server 2005 (90). If you take that SQL and put it in a command window does it parse (`Ctrl+F5`)? – test Dec 10 '15 at 22:02
  • @test No, it doesn't. Causes the same error. Likely the box is too old. Just gonna move it to sql2012 and avoid the headache. – AngryHacker Dec 10 '15 at 22:15

1 Answers1

3

SQL Server 2005 is not supported by Entity Framework 7 (in fact SQL Server 2005 is not supported by Microsoft any longer).

Specifically, the multi-row INSERT VALUES syntax used by EF7 is not supported, it requires SQL Server 2008 or newer.

See https://github.com/aspnet/EntityFramework/issues/3691

You may be able to work around it by setting a MaxBatchSize of 1

ErikEJ
  • 40,951
  • 5
  • 75
  • 115