C# LINQ) How to make DataTable from query which is grouping rows with only 1 occurence. RRS feed

  • Question

  • Data example)

    Id     desc

    1      chocolate

    2      sky

    3      earth

    1      winter

    1      happiness

    3      idea

    I have a DataTable like above and I only want to have a sub-DataTable which only has one occurence like 2 sky.

    What I tried so far is as below.

    var test = from c in dt.AsEnumerable()
                   group c by c.Field<int?>("Id") into grp
                   where grp.Count() == 1
                   select grp;

    but I cannot cast this to DataTable by CopyToDataTable(); (I have to get a type of DataTable..)

    Please understand I'm self-learning programmer and already spent much time to find a solution.

    And I'm having veryvery busy time and have to solve related issue within today.

    You can ,of course, suggest your own kind way.

    * Additionally, I highly appreciate if you also provide a solution to query only rows with more than 1 occurrence.

    Thank you so much !

    • Edited by Kay_Lee Sunday, January 7, 2018 4:26 AM
    Sunday, January 7, 2018 3:55 AM


  • Try this fragment too:

    var test = from r in dt.AsEnumerable()
                where (
                    from c in dt.AsEnumerable()
                    group c by c.Field<int?>( "Id" ) into grp
                    where grp.Count() == 1
                    select grp.Key
                ).Contains( r.Field<int?>( "Id" ) )
                select r;
    DataTable result = test.CopyToDataTable();

    Then change the condition to 'grpo.Count() > 1'.
    • Edited by Viorel_MVP Sunday, January 7, 2018 12:59 PM
    • Marked as answer by Kay_Lee Sunday, January 7, 2018 1:25 PM
    Sunday, January 7, 2018 12:56 PM

All replies