Category: LINQ

29 Feb 2016 21:50

Joined:	05/02/2011
Level:	Silver
Points:	102

Points:	5

Fetch distinct record from dataset/table using linq to dataset/datatable

I am fetching record from database and store a result in dataset.

My dataset like this

Sid Table userid par1 par2 par3
274 tbl1 43 0 0 0
232 tbl1 43 1 2 0
232 tbl1 43 1 2 1
232 tbl2 43 1 2 0
232 tbl2 43 1 2 1

I want to show all 6 column but distinct record.Distinct should be on Sid, Table and userid.I want output like this

Sid Table userid par1 par2 par3
274 tbl1 43 0 0 0
232 tbl1 43 1 2 0
232 tbl2 43 1 2 0

Does it possible through linq to dataset/datatable. I am unable to get AsEnumerable method on dataset but getting on datatable.

#764922

29 Feb 2016 22:02

Joined:	12/11/2009
Level:	Gold
Points:	43848

Points:	2

Hi,

You have multiple options in this regard,
Please check below thread and see which one is most suitable for you and use that,

stackoverflow.com/questions/9299223/get-distinct-items-from-datatable-using-linq

Regards,
Asheej T K

#764923

29 Feb 2016 22:08

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	2

Hi

you can try this Query


USE TEST
SELECT * FROM  TEMP1
SELECT sid,tabledet,userid,par1,par2,par3 FROM  TEMP1
GROUP BY sid,tabledet,userid,par1,par2,par3



SELECT * FROM  TEMP1
SELECT DISTINCT sid,tabledet,userid,par1,par2,par3 FROM  TEMP1

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#764925

29 Feb 2016 22:18

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	2

Hi Pinky,

Use FirstOrDefault method, to get the distinct records using LINQ query, use below link this will give you clear picture how to get the distinct records from database.

stackoverflow.com/questions/3542258/get-distinct-records-using-linq-to-entity

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#764930

01 Mar 2016 00:28

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	2

Hi,
Use dt.DefaultView.ToTable(true, Columns);. Here Columns is and string[ ] of desired columns.
Or try:
var distinct = (from DataRow dRow in dt.Rows
select new {col1=dRow["Column1"],col2=dRow["Column2"]}).Distinct();
foreach (var row in distinct)
{
var value1=row.col1.ToString();
var value2=row.col2.ToString();
}
Hope it helps.
Regards,
Shashikant Gurav
shashikantgurav22@gmail.com

#764942

01 Mar 2016 02:38

Joined:	05/02/2011
Level:	Silver
Points:	102

Points:	1

Thanks shashikant,but I want apply distinct on 3 column and show 6 column.It is not exact I want.

#764955

01 Mar 2016 05:56

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	4

Hi pinky,
try this, it will surely work:
var distinct = (from DataRow dRow in dt.Rows
select new { col1 = dRow["Sid"], col2 = dRow["Table1"], col3 = dRow["userid"], col4 = dRow["par1"], col5 = dRow["par2"], col6 = dRow["par3"] }).GroupBy(y => new { y.col1, y.col2, y.col3 }).Select(z => z.FirstOrDefault());
foreach (var row in distinct)
{
var value1 = row.col1.ToString();
var value2 = row.col2.ToString();
var value3 = row.col3.ToString();
var value4 = row.col4.ToString();
var value5 = row.col5.ToString();
var value6 = row.col6.ToString();
}
Regards,
Shashikant Gurav
shashikantgurav22@gmail.com

#764960

01 Mar 2016 18:21

Joined:	28/04/2006
Level:	Gold
Points:	29773

Points:	3

Hai Pinky,
As you have duplicate id's and you want to get only one record for the id.
So you need to first group the records based on the id and then get the first record as below:


return myListRecords.GroupBy(a => a.id)
      .Select(b => b.First());

Hope it will be helpful to you.

Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com

Fetch distinct record from dataset/table using linq to dataset/datatable

About dotnetspider.com

Quick Links

General

Services