Hi guys...

Looking to translate the following query into C# code, but having a little trouble getting it pieced together.

SQL

SELECT 
  [dbo].[company].*
FROM [dbo].[company]  
INNER JOIN [dbo].[membership_payment]  ON  [dbo].[company].[company_id]=[dbo].[membership_payment].[company_id]
WHERE [dbo].[company].[company_status_id] = 1
AND (
    SELECT TOP 1 m2.payment_received_date
    FROM membership_payment m2
    WHERE 2=2
        AND m2.company_id = [dbo].[membership_payment].company_id 
    ORDER BY m2.create_date DESC ) IS NULL 
AND [dbo].[membership_payment].payment_received_date IS NULL 
OR  [dbo].[company].[company_status_id] = 2 
ORDER BY [dbo].[membership_payment].[payment_date] DESC

I've been working with the FieldCompareSetPredicate but seems I can't just compare on null, looks like I have to compare it to something defined by the first argument IEntityField field.

The subselect I believe is required, because membership_payment must be sorted by create date so that I get the TOP 1 most recent record.

Here's where I am at currently:

C#

            string ALIAS2 = "ALIAS2";

            IPredicateExpression filter1 = new PredicateExpression();
            filter1.Add(PredicateFactory.CompareValue(CompanyFieldIndex.CompanyStatusId, ComparisonOperator.Equal, MBI.DataServer.Enumerations.CompanyStatus.Active));
            filter1.Add(PredicateFactory.CompareNull(MembershipPaymentFieldIndex.PaymentReceivedDate));

            RelationCollection relations1 = new RelationCollection();
            relations1.Add(CompanyEntity.Relations.MembershipPaymentEntityUsingCompanyId);

            RelationCollection relations2 = new RelationCollection();
            relations2.Add(CompanyEntity.Relations.MembershipPaymentEntityUsingCompanyId, ALIAS2, JoinHint.Inner);

            IPredicateExpression filter2 = new PredicateExpression();

            filter1.Add(new FieldCompareSetPredicate(MembershipPaymentFields.CompanyId, MembershipPaymentFields.PaymentReceivedDate,
                SetOperator.Exist,
                (MembershipPaymentFields.CompanyId == MembershipPaymentFields.CompanyId.SetFieldAlias(ALIAS2)), 
                relations2,
                ALIAS2, 
                1, 
                null));


            companyCollection.GetMulti(filter1, 0, null, relations1, null);

The above code is not quite there, for one the alias is off, but I can probably fix that soon, more importantly though is how to compare a null. I thought perhaps the SetOperator.Exists might work, but that returns true even when the subquery returns a null for a value.

Here is how you can compare with NULL.

Please let us know if you made it

Sorry, I've looked through the documentation. If you are referring to FieldCompareNullPredicate, I am not seeing how to use it to compare null against a return value from a subquery.

From what I can tell, the FieldCompareSetPredicate is used to create the subquery. Can the FieldCompareNullPredicate create a subquery also? If so, I'd like to know how?

Thanks!

Try to use the FieldCompareNullPredicate and set the field's ExpressionToApply to a ScalarQueryExpression.

This is what I am looking for... though I chose to write the query inline and pass into a IDataValueProjector, I will consider revising and using the scalarQueryExpression.

Thanks for your help!!!