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!

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.

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?

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);

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.

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

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

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)
                                )));

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

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