Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> GROUP_CONCAT - mySql
 

Pages: 1
LLBLGen Pro Runtime Framework
GROUP_CONCAT - mySql
Page:1/1 

  Print all messages in this thread  
Poster Message
sybjeb
User



Location:

Joined on:
20-Sep-2011 09:29:46
Posted:
81 posts
# Posted on: 06-Jun-2012 09:59:54. Goto attachments  
Hi,

I have 3 tables :

Quote:
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.

Code:
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

Code:
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
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37859 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sybjeb
User



Location:

Joined on:
20-Sep-2011 09:29:46
Posted:
81 posts
# Posted on: 06-Jun-2012 14:19:03.  
I re-attach the project without any dll.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# Posted on: 06-Jun-2012 19:25:27.  
Quote:
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 Regular Smiley


  Top
sybjeb
User



Location:

Joined on:
20-Sep-2011 09:29:46
Posted:
81 posts
# 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.
  Top
goose
User



Location:
Central America
Joined on:
06-Aug-2007 18:21:05
Posted:
392 posts
# 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

gansodesoya  Top
goose
User



Location:
Central America
Joined on:
06-Aug-2007 18:21:05
Posted:
392 posts
# Posted on: 23-Nov-2019 11:44:29.  
I fixed it:

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


with FunctionMappingStore:

Code:
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:

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


in the DynamicQuery object:

Code:
                q.CustomFunctionMappingStore = new TTFunctionMappings();
gansodesoya  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.