GROUP_CONCAT - mySql

Posts   
 
    
sybjeb
User
Posts: 81
Joined: 20-Sep-2011
# Posted on: 06-Jun-2012 09:59:54   

Hi,

I have 3 tables :

item_saleable * Id item_saleable_distinction * Id * FK_item_saleable_Id (foreign key to item_saleable.Id) * FK_item_distinction_type_Id (foreign key to item_distinction_type.Id) item_distinction_type * Id * Text

I would like to use it to concatenate item_distinction_type.Text using the GROUP_CONCAT MySQL function as in the following query.

SELECT FK_item_saleable_Id, GROUP_CONCAT(item_distinction_type.Text SEPARATOR '!')
FROM item_saleable_distinction
INNER JOIN item_distinction_type ON item_distinction_type.Id = item_saleable_distinction.FK_item_distinction_type_Id
GROUP BY FK_item_saleable_Id

but don't know how to write it in Linq.

My first idea was to add a GroupConcat function to the FunctionMappingStore like this

  public class DatabaseFunctions : FunctionMappingStore
        {
            public DatabaseFunctions()
                : base()
            {
                this.Add(new FunctionMapping(typeof(DatabaseFunctions), "GroupConcat", 1, "GROUP_CONCAT({0} SEPARATOR '!')"));
            }

            public static string GroupConcat(object arg1)
            {
                return string.Empty;
            }
        }

but in Linq when you use the group by clause, you can only use some limited functions as Min, Max, Average, Count, ...

Is there a way to add the **GroupConcat **to the **IGrouping **interface?

Note : you will find a sample project as an attachment

Attachments
Filename File size Added on Approval
GroupConcatTest.zip 66,967 06-Jun-2012 14:18.42 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jun-2012 11:58:10   

I removed the attachment as it contained binaries, please re-attach it without any redundant data as we already have our own dlls. Thanks.

I tried to create such a query with a custom aggregate function mapping, but that doesn't work properly, as you have to define a lambda which accepts e.g. an int, but under the hood, it's used to transform a field reference to a value and the linq provider fails to be able to do so: it doesn't expect a function mapping at that spot.

I'd rewrite the query in queryspec, it's easier as you an define the function mapping right there where you want it, or use our lower level API for this query. I couldn't find a single example online which uses a custom aggregate function mapping in another o/r mapper, like ef or linq to sql, so my guess is that linq is not suitable for queries like this.

Frans Bouma | Lead developer LLBLGen Pro
sybjeb
User
Posts: 81
Joined: 20-Sep-2011
# Posted on: 06-Jun-2012 14:19:03   

I re-attach the project without any dll.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 06-Jun-2012 19:25:27   

I tried to create such a query with a custom aggregate function mapping, but that doesn't work properly, as you have to define a lambda which accepts e.g. an int, but under the hood, it's used to transform a field reference to a value and the linq provider fails to be able to do so: it doesn't expect a function mapping at that spot.

I'd rewrite the query in queryspec, it's easier as you an define the function mapping right there where you want it, or use our lower level API for this query. I couldn't find a single example online which uses a custom aggregate function mapping in another o/r mapper, like ef or linq to sql, so my guess is that linq is not suitable for queries like this.

Didn't get your feedback on the above simple_smile

sybjeb
User
Posts: 81
Joined: 20-Sep-2011
# Posted on: 13-Jun-2012 14:06:58   

My feedback ...

I hoped you would have been able to find a solution (as you always did) but this time...

Really don't want to use the Linq version sometimes, the QuerySpec another time and finally the low level framework.

I will have to think about it.

goose avatar
goose
User
Posts: 392
Joined: 06-Aug-2007
# Posted on: 23-Nov-2019 11:34:41   

Hello, I know its an old thread, but I'm facing the exactly same problem with queryspec

goose avatar
goose
User
Posts: 392
Joined: 06-Aug-2007
# Posted on: 23-Nov-2019 11:44:29   

I fixed it:

IEntityFieldCore field = FuncionFields.FechaInicio;
                field.ExpressionToApply = new DbFunctionCall("CAST(GROUP_CONCAT({0}) as CHAR)", new object[] { FuncionFields.FechaInicio });

with FunctionMappingStore:

 internal class TTFunctionMappings: FunctionMappingStore
            {
                public TTFunctionMappings() : base ()
                {
                    this.Add(new FunctionMapping(typeof(TTFunctions), "GroupConcat", 1, "group_concat(distinct {0})"));
                }
            }

            internal static class TTFunctions
            {


                public static FunctionMappingExpression GroupConcat(IEntityFieldCore input)
                {
                    return new FunctionMappingExpression(typeof(TTFunctions), "GroupConcat", 1, input);
                }

            }

in the projection:

Dates = TTFunctions.GroupConcat(FuncionFields.FechaInicio).As("Fechas").ToValue<string>().Split()

in the DynamicQuery object:

                q.CustomFunctionMappingStore = new TTFunctionMappings();