After trying to create simple project for demonstration I realized it's a little more complicated. It's probably again concerned with compiled query cache. However, after disabling it problem still persists.
I use latest SQLite dotConnect 4.0.25.
Finally, I sent you project to simulate it.
I describe it here too. Tested with following database schema:
Code: Select all
CREATE TABLE [order] (
[id_order] INT NOT NULL,
CONSTRAINT [sqlite_autoindex_order_1] PRIMARY KEY ([id_order]));
CREATE TABLE [order_item] (
[id_order] INT NOT NULL,
[amount] int NOT NULL,
CONSTRAINT [sqlite_autoindex_order_item_1] PRIMARY KEY ([id_order]));
INSERT INTO [order](id_order) VALUES(1);
INSERT INTO [order_item](id_order, amount) VALUES(1, 6);
Then I called this code:
Code: Select all
01 public void GetAmount(int idOrder, int? minAmount)
02 {
03 using (DBContext db = new DBContext())
04 {
05 var query = from o in db.Orders
06 from i in db.OrderItems.Where(i => i.IdOrder == i.IdOrder).DefaultIfEmpty()
07 where o.IdOrder == idOrder
08 select new { i.Amount, o.IdOrder };
09
10 if (minAmount.HasValue)
11 query = query.Where(q => q.Amount > minAmount);
12
13 foreach (var i in query)
14 Console.Write("Id order: " + i.IdOrder + "\t Amount: " + i.Amount);
15 }
16
17 GetAmount(1, 1);
18 GetAmount(1, null);
The result should be the same for both calling of method GetAmount (Lines 17 and 18). However it is:
Id_order: 1 Amount: 6
Id_order: 6 Amount: 1
If the method is called only once the result is always correct. But in the example above, in second calling lines 13 and 14 are omitted which constructs different sql query with different order of columns.
It causes that values are switched.
