logo
down
shadow

How to query the first entry in each group in NHibernate


How to query the first entry in each group in NHibernate

Content Index :

How to query the first entry in each group in NHibernate
Tag : chash , By : Pancilobak
Date : November 24 2020, 05:49 AM

seems to work fine It seems latest NHibernate 5.2 LINQ provider supports only aggregate functions (MIN, MAX, COUNT...) in Select for "group by" query.
I believe in HQL your query can be rewritten using aggregate functions as:
session.CreateQuery(
    "select a from A a where a.Id in "
        + "(select min(ga.Id) from A ga group by ga.X)").List<A>();
var subquery = session.Query<A>()
    .GroupBy(ga => ga.X)
    .Select(ga => ga.Min(a => a.Id))
    .ToList();//when bug is fixed this ToList call can be omitted and make results retrieved in single db call

var results = session.Query<A>().Where(a => subquery.Contains(a.Id)).ToList();
var results = session.Query<A>()
     .Where(a => a.Id == session.Query<A>().Where(sa => sa.X == a.X).Select(sa => sa.Id).Min())
     .ToList();

//or version closer to your original query:
var results = session.Query<A>()
     .Where(a => a == session.Query<A>().Where(sa => sa.X == a.X).OrderBy(sa => sa.Id).First())
     .ToList();

var results = session.Query<A>()
     .Where(a => a == session.Query<A>()
                         .Where(sa => sa.X == a.X) //Group BY key is here
                         .OrderBy(sa => sa.Y) //Order By key is here
                         .First())
     .ToList();

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

NHibernate Query with joins and group by


Tag : chash , By : Bimal Poudel
Date : March 29 2020, 07:55 AM
seems to work fine I have 4 assoiciated classes and need to do a query. , can you test this:
Process processAlias = null;
Association assocAlias = null;
FieldOfActivity actAlias = null;

var subquery = QueryOver.Of<MonthCapacity>()
    .Where(m => m.Association == assocAlias)
    .Select(Projections.Sum<MonthCapacity>(m => m.Hours));

var results = session.QueryOver<Project>()
    .JoinQueryOver(p => p.Processes, () => processAlias)
    .JoinQueryOver(p => p.Associations, () => assocAlias)
    .JoinAlias(p => p.FieldOfActivity, () => actAlias)
    .Select(p => new ChartDto { Project = p, FieldOfActivity = actAlias, Hours = Projections.Subquery(subquery) })
    .List();
var results = session.QueryOver<Project>()
    .JoinQueryOver(p => p.Processes, () => processAlias)
    .JoinQueryOver(p => p.Associations, () => assocAlias)
    .JoinAlias(p => p.FieldOfActivity, () => actAlias)
    .SelectList(list => list
        .Select(p => new ChartDto { Project = p, FieldOfActivity = actAlias, Hours = 0 })
        .Select(Projections.Subquery(subquery))
    )
    .List<object[]>()
    .Select(objects =>
    {
        var chart = (ChartDto)objects[0];
        chart.Hours = (int)objects[1];
        return chart;
    });

nhibernate group by join query


Tag : nhibernate , By : CSCI GOIN KILL ME
Date : March 29 2020, 07:55 AM
like below fixes the issue I have the following entities:
        var list = 
            this.Session.Query<Shift>().Select(
                p => new
                {
                    Shift = p,
                    LastShiftHistory =
                        this.Session.Query<ShiftHistory>()
                            .Where(sh => sh.Shift == p)
                            .OrderByDescending(sh => sh.ShiftEndLocal)
                            .Select(sh => sh)
                            .FirstOrDefault()
                })
                .ToList();

NHibernate query latest N per group


Tag : nhibernate , By : HokieGeek
Date : March 29 2020, 07:55 AM
I wish this help you I have the following domain mapped through FluentNHibernate , Join the group by result with the original rows and return the rows:
rows.Join(
   rows.GroupBy(x => new {x.Location, x.Hour}), 
   x => new {x.Location, x.Hour, x.Available}, 
   g => new {g.Key.Location, g.Key.Hour, Available = g.Max(p=>p.Available)},
   (x1,g1) => x1
)
SELECT [t0].*
FROM [rows] AS [t0]
INNER JOIN (
    SELECT MAX([t1].[Available]) AS [value], [t1].[Location], [t1].[Hour]
    FROM [rows] AS [t1]
    GROUP BY [t1].[Location], [t1].[Hour]) 
