Could someone please help me out?
I have a strange problem when I am using prefetch. My LLBL version is 1.0.2005.1.
I have 3 tables with 1:n relation from top to down:
Enterprise -- EnterpriseService -- EnterpriseServiceAction
I want to get some EnterpriseServiceAction for one particular enterprise. My filter on table EnterpriseServiceAction is:
StartDate<=today and ( EndDate is null or EndDate >=today )
I also have a filter on table EnterpriseService.
My code looks like:
EnterpriseEntity enEntity = new EnterpriseEntity ( enterpriseId );
adapter.FetchEntity ( enEntity );
......
EntityCollection services = enEntity.EnterpriseService;
prefetchPath = new PrefetchPath2 ( (int) EntityType.EnterpriseServiceEntity );
IPrefetchPathElement2 pathElement1 = EnterpriseServiceEntity.PrefetchPathEnterpriseServiceAction;
PredicateExpression actionPredicate = new PredicateExpression ();
PredicateExpression predicate2 = new PredicateExpression ();
actionPredicate .Add ( PredicateFactory.CompareNull ( EnterpriseServiceActionFieldIndex.EndDate ) );
predicate2.AddWithOr ( PredicateFactory.CompareValue ( EnterpriseServiceActionFieldIndex.EndDate, ComparisonOperator.GreaterEqual, DateTime.Today ) );
actionPredicate.Add ( predicate2 );
actionPredicate.AddWithAnd ( PredicateFactory.CompareValue( EnterprisePortalServiceActionFieldIndex.StartDate, ComparisonOperator.LessEqual, DateTime.Today ) );
prefetchPath.Add ( pathElement1, 0, actionPredicate );
IRelationPredicateBucket serviceBucket = enEntity.GetRelationInfoEnterpriseService ();
PredicateExpression servicePredicate = new PredicateExpression ();
servicePredicate.Add (... )
serviceBucket.PredicateExpression.Add ( servicePredicate );
......
adapter.FetchEntityCollection ( services, serviceBucket, prefetchPath );
The query in SQL Profile
exec sp_executesql N'SELECT ....
FROM tbl_EnterpriseServiceAction
WHERE ( ( ( tbl_EnterpriseServiceAction.Enterprise_ID = @EnterpriseId1 AND tbl_EnterpriseServiceAction.Service_ID = @ServiceId2)
OR ( tbl_EnterpriseServiceAction.Enterprise_ID = @EnterpriseId3 AND tbl_EnterpriseServiceAction.Service_ID = @ServiceId4)
AND ( ( ( tbl_EnterpriseServiceAction.EndDate IS NULL OR tbl_EnterpriseServiceAction.EndDate >= @EndDate5)
AND tbl_EnterpriseServiceAction.StartDate] <= @StartDate6 )))) ',
N'@EnterpriseServiceId1 int,@EnterpriseId1 int,@ServiceId2 int,@EnterpriseId3 int,@ServiceId4 int, @EndDate5 datetime,@StartDate6 datetime', @EnterpriseId1 = 1, @ServiceId2 = 1, @EnterpriseId3 = 1, @ServiceId4 = 2, @EndDate5 = 'Aug 12 2006 12:00:00:000AM', @StartDate6 = 'Aug 12 2006 12:00:00:000AM'
The first part in the where clause is the prefetch result, and second part is my filter. But it seems the generated query mixes the first part and second part with the wrong postion of ")"
This query is not what I want. What I really want is:
exec sp_executesql N'SELECT ....
FROM tbl_EnterpriseServiceAction
WHERE ( ( ( tbl_EnterpriseServiceAction.Enterprise_ID = @EnterpriseId1 AND tbl_EnterpriseServiceAction.Service_ID = @ServiceId2)
OR ( tbl_EnterpriseServiceAction.Enterprise_ID = @EnterpriseId3 AND tbl_EnterpriseServiceAction.Service_ID = @ServiceId4) )
AND ( ( tbl_EnterpriseServiceAction.EndDate IS NULL OR tbl_EnterpriseServiceAction.EndDate >= @EndDate5)
AND tbl_EnterpriseServiceAction.StartDate] <= @StartDate6 )))',
N'@EnterpriseServiceId1 int,@EnterpriseId1 int,@ServiceId2 int,@EnterpriseId3 int,@ServiceId4 int, @EndDate5 datetime,@StartDate6 datetime', @EnterpriseId1 = 1, @ServiceId2 = 1, @EnterpriseId3 = 1, @ServiceId4 = 2, @EndDate5 = 'Aug 12 2006 12:00:00:000AM', @StartDate6 = 'Aug 12 2006 12:00:00:000AM'
Is there something wrong in my code?
Thanks
