How do I write a Queryspec query to get a simple RowCount?

This code works...

            var queryFactory = new QueryFactory();

            var query = queryFactory.Create()
                                    .Where(UserFields.UserLevel == PrimaryPermissionGroups.SuperUser)
                                    .Select(UserFields.ID)
                                    .CountRow();

            int superUserCount;

            using (var adapter = new DataAccessAdapter())
            {
                superUserCount = adapter.FetchScalar<int>(queryFactory.Create().Select(query));
            }

producing this SQL...

DECLARE @p1 TinyInt; SET @p1='1'
DECLARE @p3 BigInt; SET @p3='1'

SELECT
  TOP(@p3) (
SELECT
  COUNT(*) AS [LPAV_] 
FROM
  (
SELECT
  [TIPS].[dbo].[User].[ID] 
FROM
  [TIPS].[dbo].[User]   
WHERE
  ( ( [TIPS].[dbo].[User].[UserLevel] = @p1))) [LPA_L1]) AS [LLBLV_1] 
FROM
  [TIPS].[dbo].[User]  

but it seems a little verbose, requires knowledge of a PK field (ideally I'd like to write a method that just accepts a Predicate and returns an int), and needs two calls to QueryFactory to produce the verbose SQL

The optimum SQL is

DECLARE @p1 TinyInt; SET @p1='1'

SELECT
  COUNT(*) AS [LPAV_] 
FROM
  [TIPS].[dbo].[User]   
WHERE
  [TIPS].[dbo].[User].[UserLevel] = @p1

but every time I try to write a QuerySpec query that uses CountRow(), it becomes a ScalarQueryExpression and no method on Adapter seems to accept that, not even FetchScalar!

Is there a way to write a QuerySpec query that produces this SQL?

Could an overload be written on DataAccessAdapter.FetchScalar that accepts a ScalarQueryExpression and just returns it value?

Another reason why I don't like the original code and the SQL it generates - if there are no rows then it returns null and blows up converting this to int.

Just found this now we are using a clean (non imported) database with lots of empty tables.

Will have to go and find all similar usages and change the result type to int? and stick a "?? 0" at the end.

Please check the example in Fetching Scalar queries

That's where I cobbled together the query I have but I still don't like it for the reasons above.

Let me request a new feature then:

DataAccessDataAdapter.CountRows[Big](Predicate filter)

usage:

var intResult = adapter.CountRows(CustomerFields.Country=="Germany")

I think the Field can be extracted from the Predicate and that will also give the Table involved.

Generated SQL:

DECLARE @p1 nvarchar(15); SET @p1='Germany'

SELECT
COUNT(*)
FROM
[Northwind].[dbo].[Customers] 
WHERE
[Northwind].[dbo].[Customers].[Country] = @p1

This is very clear and will return 0 when no matching rows are found.

simmotech wrote:

That's where I cobbled together the query I have but I still don't like it for the reasons above.

Let me request a new feature then:

DataAccessDataAdapter.CountRows[Big](Predicate filter)

usage:

var intResult = adapter.CountRows(CustomerFields.Country=="Germany")

I think the Field can be extracted from the Predicate and that will also give the Table involved. Generated SQL:

DECLARE @p1 nvarchar(15); SET @p1='Germany'

SELECT
COUNT(*)
FROM
[Northwind].[dbo].[Customers] 
WHERE
[Northwind].[dbo].[Customers].[Country] = @p1

This is very clear and will return 0 when no matching rows are found.

That method is already there, called GetDbCount(fields, filter) and overloads. Your proposal is limited to a narrow scenario, so I'm not going to add that; there's already a lot of different ways to get scalars out of the DB, I'm not going to add a very specific one to that if there's already enough to get the results with.

Your original query:

SELECT
COUNT(*) AS [LPAV_]
FROM
[TIPS].[dbo].[User]
WHERE
[TIPS].[dbo].[User].[UserLevel] = @p1

can easily be done like this:

var query = queryFactory.User
                                    .Where(UserFields.UserLevel == PrimaryPermissionGroups.SuperUser)
                                    .Select(Functions.CountRow());
var superUserCount = adapter.FetchScalar<int?>(query);

this makes superUserCount a nullable int

Example:

var qf = new QueryFactory();
var q = qf.Customer
                .Where(CustomerFields.Country == "UK")
                .Select(Functions.CountRow());
            
// alternative
// var q = qf.Create()
//              .Select(Functions.CountRow())
//              .From(qf.Customer.As("C"))
//              .Where(CustomerFields.Country.Source("C") == "UK");

var c = new DataAccessAdapter().FetchScalar<int>(q);

sql:

Executed Sql Query: 
    Query: SELECT TOP 1 COUNT(*) AS [CF] FROM [Northwind].[dbo].[Customers]   WHERE ( ( [Northwind].[dbo].[Customers].[Country] = @p1))
    Parameter: @p1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "UK".

what you wanted.

There may well be lots of ways to get scalars out of a database. All of them seem weird and verbose to me for such a simple thing

GetDbCount(fields, filter)

RelationPredicateBucket filter = new RelationPredicateBucket(CustomerFields.Country == "France");
filter.Relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId);
int amount = 0;
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    amount = (int)adapter.GetDbCount(new OrderEntityFactory().CreateFields(), filter, null, false);
}

