I know about scalar query expression, actually you did not get me, below is the code which summarizes the inner part of query
i.e.
Select o.ClientID,
case when
DATEDIFF(day,(Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID),Getdate()) >3 and ProductID=4 then 0
else
TotalAmount end Amount,PaidOnDate = (Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID)
from [OrderItem]
inner join [Order] o on o.Id = [OrderItem].OrderID and OrderStatus not in (9,10)
where (Select Min(PaidOnDateTime) from [Order] where [Order].ID=[OrderItem].OrderID or [Order].ParentOrder=[OrderItem].OrderID) is not null
and ProductID in(4,5)
Code:
ResultsetFields oResultsetFieldsChargeBack = new ResultsetFields(2);
IPredicateExpression filterChargeback = new PredicateExpression();
filterChargeback.Add(OrderFields.Id == OrderItemFields.OrderId);
filterChargeback.AddWithOr(OrderFields.ParentOrder == OrderItemFields.OrderId);
EntityField oEntityFieldPaidOnDate = new EntityField("PaidOnDate", new ScalarQueryExpression( OrderFields.PaidOnDateTime.SetAggregateFunction( AggregateFunction.Min), filterChargeback), typeof(DateTime?));
EntityField oEntityFieldChargeBack = new EntityField("ChargeBack", new DbFunctionCall("Case When DATEDIFF(day, {0}, GetDate()) > 3 and {1}={2} then 0 else {3} end", new object[]{oEntityFieldPaidOnDate, OrderItemFields.ProductId,
Convert.ToInt32( OrdersEnum.ProductsEnum.CoachingFee),OrderItemFields.TotalAmount}));
RelationCollection oRelationCollection = new RelationCollection();
oRelationCollection.Add( OrderItemEntity.Relations.OrderEntityUsingOrderId, JoinHint.Inner).CustomFilter = new PredicateExpression(new FieldCompareRangePredicate(OrderFields.OrderStatus, true,
Convert.ToInt32(OrdersEnum.OrderStatus.Cancelled), Convert.ToInt32(OrdersEnum.OrderStatus.Deleted)));
IPredicateExpression filterDerivedTable = new PredicateExpression();
filterDerivedTable.Add(oEntityFieldPaidOnDate != DBNull.Value);
filterDerivedTable.AddWithAnd(new FieldCompareRangePredicate( OrderItemFields.ProductId, OrdersEnum.ProductsEnum.CoachingFee, OrdersEnum.ProductsEnum.MatchMakingFee));
oResultsetFieldsChargeBack.DefineField(OrderFields.ClientId, 0);
oResultsetFieldsChargeBack.DefineField(oEntityFieldChargeBack, 1);
DerivedTableDefinition oDerivedTableDefinition = new DerivedTableDefinition(oResultsetFieldsChargeBack, "ChargeBacks", filterDerivedTable, oRelationCollection);
What I actaully wanted to know how do I implement the outer part of the query using this derived table