Nhibernate Count Distinct (Based on Multiple Columns)

Nhibernate count distinct (based on multiple columns)

Okay this is going to take a few steps, so bear with me. I'm assuming SQL server here, but the instructions should work for any dialect that supports checksum¹:

1. Create a custom dialect that supports the checksum function:

   public class MyCustomDialect : MsSql2008Dialect
   {
       public MyCustomDialect()
       {
           RegisterFunction("checksum", new SQLFunctionTemplate(NHibernateUtil.Int32, "checksum(?1, ?2)"));
       }
   }
   

2. Update your configuration to use the custom dialect (you can do this either in your configuration XML file or with code. See this answer for more information). Here's how I did it inside of my existing configuration code:

   configuration
       .Configure(@"hibernate.cfg.xml")
       .DataBaseIntegration(
           db => db.Dialect<MyCustomDialect>());
   

3. Create a custom projection that calls checksum. This step is optional-- you can call Projections.SqlFunction directly if you'd like, but I think refactoring it into a separate function is cleaner:

   public static class MyProjections 
   {
       public static IProjection Checksum(params IProjection[] projections)
       {
           return Projections.SqlFunction("checksum", NHibernateUtil.Int32, projections);   
       }
   }
   

4. Write your QueryOver query and call the custom projection:

   int count = session.QueryOver<TableA>(() => tableAAlias)
       .Where(p => p.PropertyA.IsLike("%123%"))
       .Left.JoinQueryOver(p => p.TableB, () => tableBAlias)
       .Select(
           Projections.Count(
               Projections.Distinct(
               MyProjections.Checksum(
                   Projections.Property(() => tableAAlias.PropertyA),
                   Projections.Property(() => tableBAlias.PropertyB)))))
       .SingleOrDefault<int>();
   

   This should generate SQL that looks like what you're after:

   SELECT count(distinct checksum(this_.PropertyA, tableba1_.PropertyB)) as y0_
   FROM   [TableA] this_
       left outer join [TableB] tableba1_
       on this_.TableBId = tableba1_.Id
   WHERE  this_.PropertyA like '%123%' /* @p0 */
   

---

¹Still trying to figure out if there's a way to map a function without manually specifying the number of arguments

NHibernate - How to return a distinct list of column entries and the count of each entry

We can do it like this:

// this would be our DTO for result
public class ResultDTO
{
    public virtual string Name { get; set; }
    public virtual int Count { get; set; }
}

This would be the query

// here we declare the DTO to be used for ALIASing
ResultDTO dto = null;

// here is our query
var result = session.QueryOver<Table>()
    .SelectList(l => l
        .SelectGroup(x => x.Name).WithAlias(() => dto.Name)
        .SelectCount(x => x.Name).WithAlias(() => dto.Count)
    )
    .TransformUsing(Transformers.AliasToBean<ResultDTO>())
    .List<ResultDTO>();

how to select multiple columns with only one distinct column from joining two tables with all id's are UNIQUEIDENTIFIER using Nhibernate

Just convert the UniqueIdentifier to a string

select distinct(Department.deptid),min(CAST(Employee.empid AS NVARCHAR(36))) as empid
from Employee
inner join Department on Department.deptid = Employee.deptid
group by Department.deptid

Get Count of two columns using NHibernate

Here's an alternative approach without using DISTINCT.

var historical   = session.Query<Historical>().Where(x => /* other filters here*/ );

var nodeCount    = session.Query<Node>()
                   .Where(n => historical.Any(h => h.NodeId == n.NodeId)).Count();

var projectCount = session.Query<Project>()
                   .Where(p => historical.Any(h => h.ProjectId == p.ProjectId)).Count();

To execute the two count in one roundtrip, use ToFutureValue, it's built-in now on latest NHibernate.

var historical   = session.Query<Historical>().Where(x => /* other filters here*/ );

var nodeCount    = session.Query<Node>()
                   .Where(n => historical.Any(h => h.NodeId == n.NodeId))
                   .ToFutureValue(f => f.Count());

var projectCount = session.Query<Project>()
                   .Where(p => historical.Any(h => h.ProjectId == p.ProjectId))
                   .Count();

Note, you can't see if the two statements is executed one roundtrip through SQL Server profiler, you have to use NHProf. If you can't avail NHProf, just benchmark the query with and without ToFutureValue.

Also, please benchmark the Where+Any approach against Distinct and see if Where+Any is faster, otherwise just use Distinct approach.

NHibernate QueryOver: Get a row count with group by in a subquery

I'm not sure why you need such a complex query. If you only want the count of distinct emails meeting certain criteria I think you could use something like this in SQL:

select count(distinct email)
from Entry
where (conditions...)

And translating this to NHibernate's QueryOver API would look something like this:

int count = session.QueryOver<ContestEntry>()
            .Select(Projections.CountDistinct<ContestEntry>(x => x.Email))
            .FutureValue<int>()
            .Value;//query is not executed until here

