My filter seems to be getting ignored

Posts   
 
    
BexMed
User
Posts: 63
Joined: 18-Jul-2007
# Posted on: 26-Oct-2007 15:15:27   

Hello

I am creating a TypedList and it has got lots of filters.. but there is one in particular that it seems to be ignoring. I know this is probably something to do with the actual tables and fields I am bring back, but can't work out what.

Here's my code:


EntityCollection StaffPortals = GetStaffPortals(CurrentUserGuid);
            EntityCollection PrintStationPortals = GetPrintStationPortals(printstationid);


            DataSet set = new DataSet();

            DataAccessAdapter adapter = new DataAccessAdapter();
            ResultsetFields fields = new ResultsetFields(3);

            fields.DefineField(OrderDetailStatusFields.OrderDetailStatusId, 0, "Level1Id");
            fields.DefineField(OrderDetailStatusFields.StatusName, 1, "Level1Name");
            fields.DefineField(OrderDetailStatusFields.OrderDetailStatusId, 2, "Level1Count",
                               AggregateFunction.Count);

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(OrderDetailStatusEntity.Relations.OrderDetailEntityUsingOrderDetailStatusId);
            bucket.Relations.Add(OrderDetailEntity.Relations.OrderEntityUsingOrderId);
            PredicateExpression filter = new PredicateExpression(OrderDetailFields.PrintstationId == printstationid);
            filter.AddWithOr(OrderDetailFields.PrintstationId == DBNull.Value);
            bucket.PredicateExpression.Add(filter);

            PredicateExpression approvalFalseFilter = new PredicateExpression();
            approvalFalseFilter.Add(OrderFields.ApprovalRequired == false);

            PredicateExpression approvedFilter = new PredicateExpression();
            approvedFilter.Add(OrderFields.ApprovalRequired == true);
            approvedFilter.AddWithAnd(OrderFields.Approved == true);

            PredicateExpression wholeApprovalFilter = new PredicateExpression();
            wholeApprovalFilter.Add(approvalFalseFilter);
            wholeApprovalFilter.AddWithOr(approvedFilter);

            bucket.PredicateExpression.Add(wholeApprovalFilter);

            PredicateExpression Portalfilter = new PredicateExpression();
            foreach (PrintStationStaffPortalEntity ent in StaffPortals)
            {
                Portalfilter.AddWithOr(OrderFields.PortalId == ent.PortalId);
            }
            foreach (PrintStationPortalEntity ent in PrintStationPortals)
            {
                Portalfilter.AddWithOr(OrderFields.PortalId == ent.PortalId);
            }
            bucket.PredicateExpression.AddWithAnd(Portalfilter);

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.Add(fields[1]);

            DataTable dynamicList = new DataTable();
            adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);


and here is the SQL query it produces when looking at SQL Server Profiler:



exec sp_executesql N'
SELECT 
[dbo].[OrderDetailStatus].[OrderDetailStatusId] AS [Level1Id], 
[dbo].[OrderDetailStatus].[StatusName] AS [Level1Name], 
COUNT([dbo].[OrderDetailStatus].[OrderDetailStatusId]) AS [Level1Count] 
FROM (( [dbo].[OrderDetailStatus]  
INNER JOIN [dbo].[OrderDetail]  
ON  
[dbo].[OrderDetailStatus].[OrderDetailStatusId]=
        [dbo].[OrderDetail].[OrderDetailStatusId]) 
INNER JOIN [dbo].[Order]  
ON  [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) 
WHERE ( ( 
( [dbo].[OrderDetail].[PrintstationId] = 
        @PrintstationId1 OR [dbo].[OrderDetail].[PrintstationId] IS NULL) 
AND ( 
( [dbo].[Order].[ApprovalRequired] = @ApprovalRequired2) 
OR ( [dbo].[Order].[ApprovalRequired] = @ApprovalRequired3 
AND [dbo].[Order].[Approved] = @Approved4)))) 
GROUP BY [dbo].[OrderDetailStatus].[OrderDetailStatusId], 
[dbo].[OrderDetailStatus].[StatusName]',
N'@PrintstationId1 uniqueidentifier,@ApprovalRequired2 bit,@ApprovalRequired3 bit,@Approved4 bit',@PrintstationId1='A2B67A9A-2E30-4F74-9CE3-29D2E685A868',@ApprovalRequired2=0,@ApprovalRequired3=1,@Approved4=1



It seems to be totally missing out the "Portalfilter" What am I doing wrong?

Thanks

Bex

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 26-Oct-2007 15:54:25   

Did you debug it and made sure it enters those foreach loops? Might be that the StaffPortals and PrintStationPortals are empty.