Get Count of two columns using NHibernate

Question

I need to get the count of two fields using NHibernate query. Given the sample data of historicalList below, the result should be 1 Project assign to 2 Nodes. How to get the result using the Nhibernate query. Please see below my approach, Anyone can help to rewrite the code?

//Sample Data
var nodelist= new List<Node>{
new Node{1, "Node1"},
new Node{2, "Node2"},
new Node{3, "Node3"},
new Node{4, "Node4"},
new Node{5, "Node5"},
}

var projectlist= new List<Project>{
new Project{1, "Project1"},
new Project{2, "Project2"},
new Project{3, "Project3"},
new Project{4, "Project4"},
new Project{5, "Project5"},
}

var historicalList= new List<Historical>
{
 new Historical{1, 1,1}
 new Historical{1, 1,2}
}



public class Node
    {
        public virtual long ID { get; set; }
        public virtual string NodeName { get; set; }
    }

 public class Project
    {
        public virtual long ID { get; set; }
        public virtual string ProjectName { get; set; }
    }


public Historical
{
        public virtual long ID { get; set; }
        public virtual string ProjectID { get; set; }
        public virtual string NodeID { get; set; }     
}


//sample code 

 using (var session = OpenSession())
            {
                var historical= session.Query<Historical>()
              .Where(
                  x => nodeIds.Contains(x.Node.ID));
                var nodeCount = historical.Select(y => y.Node.ID).Distinct().Count();
                var projectCount = historical.Select(y => y.Project.ID).Distinct().Count();


            }

Michael Buen · Accepted Answer · 2015-07-29 01:09:39Z

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.

Najera · Accepted Answer · 2015-07-29 15:34:35Z

2

You can use join on Linq to NHibernate API directly even if you don't have the regular mapping relations to use QueryOver.

var historical = session.Query<Historical>()
    .Where(x => nodeIds.Contains(x.Node.ID));

var ncv = (from h in historical
           join n in session.Query<Node>() on h.NodeID equals n.ID
           select h).ToFutureValue(x => x.Count());

var pcv = (from h in historical
           join p in session.Query<Project>() on h.ProjectID equals p.ID
           select h).ToFutureValue(x => x.Count()); // Future is not required here

var nodeCount = ncv.Value;
var projectCount = pcv.Value;

edited Jul 29, 2015 at 15:34

answered Jul 27, 2015 at 17:37

Najera

2,8793 gold badges30 silver badges52 bronze badges

Collectives™ on Stack Overflow

Get Count of two columns using NHibernate

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related