Unless I'm missing something, I think this will get the result you're after. There is also a "Distinct" projection and a .ToRowCountQuery() method that you might find interesting.

(N)Hibernate Criteria: summing multiple distinct columns

Take a look at criteria queries documentation http://nhibernate.info/doc/nh/en/index.html#querycriteria-projection, there are some examples that will answer you question.

For your example you should try this

// using NHibernate.Criterion;
// using NHibernate.Transform;

session.CreateCriteria<JournalEntry>()
   .SetProjection(
       Projections.Sum<JournalEntry>(x => x.DebitAmount).As("Debits"),
       Projections.Sum<JournalEntry>(x => x.CreditAmount).As("Credits"),
       // you can use other aggregates
       // Projections.RowCount(),
       // Projections.Max<JournalEntry>(x => x.EffectiveDate)
    )
    .SetResultTransformer(Transformers.AliasToBean<JournalEntrySummary>())
    .UniqueResult<JournalEntrySummary>();

How use Distinct in linq & linq to NHibernate by some columns

You can use GroupBy:

var result = myList.GroupBy(a => new { a.Province, a.City })
      .Select(g => new Address { 
                  Province = g.Key.Province, 
                  City = g.Key.City 
              });

Or use anonymous type:

 myList.Select(a => new { 
            Province = a.Province,
            City = a.City
        })
      .Distinct();

By default, anonymous type uses value quality to compare, it is equivalent when all properties are equivalent.

Another way is to customer EqualityComparer which uses Province and City for Equal and GetHashCode method with another overload Distinct in here

NHibernate - Equivalent of CountDistinct projection using LINQ

I figured out a way to do this, though it may not be optimal in all situations. Just doing a .Distinct() on the LINQ query does, in fact, produce a "distinct" in the resulting SQL query when used without .Count(). If I cause the query to be enumerated by using .Distinct().ToList() and then use the .Count() method on the resulting in-memory collection, I get the result I want.

This is not exactly equivalent to what I was originally doing with the Criteria query, since the counting is actually being done in the application code, and the entire list of IDs must be sent from the DB to the application. In my case, though, given the small number of distinct IDs, I think it will work, and won't be too much of a performance bottleneck.

I do hope, however, that a true CountDistinct() LINQ operation will be implemented in the future.

Distinct over one column with value comparing on another column ICriteria NHibernate

Probably the best approach here is to use EXISTS to filter the result set, first a SQL example to get the logic correct:

DECLARE @Person TABLE (
   Id INT,
   Firstname VARCHAR(20),
   Lastname VARCHAR(20),
   Age INT 
)

INSERT INTO @Person VALUES (1, 'Brad', 'Pitt', 42)
INSERT INTO @Person VALUES (2, 'Angelina', 'Pitt', 45)
INSERT INTO @Person VALUES (3, 'John', 'Smith', 50)
INSERT INTO @Person VALUES (4, 'Jane', 'Smith', 55)

SELECT P.* FROM @Person P
WHERE EXISTS(
    SELECT SUB.LastName, MAX(SUB.Age) as Age FROM @Person SUB
    GROUP BY SUB.LastName
    HAVING SUB.LastName = P.LastName AND MAX(SUB.Age) = P.Age)

This yields the following results which is as expected:

Id  Firstname   Lastname    Age
-------------------------------
2   Angelina    Pitt        45
4   Jane        Smith       55

Now to convert to nHibernate, this effectively builds the same query as above:

var subQuery = DetachedCriteria.For<Person>("SUB")
    .SetProjection(Projections.ProjectionList()
    .Add(Projections.GroupProperty("LastName"), "Lastname")
    .Add(Projections.Max("Age"), "Age"))
    .Add(Restrictions.EqProperty(Projections.Max("Age"), "P.Age")
    .Add(Restrictions.EqProperty("LastName", "P.LastName"));

return session.CreateCriteria<Person>("P")
    .Add(Subqueries.Exists(subQuery))
    .List<Person>();

How to countDistinct on multiple columns

Here is a late answer :-) though I'm not sure if things had changed.

Recently I encountered the very same need, and worked around it using concat, i.e., by concatenating the columns into a pseudo column, then countDistinct on the pseudo column.

But I couldn't use criteriaBuilder.concat because it generated JPQL using || for the concatenation, which Hibernate had trouble with.

Fortunately there's @Formula, thus, I mapped the pseudo column to a field with @Formula:

@Entity
public class MyEntity {
  @Column(name="col_a")
  private String colA;

  @Column(name="col_b")
  private String colB;

  @Formula("concat(col_a, col_b)") // <= THE TRICK
  private String concated;
}

This way I can finally use the concated field for CriteriaBuilder.countDistinct:

//...
Expression<?> exp = criteriaBuilder.countDistinct(entity.get("concated"));
criteriaQuery.select(exp);

TypedQuery<Long> query = entityManager.createQuery(criteriaQuery);
return query.getSingleResult();

I wish JPA would (or hopefully already) support countDistinct with multiple columns, then all these mess could have been avoided.

---

Related Topics