Forum:  LLBLGen Pro Runtime Framework

Thread:  Translate not exists to


wtijsma (User)   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!
Walaa (Support Team)   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.


wtijsma (User)   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?
Walaa (Support Team)   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);


Otis (LLBLGen Pro Team)   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.
wtijsma (User)   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...


Otis (LLBLGen Pro Team)   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)
                                )));
wtijsma (User)   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