Hi guys,

I was looking for an easy way to get the number of related related entities in a typelist. see this post here http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7919 . and whilst bcubb suggestion works, its easy to break generated code by adding a field to the typedlist without updating the groupby clause in code. what would be really useful is if we could indicate in the designer that a correlated subquery should be used for the aggregate function. would this be possible? and if so is even a good idea?

cheers,

pete

I'm not sure if I understand in full what you're requesting. Could you elaborate a bit about what the feature request is?

Hi Otis,

First, my apologies for not responding sooner, I've been uncommonly busy. Basically, I am after a fast (and preferably code-free) way of creating a typed list that would be the same as the following query ...

select album_id as id, album_title as title, ( select count(*) from dbo.image i where i.album_id = a.album_id ) as imagecount

from dbo.album a

I use typed lists all the time for my view<entitytype> web-pages and I determine whether the user should be able to delete the parent entity, by the number of associated child entities. So in summary, if an album has 0 images delete is allowed and if an album has 1+ images then delete is not allowed.

Now this could be achieved by applying a group by to the generated typed list but its not a flexible solution. If I was to add a new field to the typed list from the parent table (album) then the groupby must also be updated.

Is this making any sense? I have been drinking beer

Cheers, Pete

Is this making any sense? I have been drinking beer

Perhaps I need to get in a better mindset myself.

So you want to make this page not allow deletes if related image entities exist. Is it also important to display the count to the user or just discover if related entities exist?

i have to display the count as well

Pete

May I suggest a workaround, a re-design in fact.

Add a numeric(int) field to your album table called "ImagesCount" (with default value = 0) Increment it when you add an Image, and decrement it when you delete an Image.

Then you may add this field to your TypedList, where you can show the images count, and enables the user to delete an album if the value is 0.

And in this way the TypedList query will run faster, especially if you have a lot of images in an album. And the impact on the Insert/Delete of an image won't be noticable.

Hi Walaa,

This is an option option and I will certainly look into it. But can I confirm that co-related subqueries are not supported in the select statement? i.e the sql outlined above

Cheers,

Pete

Yes they are, they're called ScalarQueryExpression objects, they're normal expressions so you can use them in expressions as other expressions and to use them, you've to set the field's Expression property to an instance of a ScalarQueryExpression instance.

This is supported in v2.0

An example, to extend a typedlist with a scalar query expression:

using System;
using System.Collections.Generic;
using System.Text;
using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.CESys.TinyForum.DALPro.HelperClasses;

namespace SD.CESys.TinyForum.DALPro.TypedListClasses
{
    /// <summary>
    /// Extension class which extends the typedlist MessagesInThreadTypedList and adds a scalar query expression column to the typedlist. 
    /// </summary>
    public partial class MessagesInThreadTypedList
    {
        /// <summary>
        /// Called when the typedlist's resultset has been build. This is the spot to add additional columns to the typedlist in code. 
        /// We do this in code because we can't add a scalar query expression in the typedlist designer. 
        /// </summary>
        /// <param name="fields">The typedlist resultset fields.</param>
        protected override void OnResultsetBuilt(IEntityFields fields)
        {
            // expand the fields with 1 slot, so we can add our scalar query expression to that slot
            int index = fields.Count;
            fields.Expand(1);
            // add a scalar query expression to the list of fields in the typedlist. The scalar query expression 
            // performs a SELECT COUNT(AttachmentID) FROM Attachments WHERE MessageID = Message.MessageID
            // query. 
            fields.DefineField(new EntityField("AmountOfAttachments",
                                new ScalarQueryExpression(AttachmentFields.AttachmentID.SetAggregateFunction(AggregateFunction.Count),
                                    (AttachmentFields.MessageID == MessageFields.MessageID))), index);

            // done
            base.OnResultsetBuilt(fields);
        }
    }
}

Brilliant. Thanks for the code, it may not be "in the designer" but it's a nice solution and meet my requirement well.

Cheers,

Pete