Is Async GetDbCount supported?

Posts   
 
    
happyfirst
User
Posts: 215
Joined: 28-Nov-2008
# Posted on: 15-Apr-2015 20:05:16   

4.2 - Adapter.

I have virtual scrolling grids that need a total count. I am using FetchEntityCollectionAsync to get the entities, but I don't see a GetDbCountAsync.

Is this possible?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-Apr-2015 21:49:23   

Use a linq with CountAsync() or queryspec query with FetchScalarAsync<T> to obtain the count asynchronously. The lowlevel api only has some methods ported to async, as the way forward is either linq or queryspec simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 24-Apr-2017 15:08:56   

Hi,

what I noticed is that they produce different SQL. At least when inheritance is in the game. For some that might be important in certain cases. Example:

Inheritance: Entities -> Users -> CompanyUsers Query (LinqPad):


var q = qf.Create().Select(UserFields.Id.Count()).Where(UserFields.Email == "queryspec@llblgen.xyz");

this.AdapterToUse.FetchScalar<int>(q).Dump();

output:


SELECT TOP(@p2) COUNT([LPA_L2].[Id]) AS [Id]
FROM   ( [XYZ].[dbo].[Entities] [LPA_L1]
         LEFT JOIN [XYZ].[dbo].[Users] [LPA_L2]
             ON [LPA_L1].[Id] = [LPA_L2].[Id])
WHERE  (([LPA_L2].[Email] = @p3)
    AND (([LPA_L2].[Id] IS NOT NULL)))

whereas:


var f = new EntityFields2(1) {UserFields.Id};

this.AdapterToUse.GetDbCount(f, new RelationPredicateBucket(UserFields.Email == "lowlevel@llblgen.xyz")).Dump();

produces:


SELECT COUNT(*) AS NumberOfRows
FROM   (SELECT [XYZ].[dbo].[Users].[Id]
        FROM   [XYZ].[dbo].[Users]
        WHERE  ([XYZ].[dbo].[Users].[Email] = @p1)) TmpResult 

simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Apr-2017 16:22:15   

Which llblgen pro version are you using? As I do recall this has been corrected in later versions (as in: inheritance relationships are also appended in GetDbCount())

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 24-Apr-2017 16:40:50   

Otis wrote:

Which llblgen pro version are you using? As I do recall this has been corrected in later versions (as in: inheritance relationships are also appended in GetDbCount())

Using 5.1.3 in LinqPad bound to my Adapter LLBLGen project.

"c:\Users\XYZ\AppData\Local\LINQPad\Drivers\DataContext\4.6\SD.LLBLGen.Pro.LINQPadDriver51 (b094696bc21c000a)\SD.LLBLGen.Pro.ORMSupportClasses.dll" is also 5.1.3

BTW, what would be the correct QuerySpec version of this low-level query:


var f = new EntityFields2(2) {UserFields.Id, UserFields.FirstName};
this.AdapterToUse.GetDbCount(f, new RelationPredicateBucket(UserFields.Email == "lowlevel@llblgen.xyz"), null, false).Dump();

output


SELECT COUNT(*) AS NumberOfRows
FROM   (SELECT DISTINCT [XYZ].[dbo].[Users].[Id],
                        [XYZ].[dbo].[Users].[FirstName]
        FROM   [XYZ].[dbo].[Users]
        WHERE  ([XYZ].[dbo].[Users].[Email] = 'lowlevel@llblgen.xyz' /* @p1 */)) TmpResult


it counts distinct of 2 columns.

Thank you!

UPDATE: I've tried something like:


q = qf.Create().Distinct().Select(UserFields.Id).Where(UserFields.Email == "queryspec@llblgen.xyz");
this.AdapterToUse.FetchScalar<int>(qf.Create().Select(q.CountRow())).Dump();

doesn't return proper result, SQL:


SELECT TOP(1 /* @p3 */) (SELECT COUNT(*) AS [LPAV_]
                 FROM   (SELECT DISTINCT [LPA_L2].[Id]
                         FROM   ( [XYZ].[dbo].[Entities]
                                  LEFT JOIN [XYZ].[dbo].[Users]
                                      ON [XYZ].[dbo].[Entities].[Id] = [XYZ].[dbo].[Users].[Id])
                         WHERE  (([LPA_L2].[Email] = 'queryspec@llblgen.xyz' /* @p1 */)
                             AND (([LPA_L2].[Id] IS NOT NULL)))) [LPA_L3]) AS [LLBLV_1]
