I have an object graph that looks like this:

RootApplication - TranGroup (group of Tran) - contains a CreatedDate date field - TranApplicantGroup (Group of TranApplicant - contains a SSN field

I am trying to create a query that can detect a duplicate applicant by SSN. The pseudo code is like this:

Give me all RootApplications, for which the most recent Tran has an applicant with the given SSN.

The most recent Tran part seems to imply I need to use a prefetch path.

I need help because the SSN filter part seems to be getting applied incorrectly. It is matching applicants in older Trans who have that SSN. It is only supposed to check the most recent Tran, by CreatedDate. So it's like I need to add the Most Recent Tran condition also to the PredicateExrpession, but I don't know how to do that.

PrefetchPath path = new PrefetchPath((int)EntityType.RootApplicationEntity);
RelationCollection relations = new RelationCollection();
SortExpression dateDescending = new SortExpression();
IPredicateExpression ssnFilter = new PredicateExpression();


ssnFilter.Add(CreditTxApplicantFields.Ssn == applicantSSN);

dateDescending.Add(new SortClause(CreditTxFields.Created, SortOperator.Descending));
                relations.Add(RootApplicationEntity.Relations.CreditTxEntityUsingRootApplicationId);
relations.Add(CreditTxEntity.Relations.CreditTxApplicantEntityUsingCreditTxId);

path.Add(RootApplicationEntity.PrefetchPathCreditTx, 1, (IPredicateExpression)null, (IRelationCollection)null, (ISortExpression)dateDescending)
                    .SubPath.Add(CreditTxEntity.PrefetchPathCreditTxApplicant, 1, ssnFilter);

RootApplicationCollection returnValues = new RootApplicationCollection(new RootApplicationEntityFactory());

returnValues.GetMulti(ssnFilter, 0, dateDescending, relations, path);

I'm not sure about the types of relations you have specified.

So please post the SQL query you need to execute then I can help you implementing it with LLBLGen code.

Tip: PrefetchPaths are used to fetech related objects/entities (graph of entities). But as far as I can tell you need to filter upon a related entitiy. So prefetchPaths are irrelevant here.

Walaa wrote:

I'm not sure about the types of relations you have specified.

So please post the SQL query you need to execute then I can help you implementing it with LLBLGen code.

Tip: PrefetchPaths are used to fetech related objects/entities (graph of entities). But as far as I can tell you need to filter upon a related entitiy. So prefetchPaths are irrelevant here.

Thanks for the reply. Unfortunately, I can't spend the time right now to give you the DDL. It is very complex and that is why I posted the pseudo code. I don't have the SQL query for this either.

I will try to explain the concept as best I can.

The Application is the root object. It has a collection of CreditTx (transactions). The CreditTx has a collection of Applicant (people).

Given the input of a SSN (social security number), I need to find all Applications, whose most recent transaction, has any applicant with that SSN.

So, for an example, Applicant A has SSN=1234. They are included in a CreditTx transaction dated July 2010 that is connected to Application P. In that same Application P, there is a later CreditTx transaction dated August 2011, with Applicant B who has SSN=5678. If I run the Duplicate Checker in this case with an SSN input of 1234, it should not detect Application P as a duplicate, because there is a later transaction with a different SSN.

It would appear from your response, that there might be a different way to get the most recent transaction, other than using a PrefetchPath with a SortExpression. If that is the case, please provide some guidance (links or code) that would help me filter first on the most recent Transaction (using a Date field), and then again on the SSN (string).

Thank you again for your help.

Walaa wrote:

I'm not sure about the types of relations you have specified.

So please post the SQL query you need to execute then I can help you implementing it with LLBLGen code.

Tip: PrefetchPaths are used to fetech related objects/entities (graph of entities). But as far as I can tell you need to filter upon a related entitiy. So prefetchPaths are irrelevant here.

OK I figured out how to express it with SQL:

SELECT ra.*, ctxa.*
  FROM [RootApplication] ra
inner join CreditTx ctx on
ctx.RootApplicationID = ra.RootApplicationID
and ctx.CreditTxID = (select top 1 (ctx1.creditTxid) from CreditTx ctx1 where ctx1.rootapplicationid = ctx.RootApplicationID order by ctx1.Created desc)
inner join CreditTxApplicant ctxa on
ctxa.CreditTxID = ctx.CreditTxID
where ctxa.SSN = '666778899'

I guess the subquery is where it all comes together. How can I express that with LLBL?

To do that you will need a ScalarQueryExpression and Custom filters for EntityRelations.

This is an example using Northwind. Let Customer, Order and OrderDetails be your three entities. We want all Customers whose in their last order have at lest one product 41.

[TestMethod]
public void GetCustomersWhoseMoreRecentOrderHasAnySpecificProduct()
{
    // BUILD THE FILTER
    IRelationPredicateBucket bucket = new RelationPredicateBucket();

    // This is the subquery we will put in the join
    // It's important to alias the objects inside the query, except when we reference the outside entity
    // because the entities are of the same type (OrderEntity)
    var innerJoniQuery = new ScalarQueryExpression(
        OrderFields.OrderId.SetObjectAlias("o2"), 
        (OrderFields.CustomerId.SetObjectAlias("o2") == OrderFields.CustomerId), null, 
        new SortExpression(new SortClause(OrderFields.RequiredDate, null, SortOperator.Descending, "o2")),              
        null, true);

    // this is the Customer - Order relation. We will add a new clause to it and put the query we build above                       
    bucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId)
        .CustomFilter = new PredicateExpression(OrderFields.OrderId == innerJoniQuery);
    // the other relation
    bucket.Relations.Add(OrderEntity.Relations.OrderDetailEntityUsingOrderId);

    // we just want the customers whose the last order contains at least one product 41
    bucket.PredicateExpression.Add(OrderDetailFields.ProductId == 41);


    // FETCH
    var customers = new EntityCollection<CustomerEntity>();
    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchEntityCollection(customers, bucket);
    }

    // TESTS            
    Assert.AreEqual(7, customers.Count);
    var expectedValues = new string[] { "FAMIA", "DUMON", "QUEDE", "HUNGO", "SAVEA", "ERNSH", "RATTC" };
    Assert.AreEqual(7, (from c in customers where expectedValues.Contains(c.CustomerId)select c).Count());
}

So, just transport this concept to your scenario. Hope helpful