Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    LINQ to SQL problem

    I'm having a problem with OrderBy(). It's not ordering my rows correctly

    here is my LINQ query:

    Code:
    var mainTable = Data.Sales_TransactionLineItems.Where(item => itemList.Contains(item.Sales_Transaction.SiteDeviceShiftID) &&
                                                                                                                                         item.Inventory_Item.ItemCategoryID == 2 &&
                                                                                                                                         item.SiteProfitCenterID == 101).
                                                                                                                          GroupBy(g => new { g.Inventory_Item.PLU, g.ItemDescription }).
                                                                                                                          OrderBy(i => i.Key.PLU).
                                                                                                                          Select(s => new { s.Key.PLU, s.Key.ItemDescription, Qty = s.Sum(i => i.Qty), Price = s.Sum(i => i.ItemPrice) });
    That LINQ query returns this table: (question marks are classified info...)
    PLU ItemDescription Qty Price
    1 ??? ??? ???
    101 ??? ??? ???
    2 ??? ??? ???
    3 ??? ??? ???
    39 ??? ??? ???
    4 ??? ??? ???
    5 ??? ??? ???

    I want it to return this:
    PLU ItemDescription Qty Price
    1 ??? ??? ???
    2 ??? ??? ???
    3 ??? ??? ???
    4 ??? ??? ???
    5 ??? ??? ???
    39 ??? ??? ???
    101 ??? ??? ???

  • #2
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sorry for the F'ed up format. I'm kinda on a time constraint...

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Clearly a bad database design.

    Your PLU field is a CHAR or VARCHAR field when it should be an INT or at least a DOUBLE or DECIMAL.

    It *is* ordering by CORRECTLY for a STRING value.

    You may be able to fix it by CASTing or CONVERTing the field to an INT in the ORDER BY. Possibly via
    Code:
       OrderBy(i => CONVERT( INT, i.Key.PLU) ).
    thought I'm not sure whether LINQ will allow that conversion at that spot.

    But why in the world you would post a LINQ question in a MySQL forum is beyond me.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Feb 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    1. I didn't design the database.
    2. PLU IS an Int field in the database.
    3. The problem was with the ListView I'm pooring the data into. For some reason, it was set to sort by ascending (the ListView only knows it to be of string type)... silly me.
    4. I didn't see a LINQ section, MySQL was the closest I saw, sorry...

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Ahhh...so all you had to do was tell ListView to *NOT* do any sorting, at all? Fun stuff. You didn't mention the ListView so I didn't even consider that.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •