LINQ group by on data service

Question

My first question is here: Linq lambda expression many to many table select

I use data services on server side to retrieve data from db on client side. I know that grouping is not supported by data services.

Call on client side:

public List<Lottery> GetLotteriesByLotteryOfferId(int lotteryOfferId)
{
    var query = this.ClientRepositories.BackOfficeData.CreateQuery<Lottery>("GetLotteriesByLotteryOfferId")
                    .AddQueryOption("lotteryOfferId", lotteryOfferId).ToList();

    return query;
}

My lambda query ON SERVER SIDE that is not working:

public IQueryable<Lottery> GetLotteriesByLotteryOfferId(int lotteryOfferId)
{
    return this.db.LotteryOffers
                                .Where(lo => lo.Id == lotteryOfferId)
                                .SelectMany(lo => lo.LotteryDrawDates)
                                .Select(ldd => ldd.Lottery)
                                .GroupBy(s => new { s.Name, s.CreatedBy, s.ModifiedOn, s.Id })
                                .Select(g => new Lottery
                                                {
                                                    Name = g.Key.Name,
                                                    CreatedBy = g.Key.CreatedBy,
                                                    ModifiedOn = g.Key.ModifiedOn,
                                                    Id = g.Key.Id
                                                });
}

Here I get an error:

The entity or complex type 'Lottery' cannot be constructed in a LINQ to Entities query.

Which I understand, because of Group By. My question is how could I achieve this on client side? So I run query in server side until Lottery selection(without grouping part) and append additional group by query part on client side?

Additional question If I want to use custom viewmodel, I just create it on client side and select "viewModel" type instead of Lottery with selection?

Example:

                                .Select(g => new CustomViewModel
                                                {
                                                    CountRows = g.Count()
                                                });

Answer 1

I think the error is the LINQ to Entities won't be able to project into a custom class, you should be able to do it by adding AsEnumerable before the projection.

public IQueryable<Lottery> GetLotteriesByLotteryOfferId(int lotteryOfferId)
{
    return this.db.LotteryOffers
                                .Where(lo => lo.Id == lotteryOfferId)
                                .SelectMany(lo => lo.LotteryDrawDates)
                                .Select(ldd => ldd.Lottery)
                                .GroupBy(s => new { s.Name, s.CreatedBy, s.ModifiedOn, s.Id })
                                .AsEnumerable()
                                .Select(g => new Lottery
                                                {
                                                    Name = g.Key.Name,
                                                    CreatedBy = g.Key.CreatedBy,
                                                    ModifiedOn = g.Key.ModifiedOn,
                                                    Id = g.Key.Id
                                                });
}

I also think your understanding of viewModel is correct.

Answer 2

I think that the error is that you use the class Lottery in your selector. LINQ to entity can only construct pur "Data Transfert Object" : class containing only public properties with trivial getter and setter and without constructor.

class LotteryDTO
{
  public string Name { get; set; }
  public string CreatedBy { get; set; } 
  ...
}

IQueryable<LotteryDTO> result = db.LotteryOffers
                            .Where(...)
                            .SelectMany(...)
                            .Select(...)
                            .GroupBy(...)
                            .Select(g => new LotteryDTO {
                               Name = g.Key.Name,
                               CreatedBy = g.Key.CreatedBy,
                               ...    
                            });