Query problem

Topics: Troubleshooting
Jun 15, 2011 at 11:00 PM
Edited Jun 15, 2011 at 11:13 PM

I'm hoping someone here can help me with this and maybe suggest a way to make this work.

In my module, I have a query that looks like this:

 

var seasonleaders = (from points in _pointsRepository.Table
                     where...
                     group points by points.User.Id into leaders
                     orderby leaders.Sum(p => p.Points) descending
                     select new { User = leaders.Key, TotalPoints = leaders.Sum(x => x.Points) }).Take(5);

 

The points table contains multiple rows for a given user with the points they've scored over the season...so the goal is to add up the points for each user and take the top 5.

This query works fine if I take out the orderby clause, however, including it appears to cause seasonleaders to have zero results. It's almost acting like a filter instead of ordering the results.

Any ideas on why this is happening and perhaps a way to get this query to produce the results ordered the way I need them? Thanks

Jun 15, 2011 at 11:11 PM
Edited Jun 15, 2011 at 11:15 PM

Tried it this way with the same results

 

var seasonleaders = (from points in _pointsRepository.Table
                     where...
                     group points by points.User.Id into leaders
                     select new { User = leaders.Key, TotalPoints = leaders.Sum(x => x.Points) }).OrderByDescending(y => y.TotalPoints).Take(5);

also tried taking off the .Take(5) to see if that changed anything and it didn't.
Jun 15, 2011 at 11:26 PM
Edited Jun 15, 2011 at 11:36 PM

hmmm...I may have found something that works. If I take off the OrderByDescending and Take and move them outside the initial LINQ query to their own line, I don't get an error any more. It now looks like this:

 

var seasonleaders = (from points in _pointsRepository.Table
                     where...
                     group points by points.User.Id into leaders
                     select new { User = leaders.Key, TotalPoints = leaders.Sum(x => x.Points) });

seasonleaders.OrderByDescending(y => y.TotalPoints).Take(5);

 

Not sure if it's actually working...I'll have to do more testing, but at least the error is gone.

UPDATE - that didn't actually work...I didn't have the syntax correct.

Jun 15, 2011 at 11:44 PM

Okay...now I got it. If I cast it to a list before doing the OrderByDescending and Take, it works. Final code is:

var seasonleaders = (from points in _pointsRepository.Table
                     where...
                     group points by points.User.Id into leaders
                     select new { User = leaders.Key, TotalPoints = leaders.Sum(x => x.Points) }).ToList().OrderByDescending(y => y.TotalPoints).Take(5);

This one seems to work fine

Jun 16, 2011 at 11:19 AM

The problem there is that you're reading all the users from the database and then performing the sort in-memory - which for a large users database is likely to give you performance issues.

I can't see exactly what's going wrong with those other queries but there must be something. Exactly what happens if you take out the ToList() in that last example?

Jun 16, 2011 at 11:26 AM

Does this work:

var seasonleaders = (from points in _pointsRepository.Table
                     where...
                     group points by points.User.Id into leaders
                     let total = leaders.Sum(x => x.Points)
                     orderby total descending
                     select new { User = leaders.Key, TotalPoints = total }).Take(5);

Jun 16, 2011 at 8:58 PM

I tried using the let statement as well...adding the let statement by itself works fine, but as soon as I add "orderby total descending", I get the same error.

If I take out the ToList() in the one example that works, the next piece of code complains that the objects in seasonleaders are null. Here is the next section of code:

if (seasonleaders != null)
{
    foreach (var leader in seasonleaders)
    {
        ...
    }
}
The null complaint is on leader...so it's passing the check to see if seasonleaders contains anything, but then complaining that the objects within seasonleaders are null.

Jun 17, 2011 at 9:13 AM

Without debugging it's hard to guess what's going on, but it kind of sounds like a bug either in NHibernate or in the IRepository<T> implementation. Can you post the code for your record and migration classes so I can just check it's not something with the way you define the schema?

Jun 20, 2011 at 11:33 PM

Sorry for the delayed response...had a huge Luau over the weekend to celebrate my recent marriage so I was a bit busy and today I moved my site from Azure to GearHost. Azure was racking up too many charges for a site that's not even in production yet and was barely even getting hit by me. Anyways...here is the code for the record in question:

using System;
using Orchard.Users.Models;

namespace Raptor.MyRealityPicks.Models
{
    public class PointsRecord
    {
        public virtual int Id { get; set; }
        public virtual ShowRecord Show { get; set; }
        public virtual SeasonRecord Season { get; set; }
        public virtual EpisodeRecord Episode { get; set; }
        public virtual LeagueRecord League { get; set; }
        public virtual UserPartRecord User { get; set; }
        public virtual CategoryRecord Category { get; set; }
        public virtual int Points { get; set; }
    }
}

and here is the migration section for that model:

            // Creating table PointsRecord
            SchemaBuilder.CreateTable("PointsRecord", table => table
                .Column("Id", DbType.Int32, column => column.PrimaryKey().Identity())
				.Column("Points", DbType.Int32)
				.Column("Show_Id", DbType.Int32)
				.Column("Season_Id", DbType.Int32)
				.Column("Episode_Id", DbType.Int32)
				.Column("League_Id", DbType.Int32)
				.Column("Category_Id", DbType.Int32)
                .Column("User_Id", DbType.Int32)
			);
Let me know if you see anything I've done wrong and need to change...thanks

Jun 21, 2011 at 3:19 AM

Well ... it could be the old capital 'I' again ... your column names should be Show_id, Season_id etc. with a lowercase i. It seems things sometimes work with different capitalisation, but a few people have posted database problems and I've advised this and it fixed it. So try that first! (Doesn't seem related in this case but you never know)

Jun 21, 2011 at 8:10 PM

I'll give that a shot and report back. Thanks!

Jun 21, 2011 at 8:44 PM

Negative...same result. But at least all of my columns are named properly now =)

Jun 22, 2011 at 6:37 PM

Can you run a SQL profiler and capture the actual SQL that gets executed on the database?