LinqConnect Professional Version 4.1.197 (28-Feb-2013) - incorrect work with SQLite

[AKRRKA]

Hello,

I have a problem again.
Use LinqConnect Professional Version 4.1.197 (28-Feb-2013).
When work with SQLite get error.

Code: Select all

            var sdt = new CrmDemoContextSQLite.CrmDemoDataContextSQLite();
            sdt.Connection.Open();

            // In my project, then calculated value. For simplicity, wrote this in an example.
            bool bIsGeneralUser = true;

            var query = from o in sdt.Orders
                        from c in o.Customers.Where(c => bIsGeneralUser || (c.Name == "test"))
                        select new
                            {
                                o.OrderID,
                                c.Name
                            };

            dataGridView1.DataSource = query;

With MySQL and SQLServer work correctly.

The sample project: DevartSQLiteSample.rar

[MariiaI]

This is a known issue and it is related to a SQLite peculiarity. We plan to fix it, but we can't tell any timeframe at the moment.
As a workaround, you could try doing one of the following:
1) join tables 'Order' and 'Customer' explicitly via the third table 'CustomersOrders', e.g.:

Code: Select all

var col1 = sdt.Orders;
var col2 = from c in col1
           join o in sdt.CustomersOrders on c.OrderID equals o.OrdersOrderID
           select o;

var col3 = from c in col2
           join o in sdt.Customers on c.CustomersCustomerId equals o.CustomerId
           select new {
                       c.Order.OrderID,
                       o.Name
                       };

JIC: to add the intermediate table 'CustomersOrders' to the model, recreate it without selecting the 'Detect Many-To- Many associations' check box.
2) make some changes to the generated SQL script and perform it explicitly via ExecureQuery/ExecuteCommand methods,e.g.:

Code: Select all

SELECT t1.OrderID AS OrderID, J1.Name AS Name
FROM "main".Orders t1
CROSS JOIN ("main".Customers t2
    INNER JOIN "main".Customers_Orders t3 ON t3.Customers_CustomerId = t2.CustomerId) J1
WHERE (t1.OrderID = J1.Orders_OrderID) AND (:p0 OR (J1.Name = :p1))

[AKRRKA]

Yes. I understand, and for so doing. But the project is large. And a lot of places where there are such requests. We'll have to change everything, sometimes complicate requests.
This is not very convenient.
With SQLite has and other problems like these.