none
Ranking in LINQ c# with order by 3 fields in table

    Question

  • I have some transactions in a table

        receipt#      NameOfPerson   Amount      TransType
        1234          John                    200         Purchase
        1234          John                     100         Void
        899           Smith                   20          Purchase
        899           Smith                   200         Purchase

    I need to add a flag in my LINQ query so that I can show that the transaction is repeating for same receipt #, person, as shown below. I.e. The flag would be `True` for the first transaction for John and would be `false` for the rest. Similarly, `True` for Smith and would be `false` for the rest.To perform some manipulations, please help me with LINQ c# to perform the same as below in a list.

        receipt#      NameOfPerson   Amount      TransType        RepeatingFlag
        1234          John                     200         Purchase         True
        1234          John                     100         Void             Flase
        899           Smith                      20          Purchase         True
        899           Smith                     200         Purchase         Flase

    Friday, July 26, 2013 3:57 PM

Answers

  • Hi abcv;

    The following query will return what you need.

    var results = (from r in ReceiptsTable
                   group r by new { r.ReceiptNo, r.NameOfPerson } into myGroup
                   from g in myGroup
                   select new {
                       ReceiptNo = g.ReceiptNo,
                       Name = g.NameOfPerson,
                       Amount = g.Amount,
                       TransType = g.TransType,
                       RepeatingFlag = myGroup.Count( ) == 1 ? false :
                           (myGroup.Take(1).Where(f=> g.ReceiptNo == f.ReceiptNo
                                   && g.NameOfPerson == f.NameOfPerson
                                   && g.Amount == f.Amount
                                   && g.TransType == f.TransType)
                               .Select(s => s).Count(s => s != null ? true : false) == 1 ) ? true : false 
                   }).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.


    Friday, July 26, 2013 7:52 PM

All replies

  • Hi abcv;

    The following query will return what you need.

    var results = (from r in ReceiptsTable
                   group r by new { r.ReceiptNo, r.NameOfPerson } into myGroup
                   from g in myGroup
                   select new {
                       ReceiptNo = g.ReceiptNo,
                       Name = g.NameOfPerson,
                       Amount = g.Amount,
                       TransType = g.TransType,
                       RepeatingFlag = myGroup.Count( ) == 1 ? false :
                           (myGroup.Take(1).Where(f=> g.ReceiptNo == f.ReceiptNo
                                   && g.NameOfPerson == f.NameOfPerson
                                   && g.Amount == f.Amount
                                   && g.TransType == f.TransType)
                               .Select(s => s).Count(s => s != null ? true : false) == 1 ) ? true : false 
                   }).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.


    Friday, July 26, 2013 7:52 PM
  • Hi abcv,

    Since we haven't heard from you for a long time, I temporarily close this case. I mark useful reply as answer. If you have any concerns, please free feel to reopen it or submit a new question. Thanks for your understanding.

    Thanks

    Best Regards


    Starain Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 05, 2013 12:20 PM
    Moderator