This Linq

var passiveAccounts = (from account in Session.Current.Query.All<PassiveAccount>()
                                  select new PassiveAccountEis
                                                 AccountOwner = account.AccountOwner.Id,
                                                 Id = account.Id,
                                                 Name = account.Name,
                                                 Number = account.Number,
                                                 Portfolio = account.Portfolio != null ? (Guid?)account.Portfolio.Id : null,
                                                 ShortDepositorName = account.ShortDepositorName,
                                                 Status = account.Status.Id

is translated to query:

SELECT  [a].[Id] ,
    [a].[Status_Id] ,
    [a].[FullName] ,
    [a].[Name] ,
    [a].[Fund_Id] ,
FROM    [dbo].[Portfolio] [a]

But this

var passiveAccounts = (from account in Session.Current.Query.All<PassiveAccount>()
                                       .Prefetch(a => a.Status)
                                       .Prefetch(a => a.AccountOwner)
                                       .Prefetch(a => a.Portfolio)
                       select Mapper.Map<PassiveAccount, PassiveAccountEis>(account)).ToList();

is translated to many queries. Three sql query on Status, AccountOwner, Portfolio for each record in PassiveAccount.

Here is a configuration of Entity to Guid mapping:

    private sealed class EntityBaseToGuidConverter : ITypeConverter<EntityBase, Guid>
        private const string EntityIsNull = "Не задана сущность для преобразования в Guid";

        public Guid Convert(EntityBase source)
            if (source == null)
                throw new InvalidOperationException(EntityIsNull);

            return source.Id;

    private sealed class EntityBaseToNullableGuidConverter : ITypeConverter<EntityBase, Guid?>
        public Guid? Convert(EntityBase source)
            return source != null ? (Guid?)source.Id : null;

why is it so?

asked Jan 26 '12 at 09:16

Ibanez's gravatar image


edited Jan 26 '12 at 09:18

2 Answers:

Prefetch API always uses multiple queries sent in batches, i.e. that's by design. We use this approach to avoid 3 issues:

1) Inefficient query plans. Usually the same result is achieved with JOIN, but query with multiple JOINs is probably the most complex case for query optimizer. So each additional JOIN significantly increases the probability of getting wrong query plan (i.e. a quite slow query).

That's why we send simple queries with predictable plans.

2) Much larger result set: each 1-n JOIN implies each of left rows will be n times duplicated. So two of such joins change the size of result set dramatically.

3) Our prefetch API requests only the data that isn't fetched yet; the same isn't achievable with JOIN-based approach.

answered Jan 31 '12 at 04:49

Alex%20Yakunin's gravatar image

Alex Yakunin

edited Jan 31 '12 at 04:49

You don't understand the question. Prefetch APi works well. My bad. I should present to you following query as example.

var passiveAccounts = (from account in Session.Current.Query.All<passiveaccount>()
select Mapper.Map<passiveaccount, passiveaccounteis="">(account)).ToList();

Additional queries raised executing property mapping code:

    public Guid Convert(EntityBase source)
        if (source == null)
            throw new InvalidOperationException(EntityIsNull);

        return source.Id;
(Jan 31 '12 at 05:18) Ibanez Ibanez's gravatar image

if PassiveAccount entity has e.g. 500 elements then 1500 queries appears(500 on Status, 500 on AccountOwner, 500 on Portfolio). Status, AccountOwner, Portfolio - reference fields of PassiveAccount

(Jan 31 '12 at 05:26) Ibanez Ibanez's gravatar image


Looks like we've finally found a bug in Prefetch pipeline that cause such non-optimal behavior. Will fix soon.

answered Feb 24 '12 at 02:55

Dmitri%20Maximov's gravatar image

Dmitri Maximov

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview


Once you sign in you will be able to subscribe for any updates here



Asked: Jan 26 '12 at 09:16

Seen: 2,915 times

Last updated: Feb 24 '12 at 02:55

Related questions

powered by OSQA