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!