Translate not exists to

Posts   
 
    
wtijsma
User
Posts: 252
Joined: 18-Apr-2006
# Posted on: 29-May-2012 10:59:53   

I have a structure like this:

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:

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:

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 avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-May-2012 19:47:36   

Would the following Query be easier:

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
Posts: 252
Joined: 18-Apr-2006
# Posted on: 30-May-2012 10:55:27   

Walaa wrote:

Would the following Query be easier:

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 avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# 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).

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 avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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:

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 | Lead developer LLBLGen Pro
wtijsma
User
Posts: 252
Joined: 18-Apr-2006
# 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:

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 avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 simple_smile


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:


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 | Lead developer LLBLGen Pro
wtijsma
User
Posts: 252
Joined: 18-Apr-2006
# 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 simple_smile


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:


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