Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Translate not exists to
 

Pages: 1
LLBLGen Pro Runtime Framework
Translate not exists to
Page:1/1 

  Print all messages in this thread  
Poster Message
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 29-May-2012 10:59:53.  
I have a structure like this:

Code:
Customer 1->n Invoice 1->n InvoiceLine -> n->1 InvoiceLineType

I'm adding object aliases as I want to be able to select all customers that DONT'T have certain invoice line types, this sample is only for 1, but it should be mutiple, that's why I added aliases in this code:

I'm trying to translate this query to a FieldCompareSetPredicate but stranded somewhere:

Code:
SELECT DISTINCT *
FROM dbo.Customer AS K INNER JOIN
     dbo.Invoice AS B ON K.CustomerId = B.CustomerId INNER JOIN
     dbo.InvoiceLine AS FR ON B.InvoiceID = FR.InvoiceId AND NOT EXISTS
         (SELECT        InvoiceLineType
         FROM            dbo.InvoiceLine AS FR2
         WHERE        (InvoiceType = @p1) AND (B.InvoiceID = InvoiceId))


It seems simple but can't find the right way to do it, I got this far but produces the wrong query:

Code:
Dim LineType As Int16 = 1
Dim Count As Int16 = 1
relationPredicateBucket.Relations.Add(CustomerEntity.Relations.InvoiceEntityUsingInvoiceId)

Dim relation As IEntityRelation = relationPredicateBucket.Relations.Add(InvoiceEntity.Relations.InvoiceLineEntityUsingInvoiceId, String.Format("InvoiceLine{0}", Count), JoinHint.Inner)
relation.CustomFilter = New PredicateExpression( _
    New FieldCompareSetPredicate( _
        InvoiceFields.InvoiceId, Nothing, _
        InvoiceLineFields.InvoiceId.SetObjectAlias(String.Format("InvoiceLine{0}", Count)), Nothing, _
        SetOperator.Exist, New PredicateExpression(InvoiceLineFields.InvoiceLineType.SetObjectAlias(String.Format("Inner{0}", Count)) = LineType), _
        New RelationCollection(InvoiceEntity.Relations.InvoiceLineEntityUsingInvoiceId, String.Format("InnerInvoiceLine{0}", Count), JoinHint.InnerInvoiceLine), True) _
    )

    
Any hints?

I'm using LLBLGen 3.5 adapter templates for .NET 3.5...

Thanks!
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14615 posts
# Posted on: 29-May-2012 19:47:36.  
Would the following Query be easier:
Code:
SELECT * FROM Customer
WHERE Id NOT IN
(
SELECT Id FROM Customer K
INNER JOIN
     Invoice AS B ON K.CustomerId = B.CustomerId
INNER JOIN
     InvoiceLine AS FR ON B.InvoiceID = FR.InvoiceId
WHERE
     FR.InvoiceType == @p1)
)

Ths should be easier to implement, please tell me you have problems implementing it.


  Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 30-May-2012 10:55:27.  
Walaa wrote:
Would the following Query be easier:
Code:
SELECT * FROM Customer
WHERE Id NOT IN
(
SELECT Id FROM Customer K
INNER JOIN
     Invoice AS B ON K.CustomerId = B.CustomerId
INNER JOIN
     InvoiceLine AS FR ON B.InvoiceID = FR.InvoiceId
WHERE
     FR.InvoiceType == @p1)
)

Ths should be easier to implement, please tell me you have problems implementing it.


Thanks, but why is that easier, I still don't know how to use the FieldCompareSetPredicate in the right way?
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14615 posts
# Posted on: 30-May-2012 22:49:14.  
This is easier as you don't have to filter on the JOIN.

Code should look like the following (not tested).
Quote:
var relations = new RelationsCollection();
relations.Add(CustomerEntity.Relations.Invoices);
relations.Add(InvoiceEntityEntity.Relations.InvoiceLine);

bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
    CustomerFields.CustomerID, null, CustomerFields.CustomerID, null,
    SetOperator.In, (InvoiceLineFields.InvoiceTypeID == xx), relations), false);


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38016 posts
# Posted on: 31-May-2012 12:02:02.  
Additionally (I think the simpler the query, the better, so try Walaa's query first), you could use:
Code:
Dim LineType As Int16 = 1
Dim Count As Int16 = 1
relationPredicateBucket.Relations.Add(CustomerEntity.Relations.InvoiceEntityUsingInvoiceId, "B")
Dim relation As IEntityRelation = relationPredicateBucket.Relations.Add(
    InvoiceEntity.Relations.InvoiceLineEntityUsingInvoiceId, "B", "FR", JoinHint.Inner)
Dim innerPredicate As PredicateExpression = New PredicateExpression(InvoiceLineFields.InvoiceLineType.SetObjectAlias("FR2") = LineType)
innerPredicate.AddWithAnd(InvoiceFields.InvoiceID.SetObjectAlias("B") = InvoiceLineFields.InvoiceId.SetObjectAlias("FR2")
relation.CustomFilter = New PredicateExpression( _
    New FieldCompareSetPredicate( _
        InvoiceFields.InvoiceId, Nothing, _
        InvoiceLineFields.InvoiceId.SetObjectAlias("FR2"), Nothing, _
        SetOperator.Exist, _
        innerPredicate, Nothing, True) _
    )


(not tested) which I think reflects what you wrote in SQL in the startpost.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 31-May-2012 12:05:44.  
Otis wrote:
Additionally (I think the simpler the query, the better, so try Walaa's query first), you could use:
Code:
Dim LineType As Int16 = 1
Dim Count As Int16 = 1
relationPredicateBucket.Relations.Add(CustomerEntity.Relations.InvoiceEntityUsingInvoiceId, "B")
Dim relation As IEntityRelation = relationPredicateBucket.Relations.Add(
    InvoiceEntity.Relations.InvoiceLineEntityUsingInvoiceId, "B", "FR", JoinHint.Inner)
Dim innerPredicate As PredicateExpression = New PredicateExpression(InvoiceLineFields.InvoiceLineType.SetObjectAlias("FR2") = LineType)
innerPredicate.AddWithAnd(InvoiceFields.InvoiceID.SetObjectAlias("B") = InvoiceLineFields.InvoiceId.SetObjectAlias("FR2")
relation.CustomFilter = New PredicateExpression( _
    New FieldCompareSetPredicate( _
        InvoiceFields.InvoiceId, Nothing, _
        InvoiceLineFields.InvoiceId.SetObjectAlias("FR2"), Nothing, _
        SetOperator.Exist, _
        innerPredicate, Nothing, True) _
    )


(not tested) which I think reflects what you wrote in SQL in the startpost.


Thanks Walaa and Otis, will have a look tonight (first opportunity) if I can get it running...


Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38016 posts
# Posted on: 31-May-2012 12:10:22.  
It's a nice query for query spec as well, I'll see if I can rewrite it in queryspec as well, as that should be easier on the eyes Regular Smiley

Code:

var qf = new QueryFactory();
var q = qf.Customer
            .From(QueryTarget.InnerJoin(qf.Order.As("B")).On(CustomerFields.CustomerId == OrderFields.CustomerId.Source("B"))
                .InnerJoin(qf.OrderDetail.As("FR1"))
                    .On((OrderFields.OrderId.Source("B") == OrderDetailFields.OrderId.Source("FR1"))
                        .AndNot(qf.OrderDetail
                                    .CorrelatedOver(OrderDetailEntity.Relations.OrderEntityUsingOrderId, string.Empty, "B")
                                .Any(OrderDetailFields.ProductId == 2)
                                )));


I used filtering on ProductId, but you get the idea.

Note: when I tried to write this, I ran into a silly issue with aliasing the inner OrderDetails, it apparently didn't propagate the alias properly causing a problem. Not aliasing the inner orderdetails (it's unnecessary here) worked. We'll look into this glitch of course.

(edit): 'TargetAs' should be used instead of As, as the .Any() + the correlatedover create a subquery and the alias is used for that subquery. Alternative:
Code:

var qf = new QueryFactory();
var q = qf.Customer
            .From(QueryTarget.InnerJoin(qf.Order.As("B")).On(CustomerFields.CustomerId == OrderFields.CustomerId.Source("B"))
                .InnerJoin(qf.OrderDetail.As("FR1"))
                    .On((OrderFields.OrderId.Source("B") == OrderDetailFields.OrderId.Source("FR1"))
                        .AndNot(qf.OrderDetail.TargetAs("FR2")
                                    .CorrelatedOver(OrderDetailEntity.Relations.OrderEntityUsingOrderId, "FR2", "B")
                                                                    .Any(OrderDetailFields.ProductId.Source("FR2") == 2)
                                )));
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 06-Jun-2012 10:36:00.  
Otis wrote:
It's a nice query for query spec as well, I'll see if I can rewrite it in queryspec as well, as that should be easier on the eyes Regular Smiley

Code:

var qf = new QueryFactory();
var q = qf.Customer
            .From(QueryTarget.InnerJoin(qf.Order.As("B")).On(CustomerFields.CustomerId == OrderFields.CustomerId.Source("B"))
                .InnerJoin(qf.OrderDetail.As("FR1"))
                    .On((OrderFields.OrderId.Source("B") == OrderDetailFields.OrderId.Source("FR1"))
                        .AndNot(qf.OrderDetail
                                    .CorrelatedOver(OrderDetailEntity.Relations.OrderEntityUsingOrderId, string.Empty, "B")
                                .Any(OrderDetailFields.ProductId == 2)
                                )));


I used filtering on ProductId, but you get the idea.

Note: when I tried to write this, I ran into a silly issue with aliasing the inner OrderDetails, it apparently didn't propagate the alias properly causing a problem. Not aliasing the inner orderdetails (it's unnecessary here) worked. We'll look into this glitch of course.

(edit): 'TargetAs' should be used instead of As, as the .Any() + the correlatedover create a subquery and the alias is used for that subquery. Alternative:
Code:

var qf = new QueryFactory();
var q = qf.Customer
            .From(QueryTarget.InnerJoin(qf.Order.As("B")).On(CustomerFields.CustomerId == OrderFields.CustomerId.Source("B"))
                .InnerJoin(qf.OrderDetail.As("FR1"))
                    .On((OrderFields.OrderId.Source("B") == OrderDetailFields.OrderId.Source("FR1"))
                        .AndNot(qf.OrderDetail.TargetAs("FR2")
                                    .CorrelatedOver(OrderDetailEntity.Relations.OrderEntityUsingOrderId, "FR2", "B")
                                                                    .Any(OrderDetailFields.ProductId.Source("FR2") == 2)
                                )));


Thanks guys, Walaa's first solution actually worked fine, it's indeed the most simple solution....

Will have a look at the QuerySpec example too


Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Pages: 1  


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

Version: 2.1.12172008 Final.