Entity framework generating wrong SQL

Question

I am attempting to insert a record through Entity Framework and it appears the SQL generated is incorrect. I am inserting into the 'PostingSelections' table. This table has FK's to three other tables 'PropertyPosting', 'PropertySummary' and 'User'. The FK to User is nullable. This is using a MySQL database, which I am suspicious is the problem.

The DDL for the PostingSelections table is:

CREATE TABLE `PostingSelections`(
    `Id` int NOT NULL AUTO_INCREMENT UNIQUE, 
    `SelectionType` TINYINT UNSIGNED NOT NULL, 
    `Date` datetime NOT NULL, 
    `SourceIPAddress` longtext NOT NULL, 
    `PropertyPosting_Id` int NOT NULL, 
    `PropertySummary_Id` int NOT NULL, 
    `User_Id` int);

My code is:

var postingselection = new postingselection();
postingselection.PropertyPosting_Id = 24;
postingselection.PropertySummary_Id = 24;  
postingselection.SelectionType = 1;  
postingselection.Date = DateTime.UtcNow;  
postingselection.SourceIPAddress = "TBD";   

db.postingselections.Add(postingselection);
db.SaveChanges();

The code is exactly the same as what is automatically generated by entityframework when I generate the controller automatically. The SQL that EF is generating is:

INSERT INTO `postingselections`(
`SelectionType`, 
`Date`, 
`SourceIPAddress`, 
`PropertyPosting_Id`, 
`PropertySummary_Id`, 
`User_Id`, 
`propertyposting_Id`, 
`propertysummary_Id`, 
`user_Id`) VALUES (
1, 
4/18/2017, 
TBD, 
24, 
24, 
0, 
24, 
24, 
NULL);
SELECT
`Id`
FROM `postingselections`
 WHERE  row_count() > 0 AND `Id`=last_insert_id()

See how it has generated three columns twice? PropertyPosting_Id, PropertyPosting_Id and User_Id? The error that is generated as expected is:

Failed in 71 ms with error: Column 'User_Id' specified twice

Could someone please help? This is a very basic insert, and I can't find any other similar postings. I can only conclude I have found a bug in EF for mySQL and will have to do this instead with a manual SQL statement.

May be user id is not nullable in object. If that's the case it should be something like int? userId { get; set; } — Raj Karri
– Raj Karri, Commented Apr 18, 2017 at 1:45
Since all 3 dupe fields are foreign-key (FK) related, seems that one-to-many relationship between them with non-nullable column triggers the issue. Try using nullable value types for all FKs. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Apr 18, 2017 at 1:47
Post your full entity model. And show navigation properties as well — Mantas Čekanauskas
– Mantas Čekanauskas, Commented Apr 18, 2017 at 5:46

iMad · Accepted Answer · 2017-04-18 08:43:48Z

1

I encountered a similar problem few days ago, I think it's related to the the Primary Key of "PostingSelections" table.

Try to specify a PK for your table, otherwise EF wont differentiate the records.

answered Apr 18, 2017 at 8:43

iMad

3461 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity framework generating wrong SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related