How to write this Linq to NHibernate query

Question

We have an entity like this :

public class File{
       public int Region{get;set;}
       public bool ShowLocation{get;set;}
       //Other fields are omitted
}

I would like to write this query:

    SELECT Region,SUM(CASE WHEN ShowLocation=1 THEN 1 ELSE 0 END) AS
 ShowCount,SUM(CASE WHEN ShowLocation=0 THEN 1 ELSE 0 END) AS NotShowCount
--WHERE omitted for the sake of simplicity
GROUP BY Region

For some reasons I would like to use Linq To Nhibernate (We have a complex filtering mechanism that generates an Expression<Func<File,bool>>)

So far I couldn't find any way to achieve this using Linq To NHibernate. Here's some of my attempts:

Conditioanl Count:(No exception but it count all rows anyway)

    Files
    .Where(whereExpression)
    .GroupBy(x=>x.Region)
    .Select(x=>new
    {
        x.Region,
        ShowCount=x.Count(f=>f.ShowLocation==1),
        NotShowCount=x.Count(f=>f.ShowLocation==0)
    });

Conditioanl Sum : Not Supported/Implemented Exception

Files
.Where(whereExpression)
.GroupBy(x=>x.Region)
.Select(x=>new 
{
  x.Region,
  ShowCount=x.SUM(f=>f.ShowLocation==1?1:0),
  NotShowCount=x.SUM(f=>f.ShowLocation==0?1:0)
});

SELECT Before GROUP : Not Supported/Implemented Exception

    Files.Where(whereExpression).Select(x=>new
    {
       x.Region,
       Show=x.ShowLocation==1?1:0,
       NotShow=x.ShowLocation==0?1:0
    })
    .GroupBy(x=>x.Region)
    .Select(x=>new 
    {
       x.Region,
       ShowCount=x.SUM(f=>f.Show),
       NotShowCount=x.SUM(f=>f.NotShow)
    });

UNION : Not Supported/Implemented Exception

    Files
       .Where(whereExpression)
       .Where(x=>x.ShowLocation==1)
 .Select(x=>new
{
x.Region,
Show=1,NotShow=0
})
.Union(Files
 .Where(whereExpression)
.Where(x=>x.ShowLocation==0)
.Select(x=>new
{x.Region,
Show=0,
NotShow=1
}))
.GroupBy(x=>x.Region)
.Select(x=>new 
{
x.Region,
CountShow=x.Count(a=>a.Show),
CountNotShow=x.Count(a=>a.NotShow)
});

I have no other clues. Any other idea ?

BunkerMentality · Accepted Answer · 2015-04-28 11:16:21Z

1

I don't know if you can get that to work with Linq to NH. Would you be able to use QueryOver instead? The QueryOver API also takes an Expression<Func<T,bool>> in the where clause so you should be able to get it to work like this with your existing filter:

            MyDto dto = null;

            var myDtoList = session.QueryOver<File>()
                .Select(
                    Projections.Group<File>(x => x.Region).WithAlias(() => dto.Region),
                    Projections.Sum(
                        Projections.Conditional(
                            Restrictions.Where<File>(c => c.ShowLocation== 1),
                            Projections.Constant(1),
                            Projections.Constant(0))).WithAlias(() => dto.ShowCount),
                    Projections.Sum(
                        Projections.Conditional(
                            Restrictions.Where<File>(c => c.ShowLocation== 0),
                            Projections.Constant(1),
                            Projections.Constant(0))).WithAlias(() => dto.NotShowCount))
                .TransformUsing(Transformers.AliasToBean<MyDto>())
                .List<MyDto>();

QueryOver doesn't really work with anonymous types so you will have to define MyDto with the properties you want to return, ie Region, ShowCount and NotShowCount

answered Apr 28, 2015 at 11:16

BunkerMentality

1,34716 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Beatles1692 Over a year ago

Unfortunately as I mentioned in my question I am afraid that I have to use Linq to NHibernate.

BunkerMentality Over a year ago

@Beatles1692 OK sorry about that, I thought you only wanted to use Linq to Nh because you want to reuse your filtering mechanism that returns Expression<Func<File,bool>>

Beatles1692 Over a year ago

The problem is QueryOver doesn't support some of Linq expressions such as Any

BunkerMentality Over a year ago

There is some way of creating your own extension methods for nhibernate linq provider. Have a look at this maybe: codeproject.com/Articles/318177/… and maybe try to create your own extension, ie SumWhere(x=>x.ShowLocation==1) or something like that. I've never tried it but it might be worth a shot?

Florian Lim · Accepted Answer · 2015-04-28 12:24:18Z

1

I used your second attempt and inserted a ToList() before the Select(). The result would look like that:

Files
.Where(whereExpression)
.GroupBy(x=>x.Region)
.ToList<IGrouping<int, File>>()
.Select(x=>new 
{
  x.Key,
  ShowCount = x.Sum(f => f.ShowLocation == 1 ? 1 : 0),
  NotShowCount = x.Sum(f => f.ShowLocation == 0 ? 1 : 0)
});

In this way the Select is applied to a List instead of the IQueryable.

answered Apr 28, 2015 at 12:24

Florian Lim

5,3622 gold badges29 silver badges29 bronze badges

2 Comments

Beatles1692 Over a year ago

Well that could be an alternative if I can't find a way to create my query in any other way :) thanks

Beatles1692 Over a year ago

Okay I tested it and it turned out a very bad idea because it doesn't allow you to project the only columns that you need before GroupBy and thus it would try to fetch all the object graph whether they are not lazy loaded and in our case it results to a SELECT N+1 :)

Collectives™ on Stack Overflow

How to write this Linq to NHibernate query

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related