none
apply SQL to a DataTable RRS feed

  • Question

  • Hi there,

    I have a dataTable like this...

    Col1,    Col2

    HAD    123

    HAD    123

    LEM     124

    LEM     124

    PLA     125

    PLA     125

    HAD    125

    I want to query the datable to select every col1 that has more than 1 distinct col 2. ie HAD has 123 and 125

    If it were a real table the following sql would work..

    SELECT distinct(col1) as col1, count(*) as col2 FROM myTable 
    GROUP BY col1 having col2 > 0

    Im just not sure how to apply this to a dataTable. I cant see how I can use the dataTable.Select("") method with a complex SQL statement.

    Any ideas,

    J


    jppnn



    • Edited by john pp nn Wednesday, January 16, 2019 2:34 PM
    Wednesday, January 16, 2019 2:33 PM

All replies

  • As Petr said, DataTable doesn't support the full SQL syntax because it is database agnostic. If you want to query against data in a DataTable then use LINQ. It still doesn't support all the SQL syntax but it is cleaner to read.

    Note that your SQL query doesn't return what you are asking for. Your SQL returns all the col1 values with a count of the rows. Your distinct on col1 doesn't change anything because you're already grouping by that column. Your having count > 0 doesn't do anything either because there has to be at least one row before it'll show up. You could rewrite your SQL query like this and it (should) generate the same results).

    SELECT col1, count(col2) FROM myTable
    GROUP BY col1

    That's easy to do in LINQ.

    var items = from r in dt.AsEnumerable()
                group r by r.Field<string>("Col1") into g
                select g;

    Your text is asking for col1 values that have more than 1 unique col2 value. That's a little harder.

    var items = from r in dt.AsEnumerable()
                group r by r.Field<string>("Col1") into g
                where g.Select(x => x.Field<int>("Col2")).Distinct().Count() > 1
                select g;
    Distinct, out of the box, doesn't allow a predicate so it requires a little more work. Some people create an extension method that allows a function to be specified instead so you can reduce this code a little.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, January 16, 2019 4:50 PM
    Moderator
  • Hi Devbrat,

    Thank you for posting here.

    Based on your description, you want to use the dataTable.Select method.

    Datatable.select does not support the way of using group and distinct query.

    So I suggest that you could use the linq query to get it, please try the following code.

      DataTable table = dataSet.Tables["Test"];
       var result = table.AsEnumerable().GroupBy(r => r.Field<string>("col1")).Where(r => r.Select(m => m.Field<int>("col2")).Distinct().Count()>1).Select(r=>r);
    

    Hope my advice could be helpful.

    Best regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 17, 2019 7:20 AM
    Moderator
  • Thanks everyone for your help with this.

    Jack, that worked perfectly.

    Can I ask if there is a quick way to determine the count of result var? Currently I have to do a for each loop to get the count...

    int count = 0;
    foreach (var info in result)
    {
          count++;
    }

    I cant seem to find a result.count property ??

    J


    jppnn


    • Edited by john pp nn Thursday, January 17, 2019 11:57 AM
    Thursday, January 17, 2019 11:56 AM
  • Hi john pp nn,

    Thanks for your feedback.

    Please try the following code to find the count of result.

    foreach (var info in result)

                {

                    int count = info.Count();

                }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 18, 2019 1:13 AM
    Moderator