Way too much code needed and, according to the sample, needs a cast at the end of it (actually my intellisense says it returns int, so may that cast is not needed). Also, I need to know the EntityFactory. I don't, I only have a Predicate.

var query = queryFactory.User
                                    .Where(UserFields.UserLevel == PrimaryPermissionGroups.SuperUser)
                                    .Select(Functions.CountRow());
var superUserCount = adapter.FetchScalar<int?>(query);

This is better SQL wise, but still way requires extra code and returns a nullable so I have to stick ?? 0 after it (why? COUNT(*) never returns null). And again, it requires knowing the Entity to which the predicate belongs.

As an aside, the documentation says this...

Queries can be converted to scalar queries. This can be necessary when a query is guaranteed to return a single value and that value has to be used in e.g. a predicate. To convert a query to a scalar query, use any of the following methods

Append the query with .CountRow() (or CountRowBig()). This will convert the query in a SELECT COUNT() FROM query construct. For EntityQuery<T> instances, it will simply replace the original entity projection with a COUNT(). For DynamicQueries, it will wrap the query.

Every time I tried use CountRow(), the query becomes a ScalarQuery which isn't a DynamicQuery and so rejected by FetchScalar.

Anyway, the suggestion I made was just that - I am more than happy to hide the implementation away myself by adding a method to my DataAccessAdapter partial class.

But how can I write my .CountRows(Predicate filter) method using only the information contained within the passed-in Predicate?

Please realize, I'm not going to add your suggestion, simply because it's too narrow and it already has equivalents. That you can't use these because you have a 'predicate' and nothing else, is a requirement of your code, not a requirement of our interface: in that line we can add a lot of extra methods, all taking a small bit of info and try to build a query out of them, but a predicate is not a query.

simmotech wrote:

GetDbCount(fields, filter)

RelationPredicateBucket filter = new RelationPredicateBucket(CustomerFields.Country == "France");
filter.Relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId);
int amount = 0;
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    amount = (int)adapter.GetDbCount(new OrderEntityFactory().CreateFields(), filter, null, false);
}

Way too much code needed and, according to the sample, needs a cast at the end of it (actually my intellisense says it returns int, so may that cast is not needed). Also, I need to know the EntityFactory. I don't, I only have a Predicate.

Count() has to run over something. If you have the predicate, and you know it's a FieldCompareValuePredicate, you can also create a new EntityFields2 object, and add the field there. Yes, this is also verbose, but so is the query you actually want to run: it's not a single line query, it's a select over which you want to run the count.

var query = queryFactory.User
                                    .Where(UserFields.UserLevel == PrimaryPermissionGroups.SuperUser)
                                    .Select(Functions.CountRow());
var superUserCount = adapter.FetchScalar<int?>(query);