FROM   ( [XYZ].[dbo].[Entities] [LPA_L1]
         LEFT JOIN [XYZ].[dbo].[Users] [LPA_L2]
             ON [LPA_L1].[Id] = [LPA_L2].[Id])

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 24-Apr-2017 18:11:40   

Are you speaking about the Left join to the Super Entity?

Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 24-Apr-2017 18:31:34   

Walaa wrote:

Are you speaking about the Left join to the Super Entity?

Basically:

1) generated SQL is different, one does LEFT JOIN with inherited table another one doesn't. Apparently it is supposed to be the same, however, not in my case

2) how to select row count from a query like SELECT COUNT(*) FROM (SELECT DISTINCT Id, FirstName) using QuerySpec

Thank you!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 25-Apr-2017 11:13:25   

Findev wrote:

Walaa wrote:

Are you speaking about the Left join to the Super Entity?

Basically:

1) generated SQL is different, one does LEFT JOIN with inherited table another one doesn't. Apparently it is supposed to be the same, however, not in my case

There's a simple explanation: it doesn't have access to the inheritance information if you pass an EntityFields2() instance: the inheritance info provider is unreachable. It therefore doesn't add any inheritance information. Instead, pass a ResultsetFields() instance with the field, you'll then see it does add the inheritance information and generate the same query, as it does have access to the inheritance information in that case simple_smile

2) how to select row count from a query like SELECT COUNT(*) FROM (SELECT DISTINCT Id, FirstName) using QuerySpec Thank you!

First define the query you want to count, then wrap it with a .Select(Functions.CountRow()).From(realQuery), like so:


[Test]
public void CountOnDistinctQueryTest()
{
    var qf = new QueryFactory();

    using(var adapter = new DataAccessAdapter())
    {
        var q = qf.Create()
                    .Select(Functions.CountRow().As("Total"))
                    .From(qf.Create()
                            .Select(ClerkFields.Id, ClerkFields.JobDescription)
                            .Where(ClerkFields.JobDescription == "pushing paper")
                            .Distinct());
        var results = adapter.FetchScalar<int>(q);
        Assert.AreEqual(2, results);
    }
}

Frans Bouma | Lead developer LLBLGen Pro
Findev
User
Posts: 103
Joined: 08-Dec-2014
# Posted on: 25-Apr-2017 14:19:46   

Hi,

indeed changing to ResultsetFields seems to make a difference simple_smile

Ended up having sync and async versions like:


public override int Count(IRelationPredicateBucket bucket, IEntityFields2 fields = null, bool allowDuplicates = true, SharedDataAccessAdapter sharedDataAccessAdapter = null)
        {
            if (fields != null)
            {
                if (!( fields is ResultsetFields ))
                {
                    throw new ArgumentException( $"Must be of {nameof(ResultsetFields)} type", nameof(fields) );
                }

                if (fields.Count == 0)
                {
                    throw new ArgumentException( "Empty list", nameof(fields) );
                }
            }

            var fieldsToUse = fields ?? EntityFieldsFactory.CreateEntityFieldsObject( GetEntityType() );

            if (sharedDataAccessAdapter?.Adapter != null)
            {
                return sharedDataAccessAdapter.Adapter.GetDbCount(fieldsToUse, bucket, null, allowDuplicates);
            }

            using (var adapter = new TDataAccessAdapterBase())
            {
                return adapter.GetDbCount( fieldsToUse, bucket, null, allowDuplicates);
            }
        }


public override async Task<int> CountAsync(IPredicate predicate, IEntityFields2 fields = null, bool allowDuplicates = true, CancellationToken cancellationToken = default(CancellationToken), SharedDataAccessAdapter sharedDataAccessAdapter = null)
        {
            if (fields?.Count == 0)
            {
                throw new ArgumentException( "Empty list", nameof(fields) );
            }

            var fieldsToUse = fields ?? EntityFieldsFactory.CreateEntityFieldsObject(GetEntityType());

            return
                await
                    QuerySpecHelper.QueryScalarViaFactoryAsync<int>(
                                       qf =>
                                       {
                                           // ReSharper disable once CoVariantArrayConversion
                                           var q = qf.Create<TCommonEntityBase>().Select( fieldsToUse.ToArray() ).Where( predicate );

                                           if (!allowDuplicates)
                                           {
                                               q = q.Distinct();
                                           }

                                           return qf.Create().Select( Functions.CountRow() ).From( q );
                                       },
                                       cancellationToken,
                                       (TDataAccessAdapterBase) sharedDataAccessAdapter?.Adapter )
                                   .ConfigureAwait( false );
        }

Thank you! simple_smile