the curent query isn't possible as sub-select within the from clause are not possible.
This query may work for you though
select pr.*,
(select min(StartDate) from Task t where t.ProjectId = pr.ProjectId) as StartDate,
(select max(EndDate) from Task t where t.ProjectId = pr.ProjectId) as EndDate,
from ProjectResource pr
where pr.UserId = @userId
and StartDate >= @startDate
and EndDate <= @endDate
The syntax may not be 100%, i don't have a db to test against right now, but it should be pretty close.
When i have extended entities like this I create an extended factory of the target entity. and override the CreateFields function. heres the code.
public class ExtendedOrderHeaderEntityFactory : OrderHeaderEntityFactory
{
/// <summary>
/// Create the fields for the customer entity and adjust the fields collection
/// with the entity field object which contains the scalar expression
/// </summary>
/// <returns></returns>
public override IEntityFields2 CreateFields()
{
IEntityFields2 toReturn = base.CreateFields();
toReturn.Expand(2);
IEntityField2 GrossSales = new EntityField2("GrossSales", new ScalarQueryExpression(OrderDetailFields.Gross.SetAggregateFunction(AggregateFunction.Sum), (OrderDetailFields.OrderNumber == OrderHeaderFields.OrderNumber)));
toReturn.DefineField(GrossSales, toReturn.Count - 2);
IEntityField2 NetSales = new EntityField2("NetSales", new ScalarQueryExpression(OrderDetailFields.Net.SetAggregateFunction(AggregateFunction.Sum), (OrderDetailFields.OrderNumber == OrderHeaderFields.OrderNumber)));
toReturn.DefineField(NetSales, toReturn.Count - 1);
return toReturn;
}
}
in this example I get a gross/net rollup of the order within the order header. you could do something very similar by getting the min/max dates you require.
I can't speak to wheter this is the best way to create addtional fields within the entity, if you need this information all the time then placing the code directly in the entity itself would be better than the factory.
once these fields are created just include the date predicates in within the filter.
IPredicateExpression filter = new PredicateExpression();
filter.Add(ProjectResourceFields[ProjectResourceFieldIndex.AmountOfFields + 1] >= StartDate);
filter.Add(ProjectResourceFields[ProjectResourceFieldIndex.AmountOfFields + 2] <= EndDate);
filter.Add(ProjectResourceFields.UserId == UserId);