Hey there Wen,

At the moment this query is internal due to the fact that it could fire off way too many database queries if used in a large list of products on every item.

If you need this implemented immediately you would have to go directly against the database - which is pretty easy with PetaPoco.

You would start by creating a POCO object

 [TableName("merchEntityCollection")]
 internal class EntityCollectionDto
{
    /// <summary>
    /// Gets or sets the key.
    /// </summary>
    [Column("pk")]
    public Guid Key { get; set; }

    /// <summary>
    /// Gets or sets the parent key.
    /// </summary>
    [Column("parentKey")]
    public Guid? ParentKey { get; set; }

    /// <summary>
    /// Gets or sets the entity type field key.
    /// </summary>
    [Column("entityTfKey")]
    public Guid EntityTfKey { get; set; }

    /// <summary>
    /// Gets or sets the name.
    /// </summary>
    [Column("name")]
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets the sort order.
    /// </summary>
    [Column("sortOrder")]
    public int SortOrder { get; set; }

    /// <summary>
    /// Gets or sets the key for the collection provider.
    /// </summary>
    [Column("providerKey")]
    public Guid ProviderKey { get; set; }

    /// <summary>
    /// Gets or sets the update date.
    /// </summary>
    [Column("updateDate")]
    public DateTime UpdateDate { get; set; }

    /// <summary>
    /// Gets or sets the create date.
    /// </summary>
    [Column("createDate")]
    public DateTime CreateDate { get; set; }
}

And then use it in a query like

     var sqlSyntax = ApplicationContext.Current.DatabaseContext.SqlSyntax;

     var sql = new Sql();
     sql.Select("*")
                .From<EntityCollectionDto>(SqlSyntax)
                .Append("WHERE [merchEntityCollection].[pk] IN (")
                .Append("SELECT DISTINCT([entityCollectionKey])")
                .Append("FROM [merchProduct2EntityCollection]")
                .Append("WHERE [merchProduct2EntityCollection].[productKey] = @pkey", new { @pkey = productKey })
                .Append(")");
        var dtos = Database.Fetch<EntityCollectionDto>(sql);

This will get you the a list of objects (not Merchello objects), but it will have the entity collection keys and collection names ....

Basically the only different to that and the internals we would use is the database caching and the factory to instantiate the IEntityCollection object.

is working on a reply...