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