GroupBy - columns from two tables

ServiceStack.OrmLite
1

Hello,

Is it possible to get SQL expression like this one:

select T1.X, T2.Y, sum(T1.Z) as SumZ
from T1
left join T2 on T1.id=T2.T1Id
group by T1.X, T2.Y

but with typed API?

I tried something like this, but GroupBy does not allow grouping from two tables

Db.From<T1>()
.LeftJoin<T2>()
.GroupBy<T1, T2>((t1,t2) => new [] { t1.X, t2.Y })
.Select<T1, T2>((t1, t2) => new
{
	t1.X,
	t2.Y,
	SumZ = Sql.Sum(t1.Z),		
});
2

Use an anonymous type expression for referencing multiple columns, e.g:

Db.From<T1>()
  .LeftJoin<T2>()
  .GroupBy<T1, T2>((t1,t2) => new { t1.X, t2.Y })
3

I don’t know what I’m missing, but GroupBy does not allow two generic type parameters?

groupBy.PNG1523×294 51.1 KB

4

Can you please provide the full class definitions of each class so I can repro this error locally,

5

Test example:

public class GroupByTester
{
  public IDbConnectionFactory DbFactory { get; set; }

  public GroupByTester()
  {
    using (var db = DbFactory.Open())
    {                    
      //Delegate 'Expression<Func<T1, T2>>' does not take 2 arguments                    
      //'SqlExpression<T1>' does not contain a definition for 'GroupBy' 
      //and the best extension method overload
      //'Queryable.GroupBy<T1, T2>(IQueryable<T1>, Expression<Func<T1, T2>>)' 
      //requires a receiver of type 'IQueryable<T1>'
      db.From<T1>()
       .LeftJoin<T2>()
       .GroupBy<T1, T2>((t1, t2) => new { t1.X, t2.Y })       
    }
  }
}

public class T1
{
  public int Id { get; set; }
  public string X { get; set; }
  public int Z { get; set; }
}

public class T2
{
  public int Id { get; set; }
  public int T1Id { get; set; }
  public string Y { get; set; }
}
6

Currently I have workaround like this (created reference of T1 in T2 class):

public class GroupByTester
    {
        public IDbConnectionFactory DbFactory { get; set; }

        public GroupByTester()
        {
            using (var db = DbFactory.Open())
            {                
              db.From<T1>()
                .LeftJoin<T2>()
                .GroupBy<T2>(t2 => new { t2.T1.X, t2.Y })                
                .Select<T1, T2>((t1, t2) => new
                {
                    t1.X,
                    t2.Y,
                    SumZ = Sql.Sum(t1.Z)
                });
            }
        }
    }

    public class T1
    {
        public int Id { get; set; }
        public string X { get; set; }
        public int Z { get; set; }
    }

    public class T2
    {
        public int Id { get; set; }
        public int T1Id { get; set; }
        public string Y { get; set; }

        [Reference]
        public T1 T1 { get; set; }        
    }
7

This should now work with the multiple GROUP BY overloads added in this commit.

This change is available from v4.5.5+ that’s now available on MyGet.

8

Thank you once more for excellent support.