First() causing problems

[eheaney]

We are migrating an application from MSSQL Server 2005 to MySQL, using dotConnect for MySQL (8.3.379.0) and LinqConnect (4.4.717.0).

I have encountered a problem that I can reproduce in a simple test app.
I get an exception "Specified method is not supported" when I try to drill down from an entity through a collection.

Here is my test setup:
Database contains 3 tables: Invoice, LineItem and PartNum.
LineItem has 2 foreign keys, referencing Invoice and PartNum.

In the following code snippet, Query 1 works, but Query 2 throws an exception. Please note that the queries are only
for this test - they are not intended to do anything in a real scenario.

Code: Select all

                using (MySQL3.DataContext3 db = new MySQL3.DataContext3())
                {
                    // Query 1: This works
                    MySQL3.Invoice invoice = (from i in db.Invoices
                                              where i.LineItems.First().ItemQty != 0
                                              select i).FirstOrDefault();
                    if (invoice != null)
                        DisplayMessage(invoice.InvoiceNum.ToString());
                    else
                        DisplayMessage("None found");


                    // Query 2: This throws exception "Specified method is not supported"
                    invoice = (from i in db.Invoices
                               where i.LineItems.First().PartNum != null
                               select i).FirstOrDefault();
                    if (invoice != null)
                        DisplayMessage(invoice.InvoiceNum.ToString());
                    else
                        DisplayMessage("None found");
                }

I think the problem is when we try to drill down from the First() selector to another entity.
Our application has lots of queries of this general form.
The same code works fine in MS Linq.

Am I missing something?

If more information is required, I can provide the test project and database (they are pretty small).

[MariiaI]

Thank you for the report. We have reproduced this issue. We will investigate it more clearly and inform you about the results as soon as possible.

[eheaney]

Has there been any progress on this issue?

[MariiaI]

The investigation is in progress. We will contact you as soon as possible.

[eheaney]

Hello. Is there any movement on this problem? We cannot move forward until we are sure the product will be supported.

[MariiaI]

Unfortunately, there is no timeframe for this issue at the moment.
We will post here if any news are available.

[MariiaI]

The investigation of this issue took long time due to its complexity and some peculiarities, for the same reason, we can't tell any timeframe regarding the fix.
However, there is a workaround: use foreign key properties, which correspond to columns that are involved in the associations, in the query instead of the EntityRef objects.
I.e. use this query (like in your code sample):

Code: Select all

invoice = (from i in db.Invoices
where i.Lineitems.First().PartnumId != null
select i).FirstOrDefault(); 

instead of this:

Code: Select all

invoice = (from i in db.Invoices
where i.Lineitems.First().Partnum != null
select i).FirstOrDefault();