CODEWITHSUNDEEP
c#sql-serverdatabaselinq-to-sql
AgentFire
AgentFire

Reputation: 9780

LINQ-to-SQL: Return an "array of rows" property (or column) per row

A simple query for Groups results in another query for every of its results:

using (var db = new DataClasses1DataContext())
{
    db.Log = Console.Out;

    var query = from g in db.Groups
                let stats = from s in g.GroupCountryStats
                            select new
                            {
                                s.CountryId,
                                s.MembersCount
                            }
                select new
                {
                    g.ObjectId,
                    g.Name,
                    Stats = stats.ToArray()
                };

    var single = query.First(); // Take(2) would result in 2 extra calls, 3 in 3 and so on...
}

Console output (the Linq-to-Sql provider log):

SELECT TOP (1) [t0].[ObjectId] AS [ObjectId], [t0].[Name]
FROM [dbo].[Groups] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.7.3056.0

SELECT [t0].[CountryId] AS [CountryId], [t0].[MembersCount]
FROM [dbo].[GroupCountryStats] AS [t0]
WHERE [t0].[GroupId] = @x1
-- @x1: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.7.3056.0

Press any key to continue . . .

I would like to retrieve all the data in one query, instead of N+1 queries.

I tried using DataLoadOptions, with LoadWith<Group>(T=>T.GroupCountryStats) but the result is unchanged. The Linq2Sql still makes more than 1 call to the database.

How to avoid that and receive everything I need in a single call?

Upvotes: 0

Views: 284

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109119

This is a bug (or a shortcoming at best) in LINQ-to-SQL. The query without First or Take translates into one decent SQL query containing an OUTER JOIN. But of course it'll give you all groups.

When LINQ-to-SQL is faced with statements that limit the number of results it seems to be programmed to apply these to the root entity only and then to query the adhering entities separately.

The funny thing is: this doesn't happen when Skip and Take are combined. So, as a hack, you could use Skip(0).take(x), for example:

var results = query.Skip(0).Take(3).ToList();

If you need one result you have to do:

var results = query.Skip(0).Take(1).AsEnumerable().First();

Without .AsEnumerable() will again relapse into 1 + 1 queries. (I assume the SQL translation of First takes precedence in the query shape).

It's a dirty hack that's necessary to fight leaky abstractions of IQueryable. Make sure that, if you choose to use it, you comment it well in the code.

A final comment: maybe you should consider turning to a more advanced ORM like Entity Framework.

Upvotes: 1

Related Questions