EF6 with MySQL - can't add new row

Question

EDIT: The issues was because I foolishly took off the primary keys from the table and EF6 won't work with the pks :)

I'm playing around with EF6 and MySQL and can read records fine from the database with something simple like...

using (airtableEntities context = new airtableEntities())
{
    accList = context.icqties.ToList();
}

However, when i'm trying to add a new icqty to the table like so

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp3
{
    class Program
    {
        static void Main()
        {
            using (airtableEntities context = new airtableEntities())
            {
                icqty test = new icqty()
                {
                    ProductCode = "test",
                    AirTableRec = "nfklwn",
                    LocationCode = "AKL",
                    QuantityInStock = 5,
                    RecordRevision = 2
                };
                context.icqties.Add(test);
                context.SaveChanges();

                Console.WriteLine($"Added all new rows to db");
            }
        }
    }
}

//------------------------------------------------------------------------------
// 
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// 
//------------------------------------------------------------------------------

namespace ConsoleApp3
{
    using System;
    using System.Collections.Generic;

    public partial class icqty
    {
        public string ProductCode { get; set; }
        public Nullable QuantityInStock { get; set; }
        public string LocationCode { get; set; }
        public int RecordRevision { get; set; }
        public string AirTableRec { get; set; }
    }
}

I get an exception saying

An unhandled exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll An error occurred while updating the entries. See the inner exception for details. occurred

MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT icqty.ProductCode, icqty.QuantityInStock, icqty.`LocationCod' at line 1

However - I don't understand why it's doing a select. It should be doing an insert into this table?

There is a screenshot here to help you understand what's going on - https://i.sstatic.net/8Yla2.jpg

I'm using MySql.Data.EntityFramework version 8.0.13.0

I'm running this as My appsettings.json looks like this

I also added in some SQL logging to the console and this is what I get

Opened connection at 3/12/2018 4:32:36 PM +13:00

Started transaction at 3/12/2018 4:32:36 PM +13:00

INSERT INTO (SELECT
`icqty`.`ProductCode`,
`icqty`.`QuantityInStock`,
`icqty`.`LocationCode`,
`icqty`.`RecordRevision`,
`icqty`.`AirTableRec`
FROM `icqty` AS `icqty`)(
`ProductCode`,
`QuantityInStock`,
`LocationCode`,
`RecordRevision`,
`AirTableRec`) VALUES (
@gp1,
5,
@gp2,
2,
@gp3)


-- @gp1: 'test' (Type = String, IsNullable = false, Size = 4)

-- @gp2: 'AKL' (Type = String, IsNullable = false, Size = 3)

-- @gp3: 'nfklwn' (Type = String, IsNullable = false, Size = 6)

-- Executing at 3/12/2018 4:32:37 PM +13:00

-- Failed in 17 ms with error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT
`icqty`.`ProductCode`,
`icqty`.`QuantityInStock`,
`icqty`.`LocationCod' at line 1



Closed connection at 3/12/2018 4:32:37 PM +13:00

Disposed transaction at 3/12/2018 4:32:37 PM +13:00

Thanks for your help!

Tetsuya Yamamoto · Accepted Answer

Sounds like the problem of inserting is the fact you're not specifying primary key field inside icqty table, as shown in this table definition:

public partial class icqty
{
    // what is the primary key field??

    public string ProductCode { get; set; }
    public Nullable QuantityInStock { get; set; }
    public string LocationCode { get; set; }
    public int RecordRevision { get; set; }
    public string AirTableRec { get; set; }
}

Since no primary key is defined inside table class definition, EF issuing INSERT INTO query using incorrect SELECT statement position, which causing DbUpdateException because EF requires primary key to be defined inside the table before being able to create proper INSERT query.

To mitigate this issue, create/set a primary key field inside icqty table:

-- add new PK column
ALTER TABLE icqty ADD COLUMN `Id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

-- using existing column as PK
ALTER TABLE icqty MODIFY COLUMN `existingcolumnname` INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

Then set it as auto-generated identity column by setting StoreGeneratedPattern option to Identity or use attributes like example below:

public partial class icqty
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; } // primary key example
    public string ProductCode { get; set; }
    public Nullable QuantityInStock { get; set; }
    public string LocationCode { get; set; }
    public int RecordRevision { get; set; }
    public string AirTableRec { get; set; }
}

Also make sure if the MySQL Connector .NET (MySql.Data.dll) version compatible with corresponding MySQL version and registered inside web.config in proper way.

Related issues:

Entity Framework (EF6) + MySql Database First Model Many to Many Relationship Wrong Query Generation

DBContext.SaveChanges() Method Throwing DbUpdateException

EF6 with MySQL - can't add new row

Answers (2)

Related Questions

EF6 with MySQL - can&#39;t add new row

Answers (2)

Related Questions

EF6 with MySQL - can't add new row