Paged Dynamic Query, adding predicates after its creation

Posts   
 
    
19252
User
Posts: 10
Joined: 12-Jul-2019
# Posted on: 03-Oct-2019 11:19:31   

Hello,

I'm feeling quite dumb, but i cant figure out how to add predicates to a dynamic query.

I have a dynamic query creation (i put a simplifyed query)

var q = qf.Create() .Select(() => new { id= tbl.id.Source("a").ToValue<string>() }) .From(qf.tbl .Select(tbl.id).Distinct().As("a"));

I then would like to 1) obtain the rowcount and then 2) add some Where and 3) page the result

i tried:

var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));

and it seems to work

but then i dont understand how i can add the predicates i would like to do something like

var result1 = adapter.FetchQuery(qf.Create().Select(q.AndWhere(filter.PredicateExpression)));

In order to page this seems to work

var result2 = adapter.FetchQuery(qf.Create().SelectFrom(q).Page(10, 10));

Can you help in figuring it out? Thanks

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 03-Oct-2019 11:50:30   

The problem you're having is that the initial start query is a wrapping query around the actual query you want to augment.

Say I have your query in an example:

using(var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Create().Select(() => new {CustomerId = CustomerFields.CustomerId.Source("a").ToValue<string>()})
              .From(qf.Customer.Select(CustomerFields.CustomerId).As("a"));
    var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
    Assert.AreEqual(91, count);
    q = q.Where(CustomerFields.Country.Source("a").Equal("USA"));
    var results = adapter.FetchQuery(q);
    Assert.AreEqual(13, results.Count);
}

At first glance this should work right? but if we look at the sql generated we see that this isn't going to work:

the count query:


SELECT TOP(1 /* @p2 */) (SELECT COUNT(*) AS [LPAV_]
                 FROM   (SELECT [LPA_a2].[CustomerId]
                         FROM   (SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId]
                                 FROM   [Northwind].[dbo].[Customers]) [LPA_a2]) [LPA_L1]) AS [LLBLV_1]
FROM   [Northwind].[dbo].[Customers]

this works, but the fetch with the where fails:


SELECT [LPA_a1].[CustomerId]
FROM   (SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId]
        FROM   [Northwind].[dbo].[Customers]) [LPA_a1]
WHERE  (([LPA_a1].[Country] = 'USA' /* @p1 */))

This is because the projection you formulated, the


.Select(tbl.id).Distinct().As("a"));

part, only contains the 'id' part, and the predicate expression added to the query thus also can only work with that field. If I change it to also contain the field I need to filter on, it will work:


using(var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Create().Select(() => new {CustomerId = CustomerFields.CustomerId.Source("a").ToValue<string>()})
              .From(qf.Customer.Select(CustomerFields.CustomerId, CustomerFields.Country).As("a"));
    var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
    Assert.AreEqual(91, count);
    q = q.Where(CustomerFields.Country.Source("a").Equal("USA"));
    var results = adapter.FetchQuery(q);
    Assert.AreEqual(13, results.Count);
}

You also see I just append the where to the query, no need to wrap it again in a query with a select.

So in your case, all fields the predicateexpression works on has to be in the projection of the initial query's source, namely here: .Select(tbl.id).Distinct().As("a")); and have to reference the fields by the alias 'a' as the derived table you're creating here is aliased as such.

Frans Bouma | Lead developer LLBLGen Pro
19252
User
Posts: 10
Joined: 12-Jul-2019
# Posted on: 03-Oct-2019 15:58:12   

You are really quick wink thanks

I simplified the query too much ... infact i had all the fields i needed in the select but i didnt reported the ".Source("a").Equal("xxx"));" flushed disappointed

I take your code

using(var adapter = new DataAccessAdapter()) { q = q.Where(CustomerFields.Country.Source("a").Equal("USA")); var results = adapter.FetchQuery(q); }

What if i want to add additional where clauses?

If i add in sequence these predicates the rows returned are not what they should be

IRelationPredicateBucket filter = new RelationPredicateBucket(); filter.PredicateExpression.Add(TRTipiCandidatureFields.Natura.Source("a").Equal("Richiesta"));

List<int> list1 = new List<int>(); list1.Add(1); list1.Add(2);

filter.PredicateExpression.Add(TSCandidatureFields.Idstato.Source("a").In(lista));

q = q.Where(filter.PredicateExpression);

And .. how can i display the executed SQL? Thanks

[EDIT]

I also tried this:

IPredicateExpression where = new PredicateExpression();

            var pred1 = TSCandidatureFields.Idstato.Source("a").In(lista);
            var pred2 = TRTipiCandidatureFields.Natura.Source("a").Equal("Richieste");

            where.Add(pred1);
            where.Add(pred2);

            q = q.Where(where);

this wont output nothing, like adding one predicate after another is not the right thing to do ...

But would like to be able to add then, something like:

if cond1 { add predicate } if cond2 { add another predicate }

thanks in advance

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 04-Oct-2019 01:51:13   

To add additional Where clauses, just expand on the example provided by Otis, as follows:

var qf = new QueryFactory();
var q = qf.Create().Select(() => new { CustomerId = CustomerFields.CustomerId.Source("a").ToValue<string>() })
         .From(qf.Customer.Select(CustomerFields.CustomerId, CustomerFields.Country, CustomerFields.City).As("a"));
var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));

q = q.Where(CustomerFields.Country.Source("a").Equal("USA"));

// Add a conditional statements here if you want...
q = q.Where(CustomerFields.City.Source("a").Equal("Portland"));

var results = adapter.FetchQuery(q);

As he said, just make sure what you are filtering on, exists in the original select list.