Entity Framework problem with a simple Linq query

Entity Framework problem with a simple Linq query

Postby my.dev » Thu 06 Nov 2008 07:38

Hello,

I am an Entity Framework beginner and a registered user of MyDirect.NET. I have the latest version installed (not beta). I have tried something really simple and I got an error (that appears to be a MySql fault but I am not sure).

I have two tables in a MySql database (orders and details). They are very simple and the idea is to have a foreign key relationship between them. I have created an EDMX and imported the two-table database using MyDirect.NET. This all appears ok. I have then tried the following:

Code: Select all
            testEntities7 te = new testEntities7();

            var q = from d in te.details
                    join o in te.orders on d.orders equals o
                    select o;
            string s = (q as System.Data.Objects.ObjectQuery).ToTraceString();
            IList list = q.ToList();


The generated query string from variable "s" is as follows:
Code: Select all
SELECT
Extent2.id AS id,
Extent2.name AS name
FROM  test.details AS Extent1
INNER JOIN test.orders AS Extent2 ON  EXISTS (SELECT
   1 AS C1
   FROM    ( SELECT 1) AS SingleRowTable1
   LEFT OUTER JOIN  (SELECT
      Extent3.id AS id
      FROM test.orders AS Extent3
      WHERE Extent1.orderfk = Extent3.id ) AS Project1 ON 1 = 1
   LEFT OUTER JOIN  (SELECT
      Extent4.id AS id
      FROM test.orders AS Extent4
      WHERE Extent1.orderfk = Extent4.id ) AS Project2 ON 1 = 1
   WHERE (Project1.id = Extent2.id) OR ((Project2.id IS NULL) AND (Extent2.id IS NULL))
)

... and the following ToList() fails with the following error:
Code: Select all
{"Unknown column 'Extent1.orderfk' in 'where clause'"}


I have tried running the very same query in PhpMyAdmin and it threw the same error. So it appears the query is constructed badly. I wonder who's fault is this...

I have then tried recreating the same database in SQLExpress and doing basically the same thing and everything is OK there! No exceptions are thrown and everything works as expected. I have checked the generated query and they are functionally the same.

This leads me to believe the problem is either in MyDirect.NET or MySql. My database version is 5.0.51a-3ubuntu5.1.

The database structure is as follows:
Code: Select all
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE IF NOT EXISTS `details` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) collate utf8_unicode_ci NOT NULL,
  `orderfk` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `orderfk` (`orderfk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
ALTER TABLE `details`
  ADD CONSTRAINT `details_ibfk_1` FOREIGN KEY (`orderfk`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;


I would use attachments instead but I could not find a way to attach anything to a post. Sorry.

Please help! Thanks!
my.dev
 
Posts: 2
Joined: Thu 06 Nov 2008 07:17

Postby Shalex » Mon 10 Nov 2008 09:40

1. We recommend you to change the current LINQ query to the following:
Code: Select all
var q = from d in te.details
                  join o in te.orders on d.orders.id equals o.id
                  select o;


2. Probably, you needed such LINQ query:
Code: Select all
var q = te.orders.Include("details");


3. It is UNIQUE KEY 'orderfk' ('orderfk') in the SQL script - as a result, the "one-to-one" relation is obtained. It is not suitable for usual "order-details" conception.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Postby my.dev » Mon 10 Nov 2008 10:45

Thanks for your reply!

1. Since this is basically LINQ to Entity, isn't it correct to write
Code: Select all
d.orders equals o
instead of referencing IDs? This defeats the purpose of entities if one has to care about IDs.

Besides, the no-ID code works perfectly with SQLExpress database. So could this even remotely be considered as a bug (or perhaps a missing important feature)?

2. I am not sure what exactly you meant to solve using Include(). Could you please be more specific?

3. Order-details concept was not the point here. I admit I named the tables wrongly. I only meant to have two related tables to replicate the actual database structure from my own real-world situation.

I found it really disturbing that really simple queries would fail due to MySql database server limitations. It appears that MySql fails to recognize some aliases in nested queries and since aliases are used everywhere in SQL code generation, I am really fearful that I might get unexpected problems during my development that I won't be able to overcome.

So, would you say that I am simply writing my LINQ queries in a wrong fashion?

And if so, how can it be that the very same code interfacing an SQLExpress database works flawlessly? I am wondering if the problems I am encountering are due to MyDirect.NET or MySql server or both.

Thank you!
my.dev
 
Posts: 2
Joined: Thu 06 Nov 2008 07:17

Postby Shalex » Mon 10 Nov 2008 13:47

Transformation from the LINQ to Entities request to the SQL text is implemented in several steps. First, it is made by the Microsoft code that creates Expression tree. Second, our provider translates it to SQL. Microsoft focused on the MS SQL Server while making EF implementation, that's why the result request could be not valid for separate DBMS in some cases. Generally, the workaround can be found: to rewrite the request in appropriate way or to modify a bit the DB.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL