none
Using Linq to combine column into row? RRS feed

  • Question

  • I am not sure if using Linq is proper for what I need. Here's the following tables:

    T_Order:                                  T_Product:
     
    id     CustID     ProdID              ProdID          ProdName
     
    1         1            6                      1                Nutrisse Brown
     
    2         1            8                      2                Nutrisse Blond
     
    3         1            2                      3                Nutrisse Black
     
    4         1            1                      4                Head & Shoulder Dry
     
    5         2            8                      5                Head & Shoulder Wet
     
    6         2            3                     6                Ivory
     
    7         3            7                      7                Coast
     
    8         3            7                      8                Irish Spring
     
    9         3            2                     9                GoldCoast                                          
     
    10       3            5                                           
     
    11       3            9 
     
    The results I wanted is:
     
    custid      productname
     
       1          Ivory, Irish Spring,  Nutrisse Blond, Nutrisse Brown
     
       2          Irish Spring,  Nutrisse Black
     
       3          Coast, Coast, Nutrisse Blond, Head & Shoulder Wet, GoldCoast
    
    

     I got my feet wet in linq before but very novice. So I am not sure how to do this quickly. I tried XML Path earlier but it seems too low as realistically, I have 2-3 table with 18k rows. One table have 26 mill rows.

    Friday, October 21, 2011 5:34 AM

Answers

  • Hi BlueMarker;

    I converted my code snippet to use Linq to DataSet, using this query does not require the use of a DataContext.

    The below query assumes that ds.Tables[0] is T-Order and ds.Tables[1] is T_Product if this is not the case reverse the Tables[index] around.

    var results = from order in ds.Tables[0].AsEnumerable()
                  join prod in ds.Tables[1].AsEnumerable() on order.Field<int>("ProdID") equals prod.Field<int>("ProdID")
                  group prod by order.Field<int>("CustID") into custGroup
                  select new
                  {
                      CustID = custGroup.Key,
                      Products = custGroup.Select( n => n.Field<string>("ProdName" ))
                  };
    
    foreach( var cust in results )
    {
        string products = cust.Products.Aggregate( "", ( buildStr, nextName ) => buildStr + ", " + nextName ).Substring( 1 );
        Console.WriteLine( cust.CustID + "\t" + products );
    } 
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by BlueMarker Friday, October 21, 2011 8:14 PM
    Friday, October 21, 2011 4:27 PM

