As we're currently porting this forum's sourcecode to .NET 2.0, ASP.NET 2.0 and LLBLGen Pro v2 to give away as an example of how to use LLBLGen Pro v2, I ran into a problem: LLBLGen Pro lacked a feature which would come in handy in a lot of scenario's.

It's this:

SELECT C.*, 
    (
        SELECT COUNT(*) FROM Orders O
        WHERE C.CustomerID = O.CustomerID
    ) AS NumberOrders
FROM Customers C

And more precisely: the subselect in the selectlist. As this query would be a perfect candidate for a dynamic list (entities work with fixed sets of fields), the problem arises how to formulate the subquery in LLBLGen Pro. As there are no alternatives which result in a single roundtrip, this gives a bit of a problem. So I decided that it would be best if I add this feature in the beta period instead of after the final is released.

It doesn't seem to be really difficult. The only I need to do is implement IExpression in a class which calls into the db specific creator to create a subquery for me. IExpression will then guarantee the object can be used inside an expression like what's also done with the database call class.

The only hurdle to overcome is the persistencinfo injection in adapter, but that's doable I think.

I'll give it a try in the next few hours and see if it works out. If so, then it is added to the next build.

This would be great!

And... we're done!

/// <summary>
/// Tests the new ScalarQueryExpression object by fetching 4 fields from customer and appending 2 scalar expression subqueries:
/// the # of orders and the latest order date.
/// </summary>
[Test]
public void ScalarQueryExpressionTest()
{
    ResultsetFields fields = new ResultsetFields( 6 );
    fields.DefineField( CustomerFields.CustomerId, 0 );
    fields.DefineField( CustomerFields.CompanyName, 1 );
    fields.DefineField( CustomerFields.City, 2 );
    fields.DefineField( CustomerFields.Region, 3 );
    fields.DefineField( new EntityField2( "NumberOfOrders",
                        new ScalarQueryExpression( OrderFields.OrderId.SetAggregateFunction( AggregateFunction.Count ),
                            (CustomerFields.CustomerId == OrderFields.CustomerId) ) ), 4 );
    fields.DefineField( new EntityField2( "LastOrderDate",
                        new ScalarQueryExpression( OrderFields.OrderDate.SetAggregateFunction( AggregateFunction.Max ),
                            (CustomerFields.CustomerId == OrderFields.CustomerId))), 5);

    DataTable results = new DataTable();

    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchTypedList(fields, results, new RelationPredicateBucket(CustomerFields.Country=="USA"), 0, 
                new SortExpression(CustomerFields.CompanyName | SortOperator.Ascending), true);
    }

    ResultsetViewer viewer = new ResultsetViewer();
    viewer.BindDataTable(results);
    viewer.ShowDialog();
}

Gives this query:

SELECT  [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId], 
        [Northwind].[dbo].[Customers].[CompanyName], 
        [Northwind].[dbo].[Customers].[City], 
        [Northwind].[dbo].[Customers].[Region], 
        (
            SELECT  TOP 1 COUNT([Northwind].[dbo].[Orders].[OrderID]) AS [OrderId] 
            FROM    [Northwind].[dbo].[Orders]  
            WHERE 
            ( 
                [Northwind].[dbo].[Customers].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID]
            )
        ) AS [NumberOfOrders], 
        (
            SELECT  TOP 1 MAX([Northwind].[dbo].[Orders].[OrderDate]) AS [OrderDate] 
            FROM    [Northwind].[dbo].[Orders]  
            WHERE   
            ( 
                [Northwind].[dbo].[Customers].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID]
            )
        ) AS [LastOrderDate] 
FROM    [Northwind].[dbo].[Customers]  
WHERE 
( 
    ( 
        [Northwind].[dbo].[Customers].[Country] = @Country1
    )
) 
ORDER BY [Northwind].[dbo].[Customers].[CompanyName] ASC

Yay!

I jam it through the same code as the FieldCompareSetPredicate uses, so it just creates a subquery, and therefore the TOP 1 is there, as I want to have it to have one value only in all situations.

As this is a normal IExpression class, you can use it everywhere you want to use an IExpression, so you can assign it to a field's ExpressionToUse property and use it in a DbFunctionCall, in another expression, in a filter ... etc.

Keep in mind that these queries can be inefficient, especially in where clauses. So use them only in select lists if possible

Otis wrote:

Keep in mind that these queries can be inefficient, especially in where clauses. So use them only in select lists if possible

I usually use derived tables for this, as they seem more efficient. I don't know why the SQL Query Engine would run them differently but it does.

BOb

pilotboba wrote:

Otis wrote:

Keep in mind that these queries can be inefficient, especially in where clauses. So use them only in select lists if possible

I usually use derived tables for this, as they seem more efficient. I don't know why the SQL Query Engine would run them differently but it does. BOb

Derived tables as in: select * from (select a, b, c from foo) as Foo2 ... ?

This feature is mostly used for readonly list data which would otherwise be written as a view. To avoid the necessity of writing views for these kind of queries, I added this feature. It's an advanced feature, as it can bite you too if you're too sloppy with this (use it in where clauses for example), but in case where you need it, it's there

In an update note you'd posted on June 8th covering this topic:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6048

you'd mentioned "Tomorrow I'll post an article which shows how to use them."

Does this thread serve as the article, or is there another location on the site where articles you write are posted?

Thanks!

Gabbo wrote:

In an update note you'd posted on June 8th covering this topic:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6048

you'd mentioned "Tomorrow I'll post an article which shows how to use them."

Does this thread serve as the article, or is there another location on the site where articles you write are posted?

Thanks!

No, the article is here: http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

Otis wrote:

Derived tables as in: select * from (select a, b, c from foo) as Foo2

Yes. This way the select is run once rather than once per record... For large result sets it can be much faster.

So, for example, if I wanted a customer list with the Total Orders for each customer I could do this query:

SELECT o.NumofOrders, c.* FROM customer c JOIN (SELECT cust_id, count(*) NumofOrders FROM customers) o ON c.cust_id = o.cust_id

But, it really depends alot as to which is faster.

BOb

I now understand what you mean. The derived table feature is still on the ToDo list, though not supported in this release.

Hi Frans,

Is SubQueryExpression also supported in v1 ? So queries like these can be run :

SELECT GroupId, GroupName, (SELECT GroupId FROM GroupMember WHERE GroupId = dbo.[GROUP].GroupId AND entityname = 'DepotEntity' AND entityId = '1FR-170805-JSE1') AS Status FROM dbo.[Group]

grtz, Danny

No, that's not supported in v1. It will require some architectural changes to support it as well, so it's not supported nor added to v1.