You must Sign In to post a response.
  • Category: LINQ

    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

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

    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM

  • #764923

    you can try this Query

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

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

    Name : Dotnet Developer-2015
    Email Id

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

  • #764925
    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.

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

    Blog :

  • #764930
    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.
    Shashikant Gurav

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

  • #764955
    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();
    Shashikant Gurav

  • #764960
    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 =>
    .Select(b => b.First());

    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

Sign In to post your comments