Geek Answers Handbook

Recommend me a suitable LINQ provider (SQL server, complex queries)

I have been using LINQ for SQL and for entities for a while, and I am generally very pleased with them. However, I am aware of their limitations, and in particular, this is becoming a big problem for me. When you execute a complex subquery in the form

MyContext.SomeTable
.Select(item=>new{
    item.SomeProperty1,
    item.SomeProperty2,
    item.NavigationProperty1
        .Select(nav1=> new {// retrieve some properties}), // This triggers a single query as long as don't have more than one subquery
    item.NavigationProperty2
        .Select(nav2=> new {// retrieve some properties}) // This triggers one query PER ROW in the original query
});

The providers I tested with are LINQ TO SQL / LINQ TO objects (and, even worse, devart LINQConnect, which are much worse and generate 1 per line in the first navigation property)

Now I get the generated (pseudo-code):

select t1.a,t1.b,t2.c,t2.d from mytable as t1
join navproperty1table as t2

and 1 million (if there are 1 million results in the first set) of the following queries: select t3.e,t3.f from navproperty2table as t3 where id = X(X changes to an X-query for the next element returned by the first query)

What I want:

select t1.a,t1.b,t2.c,t2.d,t3.e,t3.f from mytable as t1 
join navproperty1table as t2
join navproperty2table as t3

, , 3 , , "" single select ( ). , 20 + 3-6 , 2-5 .

SQL- , , , , , "" , . LINQ , , , , , , 600 , .

"-" LINQ , , , ? ( , , , , foreach , , , n +1 loadwith, , n + 1 , , 1 n + 1 , 1 10 000, 10 000 000 10 000 000 000)

  • ( , , , , , "" ).

PS: , .NET 4.0 Windows 2008 , SQL Server 2008 , , , , , .net, sql .. . - , DB , , -, //DML/ /, , SQL

: , , adventureworks

Contacts
.Select(cont=>new 
{
    cont.EmailAddress,
    cont.EmailPromotion,
    Employees = cont.Employees
        .Select(emp=>new
        {
            emp.Gender,
            emp.HireDate
        }).ToList()
}).ToList()


SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], (
SELECT COUNT(*)
FROM [HumanResources].[Employee] AS [t2]
WHERE [t2].[ContactID] = [t0].[ContactID]
) AS [value]

FROM []. [] AS [t0] LEFT OUTER JOIN [HumanResources]. [] AS [t1] ON [t1]. [ContactID] = [t0]. [ContactID] ORDER BY [t0]. [ContactID], [t1]. [EmployeeID]

     .Select( = >   {       cont.EmailAddress,       cont.EmailPromotion,       Vendors = cont.VendorContacts.Select(vend = > new       {           vend.ContactTypeID,           vend.ModifiedDate       }). ()   }). ToList()

:

SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[ContactTypeID], [t1].[ModifiedDate], (
SELECT COUNT(*)
FROM [Purchasing].[VendorContact] AS [t2]
WHERE [t2].[ContactID] = [t0].[ContactID]
) AS [value]

FROM []. [] AS [t0] LEFT OUTER JOIN []. [VendorContact] AS [t1] ON [t1]. [ContactID] = [t0]. [ContactID] ORDER BY [t0]. [ContactID], [t1]. [VendorID]

( X)

Contacts
.Select(cont=>new 
{
    cont.EmailAddress,
    cont.EmailPromotion,
    Employees = cont.Employees
        .Select(emp=>new
        {
            emp.Gender,
            emp.HireDate
        }).ToList(),
    Vendors = cont.VendorContacts.Select(vend=>new
    {
        vend.ContactTypeID,
        vend.ModifiedDate
    }).ToList()
}).ToList()

( , ):

SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], (
SELECT COUNT(*)
FROM [HumanResources].[Employee] AS [t2]
WHERE [t2].[ContactID] = [t0].[ContactID]
) AS [value], [t0].[ContactID]
FROM [Person].[Contact] AS [t0]
LEFT OUTER JOIN [HumanResources].[Employee] AS [t1] ON [t1].[ContactID] = [t0].[ContactID]
ORDER BY [t0].[ContactID], [t1].[EmployeeID]
GO

-- Region Parameters
DECLARE @x1 Int = 1
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

 -- Region Parameters
DECLARE @x1 Int = 2
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 3
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 4
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 5
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 6
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 7
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 8
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 9
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 10
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

/ :

SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], [t2].[ContactTypeID], [t2].[ModifiedDate] ,[t0].[ContactID]
FROM [Person].[Contact] AS [t0]
LEFT OUTER JOIN [HumanResources].[Employee] AS [t1] ON [t1].[ContactID] = [t0].[ContactID]
LEFT OUTER JOIN [Purchasing].[VendorContact] AS [t2] ON [t2].[ContactID] = [t0].[ContactID]
GO
+5
4

, , , ( SQL ), , , .

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20658&StartAtMessage=0𜜎

, ? , , .

1 : - linq to SQL - linq - devart linqconnect

, - llblgen

/ - Telerik OpenAccess - NHibernate - Mindscape

, ?

0

view

select t1.a,t1.b,t2.c,t2.d,t3.e,t3.f from mytable as t1 join navproperty1table as t2 join navproperty2table as t3

linq-2-sql .

, ,

from x in MyContext.Sometable
Select new { x.a, x.b, x.t2.c, x.t2.d, x.t3.f }

.. , , ( ), .

+1

, NHibernate Fetch (no linq).

(, ThenFetchMany), , NHibernate. O/RM. NHibernate , SQL (linq-to-nhibernate !). 1 + N.

, NHibernate HQL , .

With linq, I think that best of all you can get the required graph of objects in the least possible number of queries.

0
source

You can also do something like this:

var venderContacts= VendorContacts.ToLookup (u =>u.ContactID);
var contracts=Contacts
    .Select(cont=>new 
    {
        cont.EmailAddress,
        cont.EmailPromotion,
        Employees = cont.Employees
            .Select(emp=>new
            {
                emp.Gender,
                emp.HireDate
            }).ToList(),
        Vendors = venderContacts[cont.ContanctID]
    }).ToList();
0
source

More articles:


All Articles