All replies

  • Hi,

    I think this is what your are looking for:

    http://stackoverflow.com/questions/614542/use-linq-to-concatenate-multiple-rows-into-single-row-csv-property

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Friday, October 21, 2011 7:55 AM
  • Hi JA,

    The bottom of the link looks good but its written in c++? I need it in C# if possible. The rest of the code isn't for me since my data is from database with millions of rows and can't be in an array. I am thinking a query return a dataset and linq works on the results.

    Friday, October 21, 2011 1:36 PM
  • Hi again,

    All code samples in the link are written un c#. You can do also the same code, but instead of run Linq code against a List or array, you must use your context object in order to run your query against database.

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Friday, October 21, 2011 1:43 PM
  • Hi,

    Can you give me some code plz?

    Friday, October 21, 2011 2:05 PM
  • Hi,

    Assuming the bottom of the link sample, it should looks something like this:

                var query = from order in context.Orders 
                               join product in context.Products on order.ProdID equals product.ProdID
                               group order by order.CustID into orderGroup 
                               select new 
                               { 
                                   CustID = orderGroup.Key, 
                                   ProductNames = 
                                       orderGroup.Aggregate((a, b) =>  
                                           new { CustID = a.CustID, Products = (a.ProdName + ", " + b.ProdName) }).ProdName
                               } 
                                ; 
    
    

    Where context is your context database object.

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Friday, October 21, 2011 2:26 PM
  • Hi JA,

    Thanks for your response.

    I am still not sure about context, is it like a result set returned from a query? I am used to getting result set from stored procedures. The following is my code from data layers:

    public DataSet GetRawData(string auditReportNum)
            {
                string errorMSG = "";
    
                conn = CreateConnection();
    
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
    
                cmd.Parameters.Add("@AuditReportID", SqlDbType.VarChar);
                cmd.Parameters["@AuditReportID"].Value = auditReportNum;
    
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_GetRawData";
                cmd.CommandTimeout = 10000;
    
                SqlDataAdapter myAdaptor = new SqlDataAdapter();
                myAdaptor.SelectCommand = cmd;
    
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
    
                DataSet ds = new DataSet();
    
                try
                {
                    myAdaptor.Fill(ds);
                }
                catch (Exception ex)
                {
                    errorMSG = ex.Message;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
    
                return ds;
            }
    

    Is context.Order and context.Products two different table from a query which came from a stored proc which return 2 table?

     So context is like my ds.Order and ds.Products ?


    • Edited by BlueMarker Friday, October 21, 2011 2:59 PM
    Friday, October 21, 2011 2:58 PM
  • Hi BlueMarker;

    The following code snippet should give you the required results.

    //  Create the Data Context
    var ctx = new DataClasses1DataContext( );
    
    var results = from order in ctx.T_Orders
                  join prod in ctx.T_Products on order.ProdID equals prod.ProdID
                  group prod by order.CustID into custGroup
                  select new {
                        CustID = custGroup.Key,
                        Products = custGroup.Select(n => n.ProdName)
                  };
                  
    foreach (var cust in results)
    {
        string products = cust.Products.Aggregate( "", (buildStr, nextName) => buildStr + ", " + nextName).Substring(1);
        Console.WriteLine( cust.CustID + "\t" + products);
    }
    
    // Output of above code:
    1   Ivory, Irish Spring, Nutrisse Blond, Nutrisse Brown
    2   Irish Spring, Nutrisse Black
    3   Coast, Coast, Nutrisse Blond, Head & Shoulder Wet, GoldCoast
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, October 21, 2011 3:03 PM
  • Well,

    I assumed you were using Entity Framework. If you are using ADO.NET and a store procedure you shuold perform this inside this (or another) store procedure.

    If you want to use LINQ in this scenario you should use Linq to Dataset and perform this query in memory.

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Friday, October 21, 2011 3:05 PM
  • Hi BlueMarker;

    Please see this link. It will explain how to create the DataContext so that you can use Linq to SQL.

    Using LINQ to SQL (Part 1)                  


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, October 21, 2011 3:09 PM
  • Hi JA,

    Can you break down the code so I can try in a stored proc?

    Friday, October 21, 2011 3:16 PM
  • Hi Fernandoo,

    Thanks for your response.

    I will read and give it a try...will take sometime before I get back.

    Friday, October 21, 2011 3:17 PM
  • Hi BlueMarker;

    I converted my code snippet to use Linq to DataSet, using this query does not require the use of a DataContext.

    The below query assumes that ds.Tables[0] is T-Order and ds.Tables[1] is T_Product if this is not the case reverse the Tables[index] around.

    var results = from order in ds.Tables[0].AsEnumerable()
                  join prod in ds.Tables[1].AsEnumerable() on order.Field<int>("ProdID") equals prod.Field<int>("ProdID")
                  group prod by order.Field<int>("CustID") into custGroup
                  select new
                  {
                      CustID = custGroup.Key,
                      Products = custGroup.Select( n => n.Field<string>("ProdName" ))
                  };
    
    foreach( var cust in results )
    {
        string products = cust.Products.Aggregate( "", ( buildStr, nextName ) => buildStr + ", " + nextName ).Substring( 1 );
        Console.WriteLine( cust.CustID + "\t" + products );
    } 
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by BlueMarker Friday, October 21, 2011 8:14 PM
    Friday, October 21, 2011 4:27 PM
  • Thanks Fernandoo,

    I read of linq to dataset and/or used it before. Thanks for the convert. I will use/try both as I need to get my hands into linq more. I've seen a portion of the vid thus far and already dragged all the tables into the designer. I will keep you updated.

    Friday, October 21, 2011 4:57 PM
  • Hi Fernando,

    I can see your results but partially. If I bind the results to a gridview1, all I get is a custid. I tried the code below to show the rest but failed binding the data to column. Console.writeline is useless to me as it is a webapp. After I get the right results, I need to impliment it to a large scale real functionality.

    // Create new DataTable and DataSource objects.
        DataTable table = new DataTable();
    
    // Declare DataColumn and DataRow variables.
       DataColumn column;
       DataRow row;
       DataView view;
    
    // Create new DataColumn, set DataType, ColumnName and add to DataTable.    
       column = new DataColumn();
       column.DataType = System.Type.GetType("System.String");
       column.ColumnName = "Custid";
       table.Columns.Add(column);
    
    // Create second column.
        column = new DataColumn();
        column.DataType = System.Type.GetType("System.String");
        column.ColumnName = "Products";
        table.Columns.Add(column);
    
       for (int i = 0; i < results.Count(); i++)
        {
              row = table.NewRow();
              row["Custid"] = results.ElementAt(i).CustID;
              row["Products"] = results.ElementAt(i).Products.Aggregate("", (buildStr, nextName) => buildStr + ", " + nextName).Substring(1);
              table.Rows.Add(row);
       }
    
    GridView1.DataSource = results; 
    GridView1.DataBind();

    What am I missing to bind all the results to the gridview? I am getting an error at : results.ElementAt(i).CustID;


    • Edited by BlueMarker Friday, October 21, 2011 7:59 PM
    Friday, October 21, 2011 7:54 PM
  • Thanks Fernandoo,

    I got it now. I need to use this instead, just like you have it:

    foreach (var cust in results)
                {
                    row = table.NewRow();
                    row["Custid"] = cust.CustID;
                    row["Products"] = cust.Products.Aggregate("", (buildStr, nextName) => buildStr + ", " + nextName).Substring(1);
                    table.Rows.Add(row);
                }

    view =

    new DataView(table);

    GridView1.DataSource = view;

    GridView1.DataBind();

    Now on to converting to the real query...lets hope it ran quick enough. I'll keep you updated...

     



    • Edited by BlueMarker Friday, October 21, 2011 8:16 PM
    Friday, October 21, 2011 8:14 PM
  • Hi,

    Anyone know how to convert the below into Linq query?

    SELECT DISTINCT Application_Master.Licensed, Application_Master.Site, 
    COUNT([RIC_MASTER].RIC) AS RIC, Application_Master.BusinessActivity, RIC_MASTER.Application
    FROM Application_Master, RIC_MASTER
    WHERE Application_Master.Application=RIC_MASTER.Application 
    AND RIC_MASTER.AuditReport_ID = Application_Master.AuditReport_ID 
    AND RIC_MASTER.AuditReport_ID = @AuditReportID
    GROUP BY Application_Master.Licensed, Application_Master.Site,
    Application_Master.BusinessActivity, RIC_MASTER.Application
    

     

     

    Friday, October 21, 2011 8:46 PM
  • Hi BlueMarker;

    Please post this last question in a new thread.

    Thanks


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, October 22, 2011 1:34 PM