Just when I thought I was hitting the home stretch, I came across an
aggregate query test that I'm not sure how to solve. LINQ generated the
following query programmatically:
-- Begin auto-generated query
SELECT
1 AS [C1],
[Project2].[ProductID] AS [ProductID],
[Project2].[ProductName] AS [ProductName],
[Project2].[QuantityPerUnit] AS [QuantityPerUnit],
[Project2].[UnitPrice] AS [UnitPrice],
[Project2].[UnitsInStock] AS [UnitsInStock],
[Project2].[UnitsOnOrder] AS [UnitsOnOrder],
[Project2].[ReorderLevel] AS [ReorderLevel],
[Project2].[Discontinued] AS [Discontinued],
[Project2].[CategoryID] AS [CategoryID],
[Project2].[SupplierID] AS [SupplierID]
FROM ( SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued],
(SELECT
Count([Project1].[C1]) AS [A1]
FROM ( SELECT
1 AS [C1]
FROM [Order Details] AS [Extent2]
LEFT OUTER JOIN [Orders] AS [Extent3] ON
[Extent2].[OrderID] = [Extent3].[OrderID]
LEFT OUTER JOIN [Customers] AS [Extent4] ON
[Extent3].[CustomerID] LIKE [Extent4].[CustomerID]
INNER JOIN [Suppliers] AS [Extent5] ON
[Extent4].[Country] LIKE [Extent5].[Country]
WHERE ([Extent1].[SupplierID] =
[Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID])
) AS [Project1]) AS [C1]
FROM [Products] AS [Extent1]
) AS [Project2]
WHERE [Project2].[C1] > 2
-- End autogenerated query
The error SQLite throws is "no such column: Extent1.ProductID" and I believe
it's in the inner WHERE clause:
WHERE ([Extent1].[SupplierID] =
[Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID])
I can of course supply a schema, but I think it may be irrelevant and have
more to do with the fact that it's got some inner subqueries referencing an
outer object?
Since the SQL is autogenerated, I have limited control over it. The actual
LINQ query was:
var query = from p in db.Products
where p.Order_Details.Count(od => od.Orders.Customers.Country
== p.Suppliers.Country) > 2
select p;
Aside from this little issue involving aggregates, SQLite is working nicely
with Microsoft's new Entity Framework.
Robert Simpson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
