I have a typed list with two fields, each from a different table. Here's my code to fill the list.

                StoreIDAndNameTypedList stores = new StoreIDAndNameTypedList();
                PredicateExpression filter = new PredicateExpression();
                filter.Add(PredicateFactory.CompareValue(AccountFieldIndex.ParentAccountId, ComparisonOperator.Equal, storeOwner.AccountId));
                filter.Add(PredicateFactory.CompareValue(AccountFieldIndex.CustomerTypeCode, ComparisonOperator.Equal, 5));
                ISortExpression sorter = new SortExpression(SortClauseFactory.Create(AccountFieldIndex.AccountNumber, SortOperator.Ascending));
                stores.Fill(0, sorter, true, filter);

That works fine. Now I realize due to Microsoft's poorly designed CRM database, I better check for null on one of the fields, AccountNumber, which is not a join field and indeed might be null. So I want only records where it is not null.

I tried this, but don't see the change in the SQL when I watch it come across in Profiler.

                filter.Add(PredicateFactory.CompareNull(AccountFieldIndex.AccountNumber, true));

Second question, what exactly would be the difference between that line of code and this (which also doesn't work)

                filter.Add(new FieldCompareNullPredicate(EntityFieldFactory.Create(AccountFieldIndex.AccountNumber), true));

(Late addition, I took out the flag to negate the predicate and I don't see any change the SQL. Boy I must be doing something obviously wrong here )

BTW here is the generated SQL I captured in Profiler:

 exec sp_executesql N'SELECT [dbo].[SC_Stores].[StoreID] AS [StoreID],[dbo].[AccountBase].[AccountNumber] AS [AccountNumber] FROM ( [dbo].[AccountBase] INNER JOIN [dbo].[SC_Stores] ON  [dbo].[AccountBase].[AccountId]=[dbo].[SC_Stores].[AccountId]) WHERE ( [dbo].[AccountBase].[ParentAccountId] = @ParentAccountId1 And [dbo].[AccountBase].[CustomerTypeCode] = @CustomerTypeCode2) ORDER BY [dbo].[AccountBase].[AccountNumber] ASC', N'@ParentAccountId1 uniqueidentifier,@CustomerTypeCode2 int', @ParentAccountId1 = '01503023-D3F5-4FD8-9963-F9CA49DB207F', @CustomerTypeCode2 = 5

JimFoye wrote:

I have a typed list with two fields, each from a different table. Here's my code to fill the list.

                StoreIDAndNameTypedList stores = new StoreIDAndNameTypedList();
                PredicateExpression filter = new PredicateExpression();
                filter.Add(PredicateFactory.CompareValue(AccountFieldIndex.ParentAccountId, ComparisonOperator.Equal, storeOwner.AccountId));
                filter.Add(PredicateFactory.CompareValue(AccountFieldIndex.CustomerTypeCode, ComparisonOperator.Equal, 5));
                ISortExpression sorter = new SortExpression(SortClauseFactory.Create(AccountFieldIndex.AccountNumber, SortOperator.Ascending));
                stores.Fill(0, sorter, true, filter);

That works fine. Now I realize due to Microsoft's poorly designed CRM database, I better check for null on one of the fields, AccountNumber, which is not a join field and indeed might be null. So I want only records where it is not null.

I tried this, but don't see the change in the SQL when I watch it come across in Profiler.

                filter.Add(PredicateFactory.CompareNull(AccountFieldIndex.AccountNumber, true));

Hmmm... so you add this just as a 3rd predicate to the expression? Could you please check if filter.Count is 3 before you call Fill ? It should just take into account this predicate as well.

Second question, what exactly would be the difference between that line of code and this (which also doesn't work)

                filter.Add(new FieldCompareNullPredicate(EntityFieldFactory.Create(AccountFieldIndex.AccountNumber), true));

That's the same code

(Late addition, I took out the flag to negate the predicate and I don't see any change the SQL. Boy I must be doing something obviously wrong here )

At first glance your code looks fine. If the filter.Add(PredicateFactory.CompareNull(AccountFieldIndex.AccountNumber, true)); line is added before you call fill, it should just also add that predicate to the complete filter. The PredicateExpression simply runs over all teh elements and calls its ToQueryText, so it should just work. If you use AddWithAnd, would that help? (should be the same, but just to check if there is a glitch in the Add() method)

The guy on the corner must have sold me some bad crack that day, because this morning it is working. Now, I did restore the database in SQL Server last night, but that shouldn't have anything to do with it.

Here's the code again

 StoreIDAndNameTypedList stores = new StoreIDAndNameTypedList();
PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(AccountFieldIndex.ParentAccountId, ComparisonOperator.Equal, storeOwner.AccountId));
filter.Add(PredicateFactory.CompareValue(AccountFieldIndex.CustomerTypeCode, ComparisonOperator.Equal, 5));
filter.Add(PredicateFactory.CompareNull(AccountFieldIndex.AccountNumber, true));
ISortExpression sorter = new SortExpression(SortClauseFactory.Create(AccountFieldIndex.AccountNumber, SortOperator.Ascending));
stores.Fill(0, sorter, true, filter);

After the first filter.Add the count is 1, then it's 3, and after the third call it's 5. And here's the generated code now

 exec sp_executesql N'SELECT [dbo].[SC_Stores].[StoreID] AS [StoreID],[dbo].[AccountBase].[AccountNumber] AS [AccountNumber] FROM ( [dbo].[AccountBase] INNER JOIN [dbo].[SC_Stores] ON  [dbo].[AccountBase].[AccountId]=[dbo].[SC_Stores].[AccountId]) WHERE ( [dbo].[AccountBase].[ParentAccountId] = @ParentAccountId1 And [dbo].[AccountBase].[CustomerTypeCode] = @CustomerTypeCode2 And [dbo].[AccountBase].[AccountNumber] IS NOT NULL) ORDER BY [dbo].[AccountBase].[AccountNumber] ASC', N'@ParentAccountId1 uniqueidentifier,@CustomerTypeCode2 int', @ParentAccountId1 = '01503023-D3F5-4FD8-9963-F9CA49DB207F', @CustomerTypeCode2 = 5

The count of the predicate expression reflects the amount of elements in the expression, including operators. So if you call add the second time, you will have an operator in there as well. The query IMHO reflects what you wrote in the code.. .