LINQ - LINQ Comma Seperated Value - Asked By Chintan Vaghela on 01-Dec-11 07:22 AM

Hello

I have gridview which contains multiple rows each row contains productId, and ProductName.

In LINQ How, to show the product names in like.... product1, product2, product3....

Using only LINQ

Its URGENT

Jitendra Faye replied to Chintan Vaghela on 01-Dec-11 07:23 AM

Generating the records in the Comma Separated Format
--====================================================

DECLARE @Result VARCHAR(MAX)
--==============================================
--To display the field heading in the first row
--==============================================
SET @Result = 'EmpCode,EmpName,Active'
SELECT
@Result = COALESCE(@Result + '|','') + CONVERT(VARCHAR(10),[EmpCode]) +','+[EmpName]+','+CONVERT(VARCHAR(2),[Active])
FROM
[DBO].[MEmployee]

PRINT @Result

See the result as shown below:

Note :
1. COALESCE() returns the first NOTNULL expression among its arguments.
2. "|" is used for row delimiter.

Follow this link-

http://sivasqlbi.blogspot.com/2010/04/converting-records-into-comma-separated.html

Jitendra Faye replied to Chintan Vaghela on 01-Dec-11 07:23 AM

Generating the records in the Comma Separated Format
--====================================================

DECLARE @Result VARCHAR(MAX)
--==============================================
--To display the field heading in the first row
--==============================================
SET @Result = 'EmpCode,EmpName,Active'
SELECT
@Result = COALESCE(@Result + '|','') + CONVERT(VARCHAR(10),[EmpCode]) +','+[EmpName]+','+CONVERT(VARCHAR(2),[Active])
FROM
[DBO].[MEmployee]

PRINT @Result

See the result as shown below:

Note :
1. COALESCE() returns the first NOTNULL expression among its arguments.
2. "|" is used for row delimiter.

Follow this link-

http://sivasqlbi.blogspot.com/2010/04/converting-records-into-comma-separated.html

Chintan Vaghela replied to Jitendra Faye on 01-Dec-11 07:27 AM

Vickey I want solution in only LINQ

Jitendra Faye replied to Chintan Vaghela on 01-Dec-11 07:36 AM

Ok suppose you got result in resultEmp, then use this code-

string empName = string.Empty;

foreach (var item in resultEmp)

{

empName = empName + resultEmp.EmpName +

",";

}

Try this and let me know.

Riley K replied to Chintan Vaghela on 01-Dec-11 08:01 AM

You can try like this using string.Join

for example

Response.Write(string.Join(",", (from p in persons select p.FirstName).ToArray()));

Try and let me know

Regards

Suchit shah replied to Chintan Vaghela on 01-Dec-11 08:23 AM

See the Below example :

var dCounts =
      (from i in dic
        group i by i.Value into g
        select new { g.Key, count = g.Count(), Items = string.Join(",", g.Select(kvp => kvp.Key)) });

The main thing you can achive by :

Use string.Join(",", {array}), passing in your array of keys.

Suchit shah replied to Chintan Vaghela on 01-Dec-11 08:24 AM

string returnVal = (from a in b
select a.StringValue).Aggregate(new StringBuilder(),
(c, d) => c.Append(",").Append(@"""" + d + @""""),
(c) => c.ToString().TrimStart(new char[] { ',' })
.TrimEnd(new char[] { ',' }))

Here "b" is the list and StringValue will be the value which want to get converted to comma separated values.

Chintan Vaghela replied to Jitendra Faye on 02-Dec-11 01:06 AM

I hv check your solution but I Cant getting records

My situation as follows

OrdersDetail : CustomerName, ProductID

ProductDetail: ProductID, ProductName

I want to bind grid as follow

CustomerName ProductName

ABC Cha,Coffe, Milk

XYZ Cha, Milk

PQR Milk

Using LINQ

Chintan Vaghela replied to Riley K on 02-Dec-11 01:07 AM

I hv check your solution but I Cant getting records

My situation as follows

OrdersDetail : CustomerName, ProductID

ProductDetail: ProductID, ProductName

I want to bind grid as follow

CustomerName ProductName

ABC Cha,Coffe, Milk

XYZ Cha, Milk

PQR Milk

Using LINQ

Chintan Vaghela replied to Suchit shah on 02-Dec-11 01:07 AM

I hv check your solution but I Cant getting records

My situation as follows

OrdersDetail : CustomerName, ProductID

ProductDetail: ProductID, ProductName

I want to bind grid as follow

CustomerName ProductName

ABC Cha,Coffe, Milk

XYZ Cha, Milk

PQR Milk

Using LINQ

Riley K replied to Chintan Vaghela on 02-Dec-11 05:30 AM

hi RB,

I have solved it like this ,

Here are the detail steps how i solved

I think you are using NorthWind database, first I have queried the results to get ContactName and his products ordered using below query

NorthwindEntities ctx = new NorthwindEntities();

List<MyClass> result = (from o in ctx.Order_Details

join p in ctx.Products on

o.ProductID equals p.ProductID

join oo in ctx.Orders on

o.OrderID equals oo.OrderID

join c in ctx.Customers on

oo.CustomerID equals c.CustomerID

select new MyClass {CustName=c.ContactName, ProdName =p.ProductName }).ToList();

I have created another class to store the results

public class MyClass

{

public string CustName { get; set; }

public string ProdName { get; set; }

}

Initialize the class

List<MyClass> finalResult = new List<MyClass>();

Now iterate throught the results fetched and compare ,

foreach (var s in result)

{

MyClass mc = null;

if (finalResult.Count > 0)

{

mc = finalResult.Where(i => i.CustName == s.CustName).FirstOrDefault();

if (mc != null)

{

mc.ProdName += "," + s.ProdName;

}

else

{

mc = s;

}

else

{

mc = s;

}

finalResult.Add(mc);

}

GridView1.DataSource = finalResult;

GridView1.DataBind();

This is working fine for me,

Try and let me know

Regards

Riley K replied to Chintan Vaghela on 05-Dec-11 04:36 AM

Hey RB here is the more simplified way , just replace the if else statements with the below lambda expression,

Use GroupBy its very easy to do with minimized steps

var result = mc.GroupBy(m => m.CustId).Select(gr => new MyClass

{

CustName = gr.FirstOrDefault().CustName,

ProdName =

string.Join(",", gr.Select(g => g.ProdName).ToArray())

});

GridView1.DataSource = result;

GridView1.DataBind();