none
clubbing a group of column values orderwise RRS feed

  • Question

  • type                      name              email
    ------------------- ------------------ -------------
    Administrator         a                 a@abc.com
    Administrator         b                 b@abc.com
    User                       x                 x@abc.com
    User                       y                 y@abc.com
    User                       z                 z@abc.com how do i compose the LINQ, it should display

    Administrator          a@abc.com; b@abc.com
    User                       x@abc.com; y@abc.com; z@abc.com
    Tuesday, April 3, 2012 10:07 AM

Answers


  • Something like this:

    var results = from t in DataContextName.TableName
                  group t by new { Type = t.type, Access = t.access } into tGroup
                  select new {
                      Type = tGroup.Key.Type,
                      Access = tGroup.Key.Access,
                      Email = tGroup.Select(n => n.email)
                  };
    
    foreach (var r in results)
    {
        string Emails = r.Email.Aggregate ((emails, next) => next + ", " + emails )
        Console.WriteLine(r.Type + "  " + r.Access + " " + Emails);
    }
      


    Fernando (MCSD)

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

    • Marked as answer by NabX Monday, April 9, 2012 5:01 AM
    Saturday, April 7, 2012 2:56 PM

All replies

  • Hi NabX;

    The following query will do what you are looking to do.

    var results = from t in DataContextName.TableName
                  group t by t.type into tGroup
                  select new {
                      Type = tGroup.Key,
                      Email = tGroup.Select(n => n.email)
                  };
    
    foreach (var r in results)
    {
        string Emails = r.Email.Aggregate ((emails, next) => next + ", " + emails )
        Console.WriteLine(r.Type + "  ", Emails);
    }  

    Will result in the following:

    Administrator    a@abc.com; b@abc.com
    User                 x@abc.com; y@abc.com; z@abc.com

        


    Fernando (MCSD)

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

    Tuesday, April 3, 2012 1:16 PM
  • i am new in LINQ, could you please let me know what

    select new {
    Type = tGroup.Key,
    Email = tGroup.Select(n => n.email)
    };

    is doing?

    Also

    r.Email.Aggregate ((emails, next) => next 
    Thanks
    Wednesday, April 4, 2012 4:57 AM
  • Hi NabX;

    To your question, "let me know what (query) is doing?".

    select new {
                   Type = tGroup.Key,
                   Email = tGroup.Select(n => n.email)
               };

    The select keyword selects the fields to be returned from the query. The new keyword in a query creates a Anonymous type objects. The reaason for an anonymous type is because we are not returning all the fields of the table. The variable tGroup.Key is what we grouped the records in, which in this case is the field type. And the part of the statement tGroup.Select(n => n.email) is creating a list of email address for each group.

    To the part of the question, "r.Email.Aggregate ((emails, next) => next + ", " + emails ).

    For each r in the result of the query you have one anonymous object that has two fields. With the part of the query r.Email is selecting the Email field of the anonymous type. The Aggregate in the query takes the input, Emails, which is a list of strings and makes one string object consisting of all the emails. Please see the documentation on the Aggregate method.

    Enumerable.Aggregate Method

      


    Fernando (MCSD)

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

    Wednesday, April 4, 2012 12:43 PM

  • if we have another column

    type                      name          access               email
    ------------------- ------------------ -------------     -----------
    Administrator         a                admin            a@abc.com
    Administrator         b                admin             b@abc.com
    User                       x                user               x@abc.com
    User                       y                user               y@abc.com
    User                       z                user              z@abc.com


    how do i form the query to fill in access into the query

    Administrator   admin  a@abc.com; b@abc.com
    User                 user     x@abc.com; y@abc.com; z@abc.com

    Thanks

    Saturday, April 7, 2012 2:41 PM

  • Something like this:

    var results = from t in DataContextName.TableName
                  group t by new { Type = t.type, Access = t.access } into tGroup
                  select new {
                      Type = tGroup.Key.Type,
                      Access = tGroup.Key.Access,
                      Email = tGroup.Select(n => n.email)
                  };
    
    foreach (var r in results)
    {
        string Emails = r.Email.Aggregate ((emails, next) => next + ", " + emails )
        Console.WriteLine(r.Type + "  " + r.Access + " " + Emails);
    }
      


    Fernando (MCSD)

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

    • Marked as answer by NabX Monday, April 9, 2012 5:01 AM
    Saturday, April 7, 2012 2:56 PM
  • Thank you ever so much...could you please give me a pointer or link for learning LINQ in detail.

    Is there any link, which actually maps the SQL conceptions/T-SQL statements to LINQ(providing the learning curve for the SQL to LINQ).

    Many Thanks...

    Monday, April 9, 2012 5:01 AM
  • Hi NabX;

    I wish I can say that there is a web site that show how to use Linq but I have yet to find one.

    to your question, "Is there any link, which actually maps the SQL conceptions/T-SQL statements to LINQ(providing the learning curve for the SQL to LINQ).", you will find out that there is no one to one relationship between T-SQL and the Linq to SQL so you can not look at a SQL statement and have a translation directly into Linq to SQL.

    One way to learn Linq is by doing and there is a great tool called LinqPad which can be downloaded for free at the LinqPad web site. The author also has a plugin LINQPad Autocompletion but cost $39.00 which give LinqPad intelliseance for the C# language only.

    Also the Microsoft site 101 LINQ Samples for Visual C#


    Fernando (MCSD)

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

    Monday, April 9, 2012 1:01 PM