How do I correctly perform an eager load in Simple.Data?

Question

When I try to do an eager load (.WithXyz() method), I get the wrong data back. It tries to do a join using the primary Id of both tables, rather than the "property" on the main table joined to the Id of the secondary table.

Is this a bug in my code, in Simple.Data.MySql, or in Simple.Data?

I am using the version of Simple.Data (0.18.3.1) and Simple.Data.MySql (0.18.3.0) from NuGet.

My code:

var traceListener = new SimpleDataTraceListener(); // For logging SQL
Trace.Listeners.Add(traceListener);

var db = Database.Open();
OrderItem orderItem = db.OrderItems
    .WithCustomer()
    .Get(1)
    ;

Console.WriteLine(traceListener.Output);
Console.WriteLine(orderItem.Customer.FullName);

Here is an example of the SQL I expect:

SELECT orderitem.id,
       orderitem.customer_Id,
       orderitem.productname,
       customer.id AS __with1__Customer__id,
       customer.fullname AS __with1__Customer__fullname
FROM orderitem
LEFT JOIN customer ON (orderitem.customer_Id = customer.id)
WHERE orderitem.id = ?p1 LIMIT 0, 1

?p1 (UInt64) = 1

Here is the log of the SQL it is actually creating:

select orderitem.id,
       orderitem.customer_Id,
       orderitem.productname,
       customer.id AS __with1__Customer__id,
       customer.fullname AS __with1__Customer__fullname
from orderitem
LEFT JOIN customer ON (orderitem.id = customer.id)
WHERE orderitem.id = ?p1 LIMIT 0, 1

?p1 (UInt64) = 1

My data:

CREATE TABLE `customer` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `fullname` VARCHAR(130) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `fullname_UNIQUE` (`fullname` ASC)
);

CREATE TABLE `orderitem` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `customer_Id` BIGINT(20) NOT NULL,
  `productname` VARCHAR(130) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`customer_Id`) REFERENCES `customer` (`id`)
);

INSERT INTO `customer` (fullname)
VALUES ('wall-E'), ('merlyn'), ('someone');

INSERT INTO `orderitem` (customer_Id, productName)
VALUES (3, 'test item 1'), (2, 'test item 2'), (1, 'test item 3');

score 2 · Answer 1 · answered Mar 01 '13 at 11:24

It looks (from https://github.com/Vidarls/Simple.Data.Mysql/blob/master/Src/Simple.Data.Mysql.Mysql40/MysqlForeignKeyCreator.cs) like there might be a problem with the way the MySQL provider handles foreign keys. The provider was written for MySQL 4.0; the syntax may have changed considerably since that version.

I suggest raising an issue on that project's GitHub page (https://github.com/Vidarls/Simple.Data.Mysql/issues), or perhaps helping out with a pull request if you can.

In the meantime, you can explicitly specify the join like this:

var db = Database.Open();
dynamic customer;
OrderItem orderItem = db.OrderItems
    .FindAllById(1)
    .OuterJoin(db.Customers.As("Customer"), out customer)
    .On(Id: db.OrderItems.CustomerId)
    .With(customer)
    .FirstOrDefault();

I've also changed the code to use a query and FirstOrDefault, so that the explicit join works.

How do I correctly perform an eager load in Simple.Data?

1 Answers1