Hi, I am trying to create a report using a TypedView.

The view looks like this.

SELECT dl.Name, rd.DoctorInvolvementTypeId, r.DateTimeRequested FROM Request_DoctorLocation rd, DoctorLocation dl, Request r WHERE r.RequestId = rd.RequestId and dl.DoctorLocationId = rd.DoctorLocationId

I need to add an additional section through predicates to this:

AND (SELECT COUNT(sdl.Name) FROM Request_DoctorLocation srd, DoctorLocation sdl, Request sr WHERE sr.RequestId = srd.RequestId and sdl.DoctorLocationId = srd.DoctorLocationId and sdl.Name = dl.Name and sr.DateTimeRequested between '2007-01-20 14:09:00.000' and '2007-05-23 14:09:00.000' )> some value

When i run this , it works fine but I cannot create it in my code.

Here is my attempt

IPredicateExpression predicateExpression = new PredicateExpression( new FieldBetweenPredicate(ViewDoctorRequestsFields.DateTimeRequested, e.DateFrom, e.DateTo));

        predicateExpression.Add(ViewDoctorRequestsFields.DoctorInvolvementTypeId == DoctorInvolvementTypeEntity.Primary);

        PredicateExpression totalPredicateExpression = new PredicateExpression(new FieldBetweenPredicate(ViewDoctorRequestsFields.DateTimeRequested, e.DateFrom, e.DateTo));

        RelationCollection relationCollection = new RelationCollection();
        relationCollection.Add(RequestEntity.Relations.RequestDoctorLocationEntityUsingRequestId);
        relationCollection.Add(RequestDoctorLocationEntity.Relations.DoctorLocationEntityUsingDoctorLocationId);

         // JF van Zyl: Create a field to return the total tests for the request
        IEntityField totalCount =  new EntityField("TotalCount",
             new ScalarQueryExpression(ViewDoctorRequestsFields.Name.SetAggregateFunction(AggregateFunction.Count),
                 totalPredicateExpression, relationCollection));

        predicateExpression.Add(new FieldCompareValuePredicate(totalCount, ComparisonOperator.GreaterThan, CustomConvert.ToInt32(txtMinValue.Text, 0)));

It generates the following sql, which is wrong,

exec sp_executesql N'SELECT [preLink].[dbo].[viewDoctorRequests].[Name], [preLink].[dbo].[viewDoctorRequests].[DoctorInvolvementTypeId], [preLink].[dbo].[viewDoctorRequests].[DateTimeRequested] FROM [preLink].[dbo].[viewDoctorRequests] WHERE ( ( ( [preLink].[dbo].[viewDoctorRequests].[DateTimeRequested] BETWEEN @DateTimeRequested1 AND @DateTimeRequested2 AND [preLink].[dbo].[viewDoctorRequests].[DoctorInvolvementTypeId] = @DoctorInvolvementTypeId3 AND (SELECT TOP 1 COUNT([preLink].[dbo].[viewDoctorRequests].[Name]) AS [Name] FROM (( [preLink].[dbo].[Request] INNER JOIN [preLink].[dbo].[Request_DoctorLocation] ON [preLink].[dbo].[Request].[RequestId]=[preLink].[dbo].[Request_DoctorLocation].[RequestId]) INNER JOIN [preLink].[dbo].[DoctorLocation] ON [preLink].[dbo].[DoctorLocation].[DoctorLocationId]=[preLink].[dbo].[Request_DoctorLocation].[DoctorLocationId]) WHERE ( [preLink].[dbo].[viewDoctorRequests].[DateTimeRequested] BETWEEN @DateTimeRequested5 AND @DateTimeRequested6)) > @TotalCount4)))',N'@DateTimeRequested1 datetime,@DateTimeRequested2 datetime,@DoctorInvolvementTypeId3 uniqueidentifier,@DateTimeRequested5 datetime,@DateTimeRequested6 datetime,@TotalCount4 int',@DateTimeRequested1='2004-01-01 00:00:00:000',@DateTimeRequested2='2007-05-11 00:00:00:000',@DoctorInvolvementTypeId3='69274150-6835-468E-8798-84788BB3027C',@DateTimeRequested5='2004-01-01 00:00:00:000',@DateTimeRequested6='2007-05-11 00:00:00:000',@TotalCount4=5

How can I create the correct sql?

I need to add an additional section through predicates to this:

AND (SELECT COUNT(sdl.Name) FROM Request_DoctorLocation srd, DoctorLocation sdl, Request sr WHERE sr.RequestId = srd.RequestId and sdl.DoctorLocationId = srd.DoctorLocationId and sdl.Name = dl.Name and sr.DateTimeRequested between '2007-01-20 14:09:00.000' and '2007-05-23 14:09:00.000' )> some value

When i run this , it works fine but I cannot create it in my code.

How exactly do you run it? do you append the above lines to the view lines and run them?

If you do this please don't. And try to simulate it by querying the view (Select * FROM view...) and add the Where clause that you want, when you succeed in having the resultset that you want, post the new query and we can try to help you do it with LLBLGen code. (Thinking this way may help you do it yourself by LLBLGen code)

Hi, this is it.

select * from viewDoctorRequests v where v.DateTimeRequested between '2007-01-20 14:09:00.000' and '2007-05-23 14:09:00.000' AND v.DoctorInvolvementTypeId = '69274150-6835-468E-8798-84788BB3027C' AND (SELECT COUNT(sdl.Name) FROM Request_DoctorLocation srd, DoctorLocation sdl, Request sr WHERE sr.RequestId = srd.RequestId and sdl.DoctorLocationId = srd.DoctorLocationId and sdl.Name = v.Name and sr.DateTimeRequested between '2007-01-20 14:09:00.000' and '2007-05-23 14:09:00.000')> 5

I need to create the where clause in code, this is driving me mad. Thanks.

I think we may change the SQL to the following:

SELECT * FROM viewDoctorRequests v
WHERE v.DateTimeRequested between '2007-01-20 14:09:00.000' and '2007-05-23 14:09:00.000' 
AND v.DoctorInvolvementTypeId = '69274150-6835-468E-8798-84788BB3027C'
AND Name IN
(
SELECT sdl.Name
FROM Request_DoctorLocation srd, DoctorLocation sdl, Request sr 
WHERE sr.RequestId = srd.RequestId 
AND sdl.DoctorLocationId = srd.DoctorLocationId 
AND sr.DateTimeRequested 
BETWEEN '2007-01-20 14:09:00.000' and '2007-05-23 14:09:00.000'
COUNT(sdl.Name) > 5
)

If the above query will give the same result then, to implement the IN predicate you need to use FieldComapreSetPredicate