AS [t2] ON (
    [t0].[Location] = [t2].[Location]) 
    AND ([t0].[Hour] = [t2].[Hour]) 
    AND ([t0].[Available] = [t2].[value]
)

How to use Max and Group By in an NHibernate query?


Tag : chash , By : user107506
Date : March 29 2020, 07:55 AM
will help you How would I do the following query using NHibernate? , Managed to work it out, was more simple than I thought in the end:
            var result = from item in session.Linq<Item>()
                         group item by item.ClientId
                         into itemGroups
                         select new
                             {
                                 id = itemGroups.Key,
                                 max = itemGroups.Max(er => er.Id)
                             };

Django Query: Group by field and get the latest entry per group


Tag : python , By : Brandon
Date : March 29 2020, 07:55 AM
it helps some times looks like you need to add empty order_by() by the default-ordering-or-order-by in the result query can be added extra fields to the group by.
try it:
Market.objects.values('slug').annotate(Max('active')).order_by()
Related Posts Related QUESTIONS :
  • Multiple using statements with if condition
  • How do i increase a number by 1 in every line that contain the number 1
  • Add binding to elements that are created in codebehind
  • How to add a column in an existing AspNetUsers table
  • Order a list of elements with another list of doubles
  • How to setup a NuGet package to copy content files to output build directory?
  • In SignalR Core using ChannelWriter: Do I need to call TryComplete twice if there's an exception?
  • C# GetProcessesByName: issue with colon
  • c# wpf | create complex object with user-defined name to Serialize into JSON
  • How can I get a instance of a generic list with reflection?
  • WPF XAML - Design time and visibility of textbox
  • EF Core and MySql query is too slow
  • Getting Registered App Display Name from an App Id
  • How to get all variables from a string
  • Delete entity with all childs connected
  • Azure Build agent cant´t find class library referance
  • Initialize Nested Dictionaries in c#
  • .Net Core Binding
  • Generic event test method, preventing code duplication
  • How do I keep the ellipses in the center when the screen is resized
  • How to require a property using JsonSchema.NET?
  • C# XDocument Element/Elements returns null
  • Autofac keyed service with IEnumerable relationship type
  • Installing EntityFramework via NuGet manager
  • Always Check if there is Internet Connection Xamarin forms
  • WCF OneWay service slows down when aspNetCompatibilityEnabled is set to false
  • Can we use JsonRequestBehavior.AllowGet with [HttpPost] attribute?
  • How to customize the Setup wizard with custom forms in Visual Studio setup project
  • C# ASP.NET - Use method from another class to create labels
  • C# List IList or IEnumerable as argument
  • Parsing File with C# And Replace method
  • Losing special unicode characters in encryption (C#)
  • Getting stored procedure returned value instead of row affected
  • How can I construct HTML using NameValuePair in android?
  • Loading a pop up page in ASP.net through a js file
  • How to pass alert or notification message from controller to View?
  • C# to pause, turn on ssas server, backup cube.... how to?
  • How to execute DataTable.Select() for a column of custom class type for a particular element in that C#
  • how to connect mysql8.0 with C#
  • Passing incorrect values into MultiValueConverter by MultiBinding
  • Can i use IEnumerator as Update func?
  • How to convert API Json response to C# Array?
  • Blazor Textfield Oninput User Typing Delay
  • Performing both layout and render transform results in wrong output
  • uwp beforetextchanged cursor moving in front of text
  • How to keep duplicates from a string[] exclude words from a List and print them out
  • .Net Core Strings.Asc/Mid/Chr/Len missing even after importing Microsoft.VisualBasic
  • How to return to previous search page without being asked to Confirm Form Re-submission and keeping the results on ASP.N
  • How set a identity scaffolding item/page how initial page in asp.net MVC core?
  • LINQ isn't calling Dispose on my IEnumerator when using Union and Select, expected behavior or bug?
  • What is "ByteArray.uncompress()" in AS3 equivalent to in C#?
  • Getting a specific letter from a string variable for my simple guessing game for clues
  • Send an email with Outlook without a subject --- dialog box issue
  • passing List<MyModel> from my controller in the "WebInterfaceProject" to the processor method in "D
  • How to convert Word document created from template by OpenXML into MemoryStream?
  • How can I make a single slider that changes the color of an object?
  • Remap JSON parameter in c#
  • What is the difference between "this ref" and "ref this" when talking about C# 7.2 ref extension met
  • Convert OpenSSL encryption into native C#
  • Accessing Properties in Razor Pages
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com