Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Paged Dynamic Query, adding predicates after its creation
 

Pages: 1
LLBLGen Pro Runtime Framework
Paged Dynamic Query, adding predicates after its creation
Page:1/1 

  Print all messages in this thread  
Poster Message
19252
User



Location:

Joined on:
12-Jul-2019 18:24:08
Posted:
10 posts
# 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)

Quote:

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:

Quote:
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

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


In order to page this seems to work
Quote:

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


Can you help in figuring it out?
Thanks
            
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# 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:
Code:
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:
Code:

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:
Code:

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
Code:

.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:
Code:

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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
19252
User



Location:

Joined on:
12-Jul-2019 18:24:08
Posted:
10 posts
# 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"));" EmbarrassedDissapointed


I take your code

Quote:

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

Quote:

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:

            
Quote:
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

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14531 posts
# Posted on: 04-Oct-2019 01:51:13.  
To add additional Where clauses, just expand on the example provided by Otis, as follows:
Code:
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.


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.