Hi. I want to do a SubQuery in the SELECT statement as follows:

Statement 1

SELECT c.CustomerId, 
(SELECT (o.OrderPrice + o.DiscountedPrice) as CalculatedPrice FROM Order o WHERE c.CustomerId = 1), 
c.CustomerName
FROM Customer c

But currently, to do a subquery need to use ScalarQueryExpression that is suitable for the needs. However, it only returned:

Statement 2

SELECT c.CustomerId, 
(SELECT (o.OrderPrice + o.DiscountedPrice) as CalculatedPrice WHERE c.CustomerId = 1), 
c.CustomerName
FROM Customer c

LLBL will mention that the o fields cannot be bound. I have tried using other methods, it seems like whenever the field have its ExpressionToApply set, the FROM will never render and normal field or field with Aggregation can have its FROM rendered. In fact, it actually reflected on the outer query (If only the field with ScalarQueryExpression is selected or if it is at the first SELECT field).

Questions:

1. How do I achieve like in Statement 1? I need to do a field that runs a Switch case (using Functions.IIF()) but the FROM is not render.

2. Is there a way to just give 1 table in the RelationCollection? It seems that the ScalarQueryExpression supported to give.

Thank you for reading.

What's the code you're using?

Otis wrote:

What's the code you're using?

Thank you for the response. I'm using ScalarQueryExpression on EntityField2.

Required to perform subquery at the SELECT statement where the EntityField2 doing a Switch case and Aggregation in the subquery like the example below:

SELECT c.CustomerId,
(SELECT SUM (CASE WHEN o.Active IS NOT NULL THEN 1 ELSE 0) FROM Order o WHERE c.CustomerId = 1), <-- The subquery to be generated.
c.CustomerName
FROM Customer c

From what I know subquery at the WHERE statement use FieldCompareSetPredicate. So, I was wondering what are the classes to use for SELECT statement. Thank you.

No I mean the C# code that creates this query.

Otis wrote:

No I mean the C# code that creates this query.

Oh I see. Sorry, here is the code working on

IPredicate switchCondition = new PredicateExpression(OrderEntity.Fields[“Active”].IsNotNull());
IExpression switchExpre = Functions.IIF(switchCondition, 1, 0);
IEntityField2 switchField = new EntityField2("SwitchField", switchExpre);

IPredicate subQueryCondition = new PredicateExpression(CustomerEntity.Fields[“CustomerId”].Equal(1))
ScalarQueryExpression sumSwitchSe = new ScalarQueryExpression(switchField.SetAggregateFunction(AggregateFunction.Sum), subQueryCondition);

IEntityField2 sumSwitchScalarField = new EntityField2("ScalarSumSwitch", sumSwitchSe);

ResultsetFields resultsetFields = new ResultsetFields(2);
resultsetFields.Add(sumSwitchScalarField);

QueryFactory queryFactory = new QueryFactory();
DynamicQuery dynamicQuery = queryFactory.Create();

dynamicQuery.Limit(1);
dynamicQuery.Select(resultsetFields.ToArray<IEntityFieldCore>());

dynamicQuery.GroupBy(CustomerEntity.Fields[“CustomerId”]);

// Call DataAccessAdapter to fetch with dynamicQuery

User001 wrote:

Otis wrote:

No I mean the C# code that creates this query.

Oh I see. Sorry, here is the code working on

IPredicate switchCondition = new PredicateExpression(OrderEntity.Fields[“Active”].IsNotNull());
IExpression switchExpre = Functions.IIF(switchCondition, 1, 0);
IEntityField2 switchField = new EntityField2("SwitchField", switchExpre);

IPredicate subQueryCondition = new PredicateExpression(CustomerEntity.Fields[“CustomerId”].Equal(1))
ScalarQueryExpression sumSwitchSe = new ScalarQueryExpression(switchField.SetAggregateFunction(AggregateFunction.Sum), subQueryCondition);

IEntityField2 sumSwitchScalarField = new EntityField2("ScalarSumSwitch", sumSwitchSe);

ResultsetFields resultsetFields = new ResultsetFields(2);
resultsetFields.Add(sumSwitchScalarField);

QueryFactory queryFactory = new QueryFactory();
DynamicQuery dynamicQuery = queryFactory.Create();

dynamicQuery.Limit(1);
dynamicQuery.Select(resultsetFields.ToArray<IEntityFieldCore>());

dynamicQuery.GroupBy(CustomerEntity.Fields[“CustomerId”]);

// Call DataAccessAdapter to fetch with dynamicQuery

