MySQL not working well

RickardL

I run a mysql 8 database and as far as I understand I need third party drivers to get this to work (please correct me otherwise).

I tried IQ Driver but it refuses to connect to MySQL 8 (with it's not auth method 'cache_sha2_password')
I then tried Linq to DB Driver

But well... I see the tables but....

First off links are just null even tho there is correct foreign keys




Then, if I enable the rest of the query above (that is commented away) like so


You see that is has now taken a different row just because I included a select with a link to license (without the "FromLicense = x.License.Id") it would keep the same behaviour, so not really select that is the issue but rather the link.

I can force it like this



But this does not feel trustworthy, so please please include good MySql 8 support.

JoeAlbahari

Regarding the IQDriver, are you using the latest driver? (2.1.4.1 beta?) This problem should have been fixed in that beta.

Regarding the third-party LINQ-to-DB driver, it sounds like it doesn't support lazy loading (which will be why the License link is null). As to why it picks a different row when adding the license in the projection, this is not a bug. It will be because the generated SQL will include a join (look at the SQL tab), which will result in MySQL producing a different query execution plan. If you don't specify an order, the database server is free to pick whatever it likes, and it can change depending on the query execution plan.

As it happens, right now I'm working on a built-in driver in LINQPad 6 that will support MySQL, SQLite, Oracle and Postgres, via EF Core. Would you be interested in beta-testing this when its released?

Dluk

Regarding linq2db, as Joe already said, it is due to LazyLoading not working as you expected. Linq2db will not load related objects if you don't ask it to do so by using LoadWith method. You can check some examples here https://github.com/linq2db/linq2db/blob/master/Tests/Linq/Linq/LoadWithTests.cs

RickardL

Thank you both for great answers.

@JoeAlbahari: Yes I would be very intrested in testing the MySQL built in support

JoeAlbahari

LINQPad 6 now has a built-in MySQL driver. If you've already downloaded the beta, it will update itself.

Let me know how you get along.