locked
groupby and orderby thenorderby - results not as expected RRS feed

  • Question

  • User379720387 posted

    I am converting some nasty bloated code from straight SQL to Linq

    This the SQL:

    SELECT * FROM
                    (
                    SELECT location.OwnerId AS ownerLocationId, location.Location, apptDate, sss.schedSvcVisitId
                    , COALESCE(co.calOId, '8') AS cOId
                    , CASE WHEN visitStatus = 'Initial' THEN 'tbd' ELSE visitStatus END AS isActive
                    , CASE WHEN ovn.schedSvcVisitId IS NULL THEN 'False' ELSE 'True' END AS isOvn
                    , SUM(Charge) AS Charge
                    FROM ScheduledSvcVisit sss
                    JOIN Owners location on sss.ownerLocationId=location.ownerId
                    LEFT JOIN OwnerVisitNotice ovn on ovn.schedSvcVisitId=sss.schedSvcVisitId
                    LEFT JOIN calOrder co ON sss.calOId=co.calOId
                    left JOIN Transactions t on t.providerId=sss.enteredByproviderId and t.ownerLocationId=location.ownerId and t.IsBilled=1 and t.ispaid=0
                    WHERE sss.EnteredByProviderId=@0
                    GROUP BY location.OwnerId, location.Location, ApptDate, visitStatus, dateScheduled, COALESCE(co.calOrder, 'tbd'), COALESCE(co.calOId, '8'), sss.schedSvcVisitId, ovn.schedSvcVisitId
                    ) As providerclients
                WHERE isActive IN ('Scheduled', 'Invited')
                ORDER BY apptDate ASC, cOId ASC

    What I have so far is this:

    var appts = dbContext.ScheduledSvcVisits
            .Where(s => s.enteredByProviderId == providerId && (s.visitStatus == "Scheduled" || s.visitStatus== "Invited"));
    
        var apptDaysBeforeOrdering = from apptDay in appts group apptDay by apptDay.apptDate into newGroup orderby newGroup.Key select newGroup;
    
        var apptDays = from apptDay in apptDaysBeforeOrdering orderby apptDay.Key select apptDay;

    Couple of questions and a challenge:

    1. the challenge is the missing ORDER BY cOId in apptDays. How do I reference co.calOId?
    2. questions are if it is normal that sort and order by instructions are done separately? If not, how can I get things sorted in appts? 

    Friday, May 22, 2020 4:01 PM

