var query = from o in context.ORDERS
select new myORDERS() {
ORDERID = o.ORDERID,
ORDERDETAILS = from d in o.ORDERDETAILS select new myORDERDETAILS() {
PRODUCTID=d.PRODUCTID,
},
};
where myORDERS and myORDERDETAILS are my business objects.
If I try to run the query with the Microsoft provider for Sql Server System.Data.SqlClient in sql monitor I have the current query:
SELECT [t0].[ORDERID], [t1].[PRODUCTID], (
SELECT COUNT(*)
FROM [dbo].[ORDERDETAILS] AS [t2]
WHERE [t2].[ORDERID] = [t0].[ORDERID]
) AS [value]
FROM [dbo].[ORDERS] AS [t0]
LEFT OUTER JOIN [dbo].[ORDERDETAILS] AS [t1] ON [t1].[ORDERID] = [t0].[ORDERID]
ORDER BY [t0].[ORDERID], [t1].[PRODUCTID]
If I run the same query with Devart driver for Oracle I obtain the following sql queries:
SELECT t1.PRODUCTID
FROM NORTHWIND.ORDERDETAILS t1
WHERE :np0 = t1.ORDERID
np0 = 11073
SELECT t1.PRODUCTID
FROM NORTHWIND.ORDERDETAILS t1
WHERE :np0 = t1.ORDERID
np0 = 11074
SELECT t1.PRODUCTID
FROM NORTHWIND.ORDERDETAILS t1
WHERE :np0 = t1.ORDERID
np0 = 11075
SELECT t1.PRODUCTID
FROM NORTHWIND.ORDERDETAILS t1
WHERE :np0 = t1.ORDERID
np0 = 11076
SELECT t1.PRODUCTID
FROM NORTHWIND.ORDERDETAILS t1
WHERE :np0 = t1.ORDERID
np0 = 11077
SELECT t1.PRODUCTID
FROM NORTHWIND.ORDERDETAILS t1
WHERE :np0 = t1.ORDERID
np0 = 12000
SELECT t1.PRODUCTID
FROM NORTHWIND.ORDERDETAILS t1
WHERE :np0 = t1.ORDERID
np0 = 12001
This behaviour causes decrease of performance. If I not map the LINQ query in my custom objects the behaviour is the same as Microsoft driver.
Thanks