This is better SQL wise, but still way requires extra code and returns a nullable so I have to stick ?? 0 after it (why? COUNT(*) never returns null). And again, it requires knowing the Entity to which the predicate belongs.

You can also specify 'int' instead of int? and then you don't have to specify the null check.

In all honesty, that you only have a predicate and nothing else, is not something we can work with: why do you only have a predicate? A predicate is not a query, so you want us to create a full query from a predicate, but that doesn't make sense for our interface. If you need to, write a helper method which does create a full query from your predicate as we can't add that, it makes no sense for our interface.

As an aside, the documentation says this...

Queries can be converted to scalar queries. This can be necessary when a query is guaranteed to return a single value and that value has to be used in e.g. a predicate. To convert a query to a scalar query, use any of the following methods

Append the query with .CountRow() (or CountRowBig()). This will convert the query in a SELECT COUNT() FROM query construct. For EntityQuery<T> instances, it will simply replace the original entity projection with a COUNT(). For DynamicQueries, it will wrap the query.

Every time I tried use CountRow(), the query becomes a ScalarQuery which isn't a DynamicQuery and so rejected by FetchScalar.

But you can add it to a Select call on a dynamicquery and then you can fetch it. FetchScalar simply returns the first value of the first row

But how can I write my .CountRows(Predicate filter) method using only the information contained within the passed-in Predicate?

You need a field, so you can add it to an EntityFields2 object which you pass to GetDbCount. If you know the predicate is a FieldCompareValue predicate you can pull it from the predicate. In the framework there's an internal class, (in GraphTraversers) called SourceFieldFinder. It's very small:

namespace SD.LLBLGen.Pro.ORMSupportClasses
{
    /// <summary>
    /// Class which traverses a set of fields and their expressions and finds all fields which are actually mapped fields, so which have their ContainingObjectName
    /// set. These fields can then be used to be the containing field object for an expression instead of an empty expression field which doesn't have a mapping.
    /// This is necessary as llblgen pro doesn't have a 'source' definition, it determines that on the fly, which can go wrong if the projection has just expression
    /// fields and there are no relations specified. 
    /// </summary>
    internal class SourceFieldFinder : QueryApiObjectTraverser
    {
        #region Class Member Declarations
        private readonly List<IEntityFieldCore> _fieldsFound;
        #endregion

        /// <summary>
        /// Initializes a new instance of the <see cref="SourceFieldFinder"/> class.
        /// </summary>
        internal SourceFieldFinder()
        {
            _fieldsFound = new List<IEntityFieldCore>();
        }


        /// <summary>
        /// Traverses the specified field and related objects.
        /// </summary>
        /// <param name="field">The field.</param>
        public override void Traverse(IEntityFieldCore field)
        {
            if(field == null)
            {
                return;
            }
            if(field.ContainingObjectName.Length > 0)
            {
                _fieldsFound.Add(field);
            }
            base.Traverse(field);
        }


        #region Class Property Declarations
        /// <summary>
        /// Gets the fields found.
        /// </summary>
        internal List<IEntityFieldCore> FieldsFound
        {
            get { return _fieldsFound; }
        }
        #endregion
    }
}

Make a public variant and use it to traverse the predicate, by doing: var t = new YourTraverser(); t.Traverse(predicate);

t.FieldsFound will now have the fields it ran into while traversing the predicate, predicate expression or whatever predicate you passed in. Add the first to the EntityFields2 object and you can build the GetDbCount call.

This might sound a tremendous amount of work for something simple, but do realize: you only have a fragment of the actual query you want to run and need to distillate the rest out of that fragment. If you already would know that info, the work would be small: a count query as I described is easy to write.

Anyway, the suggestion I made was just that - I am more than happy to hide the implementation away myself by adding a method to my DataAccessAdapter partial class.

Well, I did say I was more than happy to add this myself. I don't mean to piss you off with everything I write.

Thanks for the info - that was the bit I was missing.

Cheers Simon

If you would have pissed me off, I wouldn't have replied at all, Simon . I just want to avoid long debates why a feature isn't added while it's perfect for that person and his project.