All replies

  • User1535942433 posted

    Hi wavemaster,

    the challenge is the missing ORDER BY cOId in apptDays. How do I reference co.calOId?

    Accroding to your description and codes,as far as I think,you could use ThenBy to reference co.calOId.

    Just like this:

    var query = s.OrderBy(s=> s.apptDay.Key).ThenBy(s=> s.calOId);

    More details.you could refer to below article:

    https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable.thenby?view=netcore-3.1

    questions are if it is normal that sort and order by instructions are done separately? If not, how can I get things sorted in appts? 

    I suggest you could use linqPad to check your codes.

    https://www.linqpad.net/

    Best regards,

    Yijing Sun

    Monday, May 25, 2020 8:57 AM
  • User379720387 posted

    delete, I am going to repost

    Monday, May 25, 2020 3:11 PM
  • User379720387 posted

    Grouping and Sorting is not working as expected.

    My dataset, for the conditions per OP:

    1: apptDate May 24, calOId = 2

    2: apptDate May 27, calOId = 1

    3: apptDate May 24, calOid = 1

    The intended result is:

    1: ApptDate May 24:

    1.1 calOId = 1

    1.2 calOId = 2

    2: ApptDate May 27:

    2.1 calOId = 1

    var visits1 = dbContext.ScheduledSvcVisits.Where(s => s.enteredByProviderId == providerId && (s.visitStatus == "Scheduled" || s.visitStatus == "Invited"))
    .OrderBy(s => s.apptDate).ThenBy(s => s.calOId)
    .GroupBy(s => s.apptDate);

    or
    var test = from visit in visits orderby visit.apptDate orderby visit.calOId group visit by visit.apptDate into newGroup select newGroup;

    I have since learned that OrderBy needs to go first and GroupBy last, and I have the two queries as per that approach

    The squiggle is now gone.

    The result (from visists1 or test) is still NOT as expected:

    1: ApptDate May 24:

    1.1 calOId = 2

    1.2 calOId = 1

    2: ApptDate May 27:

    2.1 calOId = 1

    Not matter if I do ASC or DESC Linq equivalents calOId orderby is not working right, they need to be reversed!

    I am missing something here, but what?

    Monday, May 25, 2020 4:30 PM
  • User1535942433 posted

    Hi wavemaster,

    Accroding to your description, I suggest you could try just like this:

    public class visits
            {
                public int apptDate { get; set; }
                public int calOId { get; set; }
            }
    
            static void Main(string[] args)
            {
                List<visits> visits = new List<visits>
                {
                    new visits {apptDate=24,calOId=2},
                    new visits {apptDate=27,calOId=1},
                    new visits {apptDate=24,calOId=1},
    
               };
                var results = (from x in visits
                               group x by new
                               {
                                   apptDate = x.apptDate,
                                   calOId = x.calOId
                               } into grp
                               orderby grp.Key.apptDate, grp.Key.calOId
                               select new
                               {
                                   apptDate = grp.Key.apptDate,
                                   calOId = grp.Key.calOId
                               }).OrderBy(a => a.apptDate).ThenBy(a => a.calOId).ToList();
            }

    Result:

    Best regards,

    Yijing Sun

    Friday, May 29, 2020 9:13 AM
  • User379720387 posted

    I am sending you a link by PM showing the db diagram for this functionality. It consists of 6 Entities.

    For this to work I need to be able to navigate to all of these fields from results.

    As I understand your proposed approach I would have to individually name each field, or is there a way to keep all the fields included in results?

    Friday, May 29, 2020 11:42 AM
  • User303363814 posted

    Details matter.  In some places you have calOld where the second last character seems to be a lower case letter ell.

    Some places it is calOid where the second last character is a lowercase letter eye.

    In the sample code it is calOId where the second last character is an upper case eye.

    Are these things really the same? Can you show your class definitions?

    If I have this class definition

    class DateId
    {
       public DateTime ApptDate {get; set;}
       public int calId {get; set;}
    	
       public DateId(int day, int id)
       {
          ApptDate = new DateTime(2020,5, day);
          calId = id;
       }
    }

    Then I can create some data like this

    var data  = new List<DateId> {
                   new DateId(24, 2),
                   new DateId(25, 1),
                   new DateId(24,1)
                 };
    

    And finally group and sort it like this

    data.GroupBy(d => d.ApptDate)
        .OrderBy(d => d.Key)
        .Select(d => new {
                    ApptDate = d.Key,
                    Ids = d.Select(grp => grp.calId).OrderBy(id => id)
                         }
               )
    

    (LinqPad is a great tool for experimenting with stuff like this.  It's free!)

    Saturday, May 30, 2020 11:04 AM
  • User379720387 posted

    calOId

    cal for calendar

    O for Order

    Id for identity

    There is nothing else that looks like calOId.

    There are at least 6 tables (classes) involved, and about 60 columns.  I made an attempt to simplify things by referencing the columns involved the GroupBy and OrderBy.

    yij sun's solution works, however none of the other columns and related tables are in the final result, and I don't have the understanding of Linq to fix that myself.

    Linq so far has been a journey with more questions, less readability, and unexpected pitfalls (like this GroupBy/OrderBy). I looked at LinqPad real quick, and it seems like a nice tool for someone who understands Linq.

    Saturday, May 30, 2020 12:49 PM
  • User475983607 posted

    Use SQL or a stored procedure if you already have the query.  The DbContext lets you execute raw queries.  There is no rule that you have to use LINQ.  Don't make coding harder than needed.  https://docs.microsoft.com/en-us/ef/ef6/querying/raw-sql

    Saturday, May 30, 2020 2:44 PM
  • User379720387 posted

    Ok, so this is what the db structure is for this piece of the functionality:

    SELECT *
                    FROM ScheduledSvcVisit sss
                    JOIN Appointments a ON sss.schedSvcVisitId = a.schedSvcVisitId
                    JOIN Owners location on sss.ownerLocationId=location.ownerId
                    JOIN OwnerVisitNotice ovn on ovn.schedSvcVisitId=sss.schedSvcVisitId
                    JOIN calOrder co ON sss.calOId=co.calOId
                    JOIN Transactions t on t.providerId=sss.enteredByproviderId
                    JOIN Clients c ON a.clientid = c.clientid
                    WHERE *** conditions ***

     I have expanded on the query provided by yij sun to get all columns from ScheduledSvcVisit, as well as an attempt to get Appointments and OwnerVisitNotice:

    var results = (from x in vsts
                       group x by new
                       {
                           schedSvcVisitId = x.schedSvcVisitId,
                           apptDate = x.apptDate,
                           calOId = x.calOId,
                           ownerLocationId = x.ownerLocationId,
                           dateScheduled = x.dateScheduled,
                           enteredByProviderId  = x.enteredByProviderId,
                           visitStatus = x.visitStatus,
                           Appointments = x.Appointments,
                           OwnerVisitNotices = x.Appointments.OwnerVisitNotices
                       } into grp
                       orderby grp.Key.apptDate, grp.Key.calOId
                       select new
                       {
                           schedSvcVisitId = grp.Key.schedSvcVisitId,
                           apptDate = grp.Key.apptDate,
                           calOId = grp.Key.calOId,
                           ownerLocationId = grp.Key.ownerLocationId,
                           dateScheduled =  grp.Key.dateScheduled,
                           enteredByProviderId = grp.Key.enteredByProviderId,
                           visitStatus = grp.Key.visitStatus,
                           Appointments = grp.Key.Appointments,
                           OwnerVisitNotices = grp.Key.Appointments.OwnerVisitNotices
                       }).OrderBy(a => a.apptDate).ThenBy(a => a.calOId).ToList();

    I can get all columns from ScheduledSvcVisit, so that is good.

    However, the highlighted pieces above result in an exception "The key selector type for the call to the 'GroupBy' method is not comparable in the underlying store provider."

    How do I get all the other tables as shown in the SQL script above?

    Sunday, May 31, 2020 2:00 AM
  • User303363814 posted

    Your sql is a join of seven tables.

    In linq you would use the join clause to do the same sort of operation as an sql join.

    Can you write a linq query which joins two tables?  You should be able to get started by reading https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-inner-joins

    (Inner joins are the most common type and probably the one you want, outer joins are much less common)

    Once you have a join of two tables worked out, then three is not much harder and after that it is trivial. 

    Guessing ... is x.Appointments a collection of things?  If so, you will not be able to group on that.  I think it would be quite difficult for a database provider to implement grouping on a collection since "A,B,C" would need to be the same as "C,A,B" and all other combinations.

    Sunday, May 31, 2020 4:08 AM
  • User379720387 posted

    I am thinking I shouldn't have to do that since I am using EF 6.4

    var results = dbContext.ScheduledSvcVisits
            .Where(s => s.enteredByProviderId == providerId && (s.visitStatus == "Scheduled" || s.visitStatus== "Invited"));
    
    

    This gives me everything I need, except when I add groupby and orderby it doesn't work right.

    Sunday, May 31, 2020 10:17 AM
  • User303363814 posted

    that
    What does this mean?  What feature of EF 6.4 are you referring to>

    it doesn't work right.
    This bit needs more detail

    Monday, June 1, 2020 12:37 AM
  • User379720387 posted

    My apologies for not being precise.

    This query below:

    var results = dbContext.ScheduledSvcVisits
            .Where(s => s.enteredByProviderId == providerId && (s.visitStatus == "Scheduled" || s.visitStatus== "Invited"))
    .OrderBy(s => s.apptDate).ThenBy(s => s.calOId)
    .GroupBy(s => s.apptDate);

    if it wasn't for the GroupBy and OrderBy not working would allow me to do this:

    foreach (var notice in ScheduledSvcVisits.Appointments.OwnerVisitNotices)
    {
        some html @notice.dateSent.ToString("")
    }
    
    

    I.e. I can navigate through the Entities without ever having to do a join, or know which columns were in a particular table. Just click on the arrows and navigate to where I need to be.

    And EF (data first) would tell me that a ScheduledSvcVisits has Appointments, while a Appointment has exactly one Client.

    What is making me say "doesn't work right" refers to the Grouping of ScheduledSvcVisits by apptDate AND Ordering by apptDate and calOId.

    From above:

    The result is NOT as expected:

    1: apptDate May 24:

    1.1 calOId = 2

    1.2 calOId = 1

    2: apptDate May 27:

    2.1 calOId = 1

    I am thinking that a GroupBy on apptDate then OrderBy on apptDate and calOId should give me this:

    1: apptDate May 24:

    1.1 calOId = 1

    1.2 calOId = 2

    2: apptDate May 27:

    2.1 calOId = 1

    Or am I mistaken?

    Monday, June 1, 2020 1:23 AM
  • User303363814 posted

    This seems to be going round and round in circles.

    You show a linq query which has neither groupby nor join and then complain that groupby and join do not work!  Your linq query returns something which you have called 'results'.  Then in the foreach you go back to the raw DbSet, still no sign of the famously not working groupby or join.

    "ie I can navigate through the Entities without ever having to do a join or know which columns were in a particular table"

    Are you talking about lazy loading now?  How does this example mean you don't know which columns were in a particular table?  Are you expecting to access data in a database without knowing the names of the columns?  It is possible, but you will have a fair bit of work in front of you.  Or are you now talking about intellisense?

    'What is making me say "doesn't work right"' .. you realise that you actually have to do it before it either works right or wrong?  There is no groupby and no join in this latest post.  I showed you how to do it to implement what I guessed you were wanting.

    If my guess was wrong then, please, please, please, make a small example.  One that anyone can run anywhere.  Show us the example.  Tell us what results you are seeing and what you want to see.  One problem at a time.

    Monday, June 1, 2020 1:51 AM
  • User379720387 posted

    This query has:

    the db.Context starting from ScheduledSvcVisits;

    GroupBy apptDate

    OrderBy apptDate

    What is missing is the OrderBy calOId.

    Tell me which combination and order of groupby's orderby's and thenby's will get me calOId in order?

    var vsts = dbContext.ScheduledSvcVisits.Where(s => s.enteredByProviderId == providerId && (s.visitStatus == "Scheduled" || s.visitStatus == "Invited")).GroupBy(d => d.apptDate).OrderBy(d => d.Key);

    Links to pdf with some screen clips directly from VS.

    I have tried any combination and order of these elements:

    .OrderBy(s => s.apptDate).ThenBy(s => s.calOId)

    .GroupBy(s => s.apptDate)

    Always resulting in calOId 2 and then calOId 1

    https://1drv.ms/b/s!Ap6uenbtw3z50TgbxEPDyBXVaE6h?e=l0Zffd

    Monday, June 1, 2020 9:53 AM
  • User753101303 posted

    Hi,

    Showing too much code variation makes harder to understand what you tried or want. If I understood for now you can group data on a date and the missing part is sorting on a child collection within each group?

    apptDate and calOId are part of the same table? 

    Edit: when something goes wrong and I can't make progress I prefer to test on sample which is as simple as possible to test really just the particular point on which I have my issue.

    Monday, June 1, 2020 10:52 AM
  • User379720387 posted

    apptDate and calOId are part of the same table? 

    Yes, both are from ScheduledSvcVisit, among a handful of others.

    This is the query result before grouping and ordering:

    schedSvcVisitId ownerLocationId apptDate enteredByProviderId visitStatus calOId
    129   60   2020-05-28 00:00:00.000   22   Scheduled   1
    132   70   2020-05-27 00:00:00.000   22   Invited     2
    133   61   2020-05-27 00:00:00.000   22   Scheduled   1



     

    Monday, June 1, 2020 11:29 AM
  • User303363814 posted

    Tell me which combination and order of groupby's orderby's and thenby's will get me calOId in order?

    There is an example, using your property names at https://forums.asp.net/post/6306582.aspx

    Tuesday, June 2, 2020 2:28 AM
  • User379720387 posted

    Let's say the grouping and ordering worked, so that is a success.

    However, below just gives me the Ids, really need the rest of db.Context here. Just like in the images of yesterdays pdf.

    Ids = d.Select(grp => grp.calOId).OrderBy(id => id)

    See pic with red circle.

    https://1drv.ms/b/s!Ap6uenbtw3z50Trqz9InMIsYJcI2?e=AxzIdg

    Tuesday, June 2, 2020 9:51 AM
  • User303363814 posted

    SO, is this now a new question? "How can I return more than one property in a Select clause?"  It might be time to start a new thread ..

    But

    .Select(thing => new {thing.prop1, thing.prop2, total = thing.prop3.Sum(p => p.Price) } )

    Tuesday, June 2, 2020 11:11 PM
  • User379720387 posted

    The results are still "not as expected", as dbContext has vanished. I.e. there is no longer a way for me to navigate to the other Entities

    new {thing.prop1, thing.prop2, total = thing.prop3.Sum(p => p.Price)}

    is:

    Ids = d.Select(grp => new { grp.calOId, grp.enteredByProviderId, grp.Appointments } ).OrderBy(id => id)

    ScheduledSvc is one to many with Appointments. Having grp.Appointments results into an error message:

    DbSortClause expressions must have a type that is order comparable.
    Parameter name: key

    I can do I count: grp.Appointments.Count but I need everything in Appointments returned, and the same for the other related tables.

    I don't how to say this but  so my best description is how do I keep dbContext after grouping and ordering?

    Friday, June 5, 2020 3:46 PM
  • User303363814 posted

    Make an example that anyone can run.

    Saturday, June 6, 2020 10:48 PM
  • User379720387 posted

    In the process of making an example it was concluded the table relationships were such that the groupby was destined to fail.

    Thanks all for your input.

    Wednesday, June 17, 2020 7:26 PM