ASP MVC MsSql to MySQL migration

Question

For a low budget project I have to run IIS Asp Mvc with MySql. Migrating an existing project runs fine but if I create a LINQ query with Take & Skip it fails.

First Test (OK)

var post = _db.Posts.FirstOrDefaultAsync(a => a.id == 1234);

Second Test (OK)

var post = _db.Posts.Include(a => a.Comments);
var result = await post.Select(a => new TRDPostViewModel
{
  Created = a.Created,
  Body = a.Body,
  Comments = a.Comments.Select(d => new TRDCommentViewModel
  {
     Body = d.Body,
     Id = d.Id,
  }).Where(m => m.Trash == false)
     .OrderByDescending(f => f.Created)
   .ToList(),
}).FirstOrDefaultAsync();

Third Test (FAIL)

var result = await post.Select(a => new TRDPostViewModel
{
  Created = a.Created,
  Body = a.Body,
  Comments = a.Comments.Select(d => new TRDCommentViewModel
  {
     Body = d.Body,
     Id = d.Id,
  }).Where(m => m.Trash == false)
     .OrderByDescending(f => f.Created)
     .Skip(33)
     .Take(10)
   .ToList(),
}).FirstOrDefaultAsync();

And here is the Trace:

Unknown column 'Extent1.Id' in 'where clause'MySql.Data.MySqlClient.MySqlException

Makes no sense at all. Same code with MsSql is working fine. Using latest MySql.Data.Entity.EF6, Version=6.9.7.0

Am I missing something? Spend hours to solve but without success.

Did you try to activates the EF logger (Database.Log = Console.Write) to check what happens ? — christophe.chapron, Sep 26 '15 at 14:07
Jap I logged the failing sql query. But this is really strange. I search the log and paste it here. — creality, Sep 27 '15 at 18:37

score 0 · Answer 1 · answered Sep 28 '15 at 09:54

Are you sure your second query is really OK?

1) Id = d.Id, <= Why this comma (not really important)? ('ID =' is redundant)

2) .Where(m => m.Trash == false) <= 'Trash' is not in the select, so this property is not know at this time

3) .OrderByDescending(f => f.Created) <= idem for 'Created'

4) Why a comma after .ToList()?

I have simplified your DDL (which is not a MWE) with generated data. I have reproduced your problem in VS2013.

I have also test your query with LINQPad directly against the database and I have the same problem with the third test, probably a bug in the driver mysql:

trdposts.Select(a => new {
    Created = a.Created,
    Body = a.Body,
    Comments = a.Posttrdcomments
                .Select(d => new { Body = d.body, Id = d.Id, d.Created, d.Trash})
                .Where(m => m.Trash == 1)
                .OrderByDescending(f => f.Created)
                .Skip(33)
                .Take(10)
                .ToList()
    })

Give a shorter SQL query:

SELECT t1.PostId, t1.body, t1.Id, t1.Created, t1.Trash
FROM trdposts AS t0
    OUTER APPLY (
      SELECT t2.body, t2.Created, t2.Id, t2.PostId, t2.Trash
      FROM trdcomments AS t2
      WHERE ((t2.PostId = t0.Id) AND (t2.Trash = 1))
      ORDER BY t2.Created DESC
  ) AS t1
ORDER BY t1.Created DESC

Without .Skip() and .Take(), we get good 'LEFT OUTER JOIN'

Jap confirmed. Just ignore the comma - it makes no sense but its ok there. I also think thats a bug in mysql driver. Thank you for your time to hunt this bug. Whats next? I try to reach the MySQL buddies and let you know. — creality, Sep 28 '15 at 19:43

score 0 · Answer 2 · answered Dec 28 '15 at 20:31

That kind of query is kind of impossible to do with MySQL. How would you write it in SQL if you wrote the query yourself? The problem is that MySQL has no support for what the SQL standard calls lateral joins, in MsSQL the keyword "APPLY" is used. The .NET driver for PostgreSQL and MsSQL supports those kind of queries, but not MySQL.

score 0 · Answer 3 · answered May 23 '18 at 21:13

0

Its a known issue with MySQL EF and from other posts maybe MySQL itself. See:

https://bugs.mysql.com/bug.php?id=78610

and other SO post:

Unknown column 'Project2.Name' in 'where clause'

it was posted 3 years ago and marked critical if that tells you anything. It exists in MySQL connector 6.9.11.0 and later. I've just had to work around it as best i can. I don't expect it to get fixed.

answered May 23 '18 at 21:13

Dennis Robinson

31
2

Thanks for your answer. In the meantime I switched to MongoDB as database provider. I reported this in the oracle bugtracker database 2 years ago. Until today the bug exists. – creality May 24 '18 at 13:18

ASP MVC MsSql to MySQL migration

3 Answers3

Linked