logo
down
shadow

Join tables with nested select count and group by in Nhibernate HQL


Join tables with nested select count and group by in Nhibernate HQL

By : user3851558
Date : October 16 2020, 11:12 AM
I think the issue was by ths following , For what you are trying to do, you do need to write any HQL query Since you have collections in your classes and you have mapped them in your .hbm.xml files like (if you are using fluent nhibernate ignore this)
code :


Share : facebook icon twitter icon
Count Distinct - Inner Join - Group By / With the new Linq-to-NHibernate Provider

Count Distinct - Inner Join - Group By / With the new Linq-to-NHibernate Provider


By : Garrett Mitchell
Date : March 29 2020, 07:55 AM
Does that help I don't think it's currently possible to do the count(distinct x) part.
This is the closest I got:
code :
from comment in session.Query<Comment>()
group comment by comment.Post.Title
      into g
      select new
             {
                 Title = g.Key,
                 Count = g.Select(x => x.UserId).Distinct().Count()
             };
from comment in session.Query<Comment>()
group comment by comment.Post.Title
      into g
      select new
             {
                 Title = g.Key,
                 Count = g.Count()
             };
SELECT Post.Title, COUNT(*)
FROM Comment
LEFT JOIN Post ON Post.Id = Comment.PostId
GROUP BY Post.Title
Nhibernate join with select columns from both tables

Nhibernate join with select columns from both tables


By : user3287797
Date : March 29 2020, 07:55 AM
wish of those help Ok after several attempts I am stuck on this one!
code :
Product px = null;
ProductReview rev = null;

var result = CurrentSession.QueryOver<ProductReview>()
    .Where(r => r.IsActive && !r.IsDraft)
    .JoinQueryOver(r => r.Product)
    .OrderBy(r => r.ReviewDate).Desc()
    .Take(5)
    .List();
How to select count in NHibernate HQL when joining tables

How to select count in NHibernate HQL when joining tables


By : Luciana Nunes
Date : March 29 2020, 07:55 AM
hope this fix your issue You don't need an explicit join, nor a fetch for your query.
This should be enough:
code :
select count(*)
from TeacherResource
Where Product.CatalogTitle like '%ame%'
MySQL select using group and count for join tables

MySQL select using group and count for join tables


By : ASHOK BANJARA
Date : March 29 2020, 07:55 AM
this one helps. For my project, I've a student table and a school table. , Consider following SQL query:
code :
SELECT school.NAME, 
       count(student.id) AS Total_Student 
FROM   school 
       LEFT JOIN student 
              ON school.id = student.school_id 
GROUP BY school.id
ORDER BY Total_Student
LINQ Group by on multiple tables with nested group by with join and aggregate

LINQ Group by on multiple tables with nested group by with join and aggregate


By : Elisa
Date : March 29 2020, 07:55 AM
wish help you to fix your issue One of the greatest features of EF is the so called navigation properties. When used inside the LINQ to Entities queries, they provide the necessary metadata to build the necessary joins when translating the query to SQL. And allow you to build the queries like if they are run on objects, which basically eliminates the need to think about joins, but concentrate on your logic.
Let say your model is something like this (showing navigation properties only):
code :
public class Invoice
{
    // ...
    public ICollection<InvoiceDetail> Details { get; set; }
}

public class InvoiceDetail
{
    // ...
    public ICollection<Mixing> Mixings { get; set; }
}
var query =
    from i in db.Invoices
    let TOTALCARET = i.Details.Sum(d => (decimal?)d.CARET) ?? 0
    let USEDCARET = i.Details.SelectMany(d => d.Mixings).Sum(m => (decimal?)m.CARETUSED) ?? 0
    select new
    {
        i.INVOICENO,
        i.DATE,
        TOTALCARET,
        USEDCARET,
        AVAILABLECARET = TOTALCARET - USEDCARET
    };
shadow
Privacy Policy - Terms - Contact Us © bighow.org