Usage of function ListAgg

Hi, can you please help me with Sql.Ext.ListAgg function?

I have a table Position that looks like this:	CompanyId	Email
1	john.doe@example.com
1	jane.doe@example.com
2	big.boss@example.com

I'm trying to build query with Linq2DB that would group row by CompanyId and build comma separated list of Emails:

SELECT
  CompanyId,
  LISTAGG(Email, ',') WITHIN GROUP ( ORDER BY Email ) as Emails
FROM Position
GROUP BY CompanyId

CompanyId	Emails
1	john.doe@example.com,jane.doe@example.com
2	big.boss@example.com

Here is my c# model:

public class Position {
  public int CompanyId { get; set; }
  public string Email { get; set; }
}

public class MyDataConection : DataConnection {
  public ITable<Position> Positions => GetTable<Position>();

  public IQueryable<object> CompanyEmails1 => Positions
    .Select(p => {
      p.CompanyId,
      Sql.Ext.ListAgg(p.Email, ",").WithinGroup.OrderBy(p.Email).ToValue()
    });

  public IQueryable<object> CompanyEmails2 => Positions
    .GroupBy(p => p.CompanyId)
    .SelectMany(g => g.Select(p => new { CompanyId = g.Key, p.Email }))
    .Select(p => {
      p.CompanyId,
      Sql.Ext.ListAgg(p.Email, ",").WithinGroup.OrderBy(p.Email).ToValue()
    });
}

First query CompanyEmails1 builds this and fails (missing group by statement):

SELECT
  CompanyId,
  ListAgg(Email, ',') within group (order by Email)
FROM Position

Second query CompanyEmails2 builds this and fails too:

SELECT
  x.C1229240928,
  LISTAGG(c_1.Email, ",") WITHIN GROUP ( ORDER BY c_1.Email )
FROM (
  SELECT
    CompanyId as C1229240928
  FROM Position
  GROUP BY CompanyId
) x
CROSS JOIN Position c_1 on x.C1229240928 = c_1.CompanyId

The only examples I found were in the documentation of Linq2DB and in samples there doesn't figure GroupBy in linq query

Also in source code there are some tests with usage of function ListAgg and there doesn't figure GroupBy neither.

Well I have found the solution ... I can't use the function Sql.Ext.ListtAgg but instead there is an extension method StringAggregate translated into LISTAGG sql function.

So the example above would be:

public class Position {
  public int CompanyId { get; set; }
  public string Email { get; set; }
}

public class MyDataConection : DataConnection {
  public ITable<Position> Positions => GetTable<Position>();

  public IQueryable<object> CompanyEmails1 => Positions
    .GroupBy(p => p.CompanyId)
    .Select(g => {
      CompanyId = g.Key,
      Emails = g.StringAggregate(",", p => p.Email).OrderBy(p => p.Email).ToValue()
    });

  public IQueryable<object> CompanyEmails2 => 
    from p in Positions
    group p by p.CompanyId into grouping
    select new {
      CompanyId = grouping.Key,
      Emails = grouping.StringAggregate(",", x => x.Email).OrderBy(x => x.Email).ToValue()
    };

}

LinqToDB4iSeries / Linq2DB4iSeries

Usage of function ListAgg #49