System.Data.SQLite

Hi,

I'm having a spot of trouble with the Linq-To-Entities version of SQLite. If I use the "Include()" method to eager fetch a related property, it normally works fine. However, if I include a "let" statement in the query, the "Include()" call is ignored (and this can be verified by viewing the SQL in the ToTraceString() method.)

The workaround for the moment seems to be to pre-load the related entity into the data context... the property is then populated correctly. Below is some source code and I've attached a sample project to demonstrate this problem. Any help is much appreciated.

Regards,

Trev.

Code:

Hows it work on Sql Server?  Properly?  Can you show me the TraceString() for both cases on Sql Server?

I tried to reproduce the table schema on Sql Server and got several errors.

First, these foreign keys failed because [OrderId] and [SupplierId] in [Supplier_Orders] are not unique:
ALTER TABLE [Order] ADD CONSTRAINT [FK_Order_OrderId_Supplier_Orders_OrderId] FOREIGN KEY ([OrderId]) REFERENCES [Supplier_Orders] ([OrderId])

Next, this foreign key references a column that doesn't exist, but since SQLite only parses and doesn't enforce, I suspect it was a schema change somewhere that allowed it to happen:

ALTER TABLE [Supplier_Orders] ADD CONSTRAINT [FK_Supplier_Orders_OrderId_Order_Id] FOREIGN KEY ([OrderId]) REFERENCES [Order] ([Id])

So I'm not sure how you want to fix those errors, but I suspect things will work better once you have.

One last thing ... when I did correct two of the instances where columns had been renamed, then I recreated the EDMX file, I got the following messages:

Message 1 The relationship 'FK_Customer_0' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded. E:\src\SQLiteEFIncludeProblem\SQLiteEFIncludeProblem\Model1.edmx 0
Message 2 The relationship 'FK_Order_0' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded. E:\src\SQLiteEFIncludeProblem\SQLiteEFIncludeProblem\Model1.edmx 0
Message 3 The relationship 'FK_Supplier_0' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded. E:\src\SQLiteEFIncludeProblem\SQLiteEFIncludeProblem\Model1.edmx 0

So I suspect this is why your let statement isn't working -- the association between suppliers and orders bridged by the suppler_order table isn't being made.

I finally got this into Sql Server by simplifying the schema and removing the invalid foreign keys.  However, when I ran your code against Sql Server, I got the exact same results and behavior as with SQLite:

-- Original table schema
CREATE TABLE [Customer] (
[CustomerId] int PRIMARY KEY NOT NULL,
[Name] varchar(50)
);

-- Original table schema
CREATE TABLE [Order] (
[OrderId] int PRIMARY KEY NOT NULL,
[CustomerId] int,
[OrderTotal] float,
CONSTRAINT [FK_Order_CustomerId_Customer_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([CustomerId])
);

-- Original table schema
CREATE TABLE [Supplier] (
[SupplierId] int PRIMARY KEY NOT NULL,
[Name] varchar(50)
);