Question

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
                                             }).ToList();

is translated to query:

SELECT  [a].[Id] ,
    [a].[Status_Id] ,
    [a].[FullName] ,
    [a].[Name] ,
    [a].[Fund_Id] ,
    [a].[ManagementCompany_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?

Answer 1

0

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 at 04:49

Alex Yakunin
2916●1●11

edited Jan 31 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 at 05:18) Ibanez

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 at 05:26) Ibanez

Subscription:

Related questions