Incorrect syntax near ','.

[jasoncavett]

I had this issue while using beta7, however, I still haven't been able to resolve with beta8, so I am posting here.

• Did you include the snippet of broken code in the issue?
  Here is the code that is breaking.

var story = new Story
{
    // other properties
    StoryPhotos = storyViewModel.ImagePaths.Select(i => new StoryPhoto {
        FilePath = i
    }).ToList()
};
await repository.AddAsync<Story>(story);

AddAsync looks like:

public async Task<T> AddAsync<T>(T t)
    where T : class
{
    context.Set<T>().Add(t);
    await context.SaveChangesAsync();
    return t;
}

• What are the EXACT steps to reproduce this problem?
  I call the above code (which is wrapped into a MVC endpoint that takes in a StoryViewModel). When I get to SaveChangesAsync, I see the following exception.

An unhandled exception occurred while processing the request.

SqlException: Incorrect syntax near ','.
System.Data.SqlClient.SqlConnection.ErrorHandler (System.Object sender, Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) <0x111bc22c0 + 0x00289> in <filename unknown>, line 0

DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
Microsoft.Data.Entity.Update.ReaderModificationCommandBatch+<ExecuteAsync>d__25.MoveNext () <0x111bb9a90 + 0x006d3> in <filename unknown>, line 0

• What package versions are you using (you can see these in the project.json file)?

    "EntityFramework.Commands": "7.0.0-beta8",
    "EntityFramework.SqlServer": "7.0.0-beta8",
    "Microsoft.AspNet.Antiforgery": "1.0.0-beta8",
    "Microsoft.AspNet.Authorization": "1.0.0-beta8",
    "Microsoft.AspNet.Diagnostics": "1.0.0-beta8",
    "Microsoft.AspNet.Identity.EntityFramework": "3.0.0-beta8",
    "Microsoft.AspNet.Mvc": "6.0.0-beta8",
    "Microsoft.AspNet.Mvc.DataAnnotations": "6.0.0-beta8",
    "Microsoft.AspNet.Mvc.TagHelpers": "6.0.0-beta8",
    "Microsoft.AspNet.Server.Kestrel": "1.0.0-beta8",
    "Microsoft.AspNet.Server.WebListener": "1.0.0-beta8",
    "Microsoft.AspNet.StaticFiles": "1.0.0-beta8",
    "Microsoft.AspNet.Tooling.Razor": "1.0.0-beta8",
    "Microsoft.Framework.CodeGenerators.Mvc": "1.0.0-beta8",
    "Microsoft.Framework.Configuration.CommandLine": "1.0.0-beta8",
    "Microsoft.Framework.Configuration.EnvironmentVariables": "1.0.0-beta8",
    "Microsoft.Framework.Configuration.Json": "1.0.0-beta8",
    "Microsoft.Framework.Logging.Console": "1.0.0-beta8",
    "System.Net.Http": "4.0.1-beta-23225"

• What operating system are you using?
  OSX El Capitan, Windows 8.1
• Other Technologies
  The backend is SQL Server 2005. I am wondering if this may play into this at all.

[rowanmiller]

Hey,

There isn't enough of a code listing here for us to reproduce the issue. AddAsync is called with memory, but there is no code that indicates what this is. Can you provide a complete code listing that we can run to reproduce the issue (including the model).

~Rowan

[jasoncavett]

@rowanmiller Apologies about that. I updated the example. I had switched to some old lingo when I was adding the code here so my example had memory referenced rather than story. It is now correct (and is still demonstrating the issue).

[rowanmiller]

Thanks, can you share the model (entity classes and context) so that we can debug the issue

[jasoncavett]

@rowanmiller See the below code for my context and the two, respective entity classes. Thank you for taking time to look at this. I appreciate it.

public class StoryContext : IdentityDbContext<ApplicationUser> 
    {
        public DbSet<Story> Stories { get; set; }
        public DbSet<StoryPhoto> StoryPhotos { get; set; }
        public DbSet<Author> Authors { get; set; }
        // ... some other sets but they don't interact with these three

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
        }
    }

    [Table("Stories")]
    public class Story
    {
        [Column("Id")]
        public int Id { get; set; }

        [Column("Text")]
        public string Text { get; set; }

        [Column("Title")]
        public string Title { get; set; }

        [Column("AuthorId")]
        public long AuthorId { get; set; }

        [ForeignKey("AuthorId")]
        public Author Author { get; set; }

        public virtual ICollection<StoryPhoto> StoryPhotos { get; set; }
    }

[Table("StoryPhoto")]
public class StoryPhoto
{
    [Column("Id")]
    public int Id { get; set; }

    [Column("FilePath")]
    public string FilePath { get; set; }

    [Column("StoryId")]
    public int StoryId { get; set; }

    [ForeignKey("StoryId")]
    public Story Story { get; set; }
}

[AndriySvyryd]

@jasoncavett I've removed Story.Author and Story.AuthorId as you didn't include that class in the code and I get no exception with the latest code. Please try again and if it still fails attach a self-contained project with the repro.

[jasoncavett]

@AndriySvyryd Thanks. Just to be sure, you added N StoryPhotos, where N > 1? That's the only time I notice the issue.

In any case, let me go back and try. I am using beta8, so perhaps I'll try to upgrade to RC1.

To help with debugging, is there any way I can easily see the SQL that is being generated (without direct access to the DB - long story about why I can't get to that)? I'll let you know what I can find. Thanks again.
Am now seeing the generated code. Will do more debugging on this and update as I learn more.

[jasoncavett]

Okay, I am able to recreate the issue directly on my SQL database. Here is the query that is being generated.

INSERT INTO [StoryPhoto] ([FilePath], [StoryId])
      OUTPUT INSERTED.[Id]
      VALUES ('path1', '174'),
      ('path2', '174'),
      ('path3', '174');

And I get this error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

In addition, being able to find this, I Googled my original question a bit differently (when I first ran across this) and found this post on SO which included this answer:

In order to use the multi-row VALUES(),() syntax, you need to be 
running SQL Server 2008 (or newer).

Since you are running SQL Server 2005, you need to run 
separate insert statements, use UNION/UNION ALL, or upgrade 
the instance (which is separate from Management Studio, which 
is just a client tool you use to connect to instances running any 
number of versions of SQL Server).

So, it was my suspicion in my original post. This particular application is still running SQL Server 2005.

This led me to find the database versions that EntityFramework.MicrosoftSqlServer support which is 2008 and greater.

I think that answers the question and this can be closed! Thanks to both you for taking a look. @rowanmiller @AndriySvyryd

[divega]

@jasoncavett Thanks for digging into this. I don't expect us to change our decision to support only SQL Server 2008 and above, but it is good for us to understand what exactly doesn't work with previous versions. It seems that our INSERT batching strategy (using a single INSERT statement with multiple VALUES entries) is one of those.

I believe if someone really needed to work with SQL Server 2005 they could create a different strategy that packs multiple INSERT statements in a single batch. This is something I think we could consider as a contribution.

[jasoncavett]

@divega Happy to help where I can! I did manage a workaround in the immediate, and, ultimately, this helped drive a decision to upgrade SQL Server. But, I may investigate the strategy that you suggested as I am sure I won't be the last person to run into this. If I come up with something, I'll look to contribute.