Devart Forums

Posted: **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!

Posted: **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.

Posted: **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!

Posted: **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.

Devart Forums

Entity Framework problem with a simple Linq query

Entity Framework problem with a simple Linq query