Description:
Hello,
we are using 6.9.9 connector in combination with EF 6 (6.1.3).
If we include one collection to to a collection it works fine, but if we include another one we get this exception (Unkown Column `Extent1`.`id` in 'on clause').
We also posted this behavior in your forums: http://forums.mysql.com/read.php?38,650028,650028#msg-650028
Or the Stackoverflow post: http://stackoverflow.com/questions/39287545/entity-framework-include-multiple-collection-p...
How to repeat:
This will work:
using (var context = new MyDBContext())
{
var a1 = context.A1.Include(d => d.A3)
.Include(d => d.A3.Select(d2 => d2.A41))
.FirstOrDefault(w => w.id == 1);
}
This will crash:
using (var context = new MyDBContext())
{
var a1 = context.A1.Include(d => d.A3)
.Include(d => d.A3.Select(d2 => d2.A41))
.Include(d => d.A3.Select(d2 => d2.A42))
.FirstOrDefault(w => w.id == 1);
}
//////// Create Statement for reproducing
/*
Navicat MySQL Data Transfer
Source Server : VM-SQL14
Source Server Version : 50710
Source Host : 192.168.113.21:3306
Source Database : SURIS
Target Server Type : MYSQL
Target Server Version : 50710
File Encoding : 65001
Date: 2016-09-05 10:14:26
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for A1
-- ----------------------------
DROP TABLE IF EXISTS `A1`;
CREATE TABLE "A1" (
"id" int(11) NOT NULL,
"F1" varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Records of A1
-- ----------------------------
INSERT INTO `A1` VALUES ('1', 'Test');
-- ----------------------------
-- Table structure for A2
-- ----------------------------
DROP TABLE IF EXISTS `A2`;
CREATE TABLE "A2" (
"A1Id" int(11) NOT NULL,
"A3Id" int(11) NOT NULL,
PRIMARY KEY ("A1Id","A3Id"),
KEY "A3Id" ("A3Id"),
CONSTRAINT "a2_ibfk_1" FOREIGN KEY ("A1Id") REFERENCES "A1" ("id") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "a2_ibfk_2" FOREIGN KEY ("A3Id") REFERENCES "A3" ("Id") ON DELETE CASCADE ON UPDATE NO ACTION
);
-- ----------------------------
-- Records of A2
-- ----------------------------
INSERT INTO `A2` VALUES ('1', '1');
-- ----------------------------
-- Table structure for A3
-- ----------------------------
DROP TABLE IF EXISTS `A3`;
CREATE TABLE "A3" (
"Id" int(11) NOT NULL,
"F1" varchar(255) CHARACTER SET latin1 DEFAULT NULL,
"Mid" int(11) DEFAULT NULL,
PRIMARY KEY ("Id"),
KEY "Mid" ("Mid")
);
-- ----------------------------
-- Records of A3
-- ----------------------------
INSERT INTO `A3` VALUES ('1', 'dfdsfdsl', null);
-- ----------------------------
-- Table structure for A3MA41
-- ----------------------------
DROP TABLE IF EXISTS `A3MA41`;
CREATE TABLE "A3MA41" (
"A3_Id" int(11) NOT NULL,
"A41_Id" int(11) NOT NULL,
PRIMARY KEY ("A3_Id","A41_Id"),
KEY "a3ma41_ibfk_2" ("A41_Id"),
CONSTRAINT "a3ma41_ibfk_1" FOREIGN KEY ("A3_Id") REFERENCES "A3" ("Id") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "a3ma41_ibfk_2" FOREIGN KEY ("A41_Id") REFERENCES "A41" ("Mid") ON DELETE CASCADE ON UPDATE NO ACTION
);
-- ----------------------------
-- Records of A3MA41
-- ----------------------------
-- ----------------------------
-- Table structure for A3MA42
-- ----------------------------
DROP TABLE IF EXISTS `A3MA42`;
CREATE TABLE "A3MA42" (
"A3_Id" int(11) NOT NULL,
"A42_Id" int(11) NOT NULL,
PRIMARY KEY ("A3_Id","A42_Id"),
KEY "A42_Id" ("A42_Id"),
CONSTRAINT "a3ma42_ibfk_1" FOREIGN KEY ("A3_Id") REFERENCES "A3" ("Id") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "a3ma42_ibfk_2" FOREIGN KEY ("A42_Id") REFERENCES "A42" ("Mid") ON DELETE CASCADE ON UPDATE NO ACTION
);
-- ----------------------------
-- Records of A3MA42
-- ----------------------------
-- ----------------------------
-- Table structure for A41
-- ----------------------------
DROP TABLE IF EXISTS `A41`;
CREATE TABLE "A41" (
"A3Id" int(11) NOT NULL,
"Mid" int(11) NOT NULL,
"F" varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY ("Mid")
);
-- ----------------------------
-- Records of A41
-- ----------------------------
INSERT INTO `A41` VALUES ('1', '1', null);
-- ----------------------------
-- Table structure for A41MC1
-- ----------------------------
DROP TABLE IF EXISTS `A41MC1`;
CREATE TABLE "A41MC1" (
"A41_Id" int(11) NOT NULL,
"A41_C1_Id" int(11) NOT NULL,
PRIMARY KEY ("A41_Id","A41_C1_Id"),
KEY "a41mc1_ibfk_2" ("A41_C1_Id"),
CONSTRAINT "a41mc1_ibfk_1" FOREIGN KEY ("A41_Id") REFERENCES "A41" ("Mid") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "a41mc1_ibfk_2" FOREIGN KEY ("A41_C1_Id") REFERENCES "A41_C1" ("Id") ON DELETE CASCADE ON UPDATE NO ACTION
);
-- ----------------------------
-- Records of A41MC1
-- ----------------------------
INSERT INTO `A41MC1` VALUES ('1', '1');
-- ----------------------------
-- Table structure for A41MC2
-- ----------------------------
DROP TABLE IF EXISTS `A41MC2`;
CREATE TABLE "A41MC2" (
"A41_Id" int(11) NOT NULL,
"A41_C2_Id" int(11) NOT NULL,
PRIMARY KEY ("A41_Id","A41_C2_Id"),
KEY "A41_C2_Id" ("A41_C2_Id"),
CONSTRAINT "a41mc2_ibfk_1" FOREIGN KEY ("A41_Id") REFERENCES "A41" ("Mid") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "a41mc2_ibfk_2" FOREIGN KEY ("A41_C2_Id") REFERENCES "A41_C2" ("Id") ON DELETE CASCADE ON UPDATE NO ACTION
);
-- ----------------------------
-- Records of A41MC2
-- ----------------------------
INSERT INTO `A41MC2` VALUES ('1', '2');
-- ----------------------------
-- Table structure for A41_C1
-- ----------------------------
DROP TABLE IF EXISTS `A41_C1`;
CREATE TABLE "A41_C1" (
"Id" int(11) NOT NULL,
PRIMARY KEY ("Id")
);
-- ----------------------------
-- Records of A41_C1
-- ----------------------------
INSERT INTO `A41_C1` VALUES ('1');
-- ----------------------------
-- Table structure for A41_C2
-- ----------------------------
DROP TABLE IF EXISTS `A41_C2`;
CREATE TABLE "A41_C2" (
"Id" int(11) NOT NULL,
PRIMARY KEY ("Id")
);
-- ----------------------------
-- Records of A41_C2
-- ----------------------------
INSERT INTO `A41_C2` VALUES ('2');
-- ----------------------------
-- Table structure for A42
-- ----------------------------
DROP TABLE IF EXISTS `A42`;
CREATE TABLE "A42" (
"A3Id" int(11) NOT NULL,
"Mid" int(11) NOT NULL,
"F1" varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY ("Mid")
);
-- ----------------------------
-- Records of A42
-- ----------------------------
INSERT INTO `A42` VALUES ('1', '1', null);
-- ----------------------------
-- Table structure for A42MC1
-- ----------------------------
DROP TABLE IF EXISTS `A42MC1`;
CREATE TABLE "A42MC1" (
"A42_Id" int(11) NOT NULL,
"A42_C1_Id" int(11) NOT NULL,
PRIMARY KEY ("A42_Id","A42_C1_Id"),
KEY "A42_C1_Id" ("A42_C1_Id"),
CONSTRAINT "a42mc1_ibfk_1" FOREIGN KEY ("A42_Id") REFERENCES "A42" ("Mid") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "a42mc1_ibfk_2" FOREIGN KEY ("A42_C1_Id") REFERENCES "A42_C1" ("Id") ON DELETE CASCADE ON UPDATE NO ACTION
);
-- ----------------------------
-- Records of A42MC1
-- ----------------------------
INSERT INTO `A42MC1` VALUES ('1', '1');
-- ----------------------------
-- Table structure for A42MC2
-- ----------------------------
DROP TABLE IF EXISTS `A42MC2`;
CREATE TABLE "A42MC2" (
"A42_Id" int(11) NOT NULL,
"A42_C2_Id" int(11) NOT NULL,
PRIMARY KEY ("A42_Id","A42_C2_Id"),
KEY "A42_C2_Id" ("A42_C2_Id"),
CONSTRAINT "a42mc2_ibfk_1" FOREIGN KEY ("A42_Id") REFERENCES "A42" ("Mid") ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT "a42mc2_ibfk_2" FOREIGN KEY ("A42_C2_Id") REFERENCES "A42_C2" ("Id") ON DELETE CASCADE ON UPDATE NO ACTION
);
-- ----------------------------
-- Records of A42MC2
-- ----------------------------
INSERT INTO `A42MC2` VALUES ('1', '2');
-- ----------------------------
-- Table structure for A42_C1
-- ----------------------------
DROP TABLE IF EXISTS `A42_C1`;
CREATE TABLE "A42_C1" (
"Id" int(11) NOT NULL,
PRIMARY KEY ("Id")
);
-- ----------------------------
-- Records of A42_C1
-- ----------------------------
INSERT INTO `A42_C1` VALUES ('1');
-- ----------------------------
-- Table structure for A42_C2
-- ----------------------------
DROP TABLE IF EXISTS `A42_C2`;
CREATE TABLE "A42_C2" (
"Id" int(11) NOT NULL,
PRIMARY KEY ("Id")
);
-- ----------------------------
-- Records of A42_C2
-- ----------------------------
INSERT INTO `A42_C2` VALUES ('2');
Suggested fix:
It works fine with MSSQL.