(You really should use queryspec or similar to formulate queries, as it's way less verbose).

To fix it, all you have to do is replace

IEntityField2 sumSwitchScalarField = new EntityField2("ScalarSumSwitch", sumSwitchSe);

with

IEntityField2 sumSwitchScalarField = OrderFields.Active.SetExpression(sumSwitchSe);

It doesn't matter what field you pick from Order, the field itself isn't used, it's been replaced by the expression, however it IS used to determine what the target is for the scalar query

Also, please use e.g. CustomerFields.CustomerId, instead of CustomerEntity.Fields[“CustomerId”] as the latter creates a Fields object too while the former creates a just a lightweight field

Otis wrote:

User001 wrote:

Otis wrote:

No I mean the C# code that creates this query.

Oh I see. Sorry, here is the code working on

IPredicate switchCondition = new PredicateExpression(OrderEntity.Fields[“Active”].IsNotNull());
IExpression switchExpre = Functions.IIF(switchCondition, 1, 0);
IEntityField2 switchField = new EntityField2("SwitchField", switchExpre);

IPredicate subQueryCondition = new PredicateExpression(CustomerEntity.Fields[“CustomerId”].Equal(1))
ScalarQueryExpression sumSwitchSe = new ScalarQueryExpression(switchField.SetAggregateFunction(AggregateFunction.Sum), subQueryCondition);

IEntityField2 sumSwitchScalarField = new EntityField2("ScalarSumSwitch", sumSwitchSe);

ResultsetFields resultsetFields = new ResultsetFields(2);
resultsetFields.Add(sumSwitchScalarField);

QueryFactory queryFactory = new QueryFactory();
DynamicQuery dynamicQuery = queryFactory.Create();

dynamicQuery.Limit(1);
dynamicQuery.Select(resultsetFields.ToArray<IEntityFieldCore>());

dynamicQuery.GroupBy(CustomerEntity.Fields[“CustomerId”]);

// Call DataAccessAdapter to fetch with dynamicQuery

(You really should use queryspec or similar to formulate queries, as it's way less verbose).

To fix it, all you have to do is replace

IEntityField2 sumSwitchScalarField = new EntityField2("ScalarSumSwitch", sumSwitchSe);

with

IEntityField2 sumSwitchScalarField = OrderFields.Active.SetExpression(sumSwitchSe);

It doesn't matter what field you pick from Order, the field itself isn't used, it's been replaced by the expression, however it IS used to determine what the target is for the scalar query

Also, please use e.g. CustomerFields.CustomerId, instead of CustomerEntity.Fields[“CustomerId”] as the latter creates a Fields object too while the former creates a just a lightweight field

Thank you for the response. I was practicing how to use ScalarQueryExpression 😅. I have tried the recommendation provided, where using the field that belongs to the OrderEntity then set the ScalarQueryExpression, so it can know which Entity to select from. However, the FROM still not showing up and still not using the subquery FROM. Here is the code generated with a beautifier.

SELECT 
  TOP(@p7) (
    SELECT 
      SUM(
        CASE WHEN CASE WHEN (
          [Order].[Active] IS NOT NULL
        ) THEN 1 ELSE 0 END = 1 THEN @p2 ELSE @p4 END
      ) AS [SwitchField] 
    WHERE 
      (
        [Customer].[CustomerId] = @p5
      )
  ) AS [ScalarSumSwitch] 
FROM 
  [Order]
GROUP BY 
   [Customer].[CustomerId] ',N' @p2 int, 
  @p4 int, 
  @p5 int, 
  @p7 bigint ',@p2=1,@p4=0,@p5=1,@p7=1

Your query doesn't make sense, as you have to tie the scalar query to the outer query, which you don't do.

This is the query as formulated in the start post:

[Test]
public void SumOverAddTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var qf = new QueryFactory();
        var q = qf.Customer
                  .Select(() => new
                                {
                                    CustomerId = CustomerFields.CustomerId.ToValue<string>(),
                                    CalculatedPrice = qf.Order
                                                        .CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
                                                        .Select((OrderFields.OrderId + OrderFields.EmployeeId)).Sum().As("CalculatedPrice").ToValue<int>()
                                });
        var results = adapter.FetchQuery(q);
        
    }
}

Which gives: (I added orderid + employeeid as I didn't have other numeric values in the order entity, but you get the idea)

SELECT [Northwind].[dbo].[Customers].[CustomerID]                                                                            AS [CustomerId],
       (SELECT SUM([LPA_L1].[LLBLV_1]) AS [LPAV_]
        FROM   (SELECT ([Northwind].[dbo].[Orders].[OrderID] + [Northwind].[dbo].[Orders].[EmployeeID]) AS [LLBLV_1]
                FROM   [Northwind].[dbo].[Orders]
                WHERE  ((([Northwind].[dbo].[Orders].[CustomerID] = [Northwind].[dbo].[Customers].[CustomerID])))) [LPA_L1]) AS [CalculatedPrice]
FROM   [Northwind].[dbo].[Customers] 

Your start query doesn't have an IIF() your other query has, so this is a different query (i.e. not the same code). So what is the real SQL query you want to achieve, as what you are currently trying doesn't work. Admitted the FROM clause should be there, but it's a complicated problem in our runtime: if there's no real field in a projection it tries to find one, hence I specified it as a real field. It might be the IIF is causing issues, but I have to investigate.

I think you need to change

IEntityField2 switchField = new EntityField2("SwitchField", switchExpre);

into a real field from Order, not a new field. In general you don't need to create these new fields that way