c# - ASP MVC MsSql to MySQL migration -

for low budget project have run iis asp mvc mysql. migrating existing project runs fine if create linq query take & skip 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 trace:

unknown column 'extent1.id' in 'where clause'mysql.data.mysqlclient.mysqlexception

makes no sense @ all. same code mssql working fine. using latest mysql.data.entity.ef6, version=6.9.7.0

am missing something? spend hours solve without success.

are sure second query ok?

1) id = d.id, <= why comma (not important)? ('id =' redundant)

2) .where(m => m.trash == false) <= 'trash' not in select, property not know @ time

3) .orderbydescending(f => f.created) <= idem 'created'

4) why comma after .tolist()?

i have simplified ddl (which not mwe) generated data. have reproduced problem in vs2013.

i have test query linqpad directly against database , have same problem third test, bug in 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 shorter sql query:

select t1.postid, t1.body, t1.id, t1.created, t1.trash trdposts t0     outer apply (       select t2.body, t2.created, t2.id, t2.postid, t2.trash       trdcomments t2       ((t2.postid = t0.id) , (t2.trash = 1))       order t2.created desc   ) t1 order t1.created desc 

without .skip() , .take(), 'left outer join'