none
How do we check multiple columns in where condition using data table LINQ statement RRS feed

  • Question

  • I have the data table. below is the screen shot for reference.

    I want to return true, if values "0" (zero) from all the columns (Subject1 to Subject7) in data table and along with one more condition Average should be "0" (zero).

    How do we check multiple columns in single LINQ statement in where condition.

    For example in the above screenshot,

     EM104 ==> satisfied all the conditions. it means all the columns (from "Subject1" to "Subject7") and Average values will be zero. So, it will be return "True"

    How do we check multiple columns in single statement..?

    Tuesday, May 19, 2020 2:33 PM

Answers

  • Any alternative way..It means, instead of giving all the columns, 

    shall we mention from this column to this column. like From "Sub..1" to "Sub..7"..

    Any idea...?

    Sorry for the late reply, I'm attending a Microsoft virtual event.

    using System;
    using System.Data;
    using System.Linq;
    
    namespace ConsoleApp3
    {
        class Program
        {
            static void Main(string[] args)
            {
                var table = new DataTable();
                table.Columns.Add(new DataColumn() {ColumnName = "EmpNo", DataType = typeof(string)});
                table.Columns.Add(new DataColumn() {ColumnName = "Name", DataType = typeof(string)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject1", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject2", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject3", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject4", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject5", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject6", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject7", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Average", DataType = typeof(int)});
    
    
                table.Rows.Add("EM101", "jerald",0,10,80,0,20,10,0,0);
                table.Rows.Add("EM102", "Ruby",10,10,0,40,0,10,0,20);
                table.Rows.Add("EM104", "Vini",0,0,0,0,0,0,0,0);
                table.Rows.Add("EM103", "Zeno",10,10,0,0,40,10,0,0);
                table.Rows.Add("EM111", "Karen", 0, 0, 0, 0, 0, 0, 0, 0);
    
    
                var subjectColumns = table
                    .Columns
                    .Cast<DataColumn>()
                    .Where(x => x.ColumnName
                        .StartsWith("Sub"))
                    .Select(x => $"{x.ColumnName} = 0")
                    .ToArray().JoinWithLastSeparator(" and ");
    
                subjectColumns += " and Average = 0";
    
                DataRow[] results = table.Select(subjectColumns);
    
                if (results.Length >0)
                {
                    foreach (var dataRow in results)
                    {
                        Console.WriteLine(string.Join(",", dataRow.ItemArray));
                    }
                }
    
                Console.ReadLine();
            }
        }
    
        public static class Extensions
        {
            public static string JoinWithLastSeparator(this string[] sender, string pDelimiter) => 
                string.Join(pDelimiter + " ", 
                    sender.Take(sender.Length - 1)) + ((((sender.Length <= 1) ? "" : " and ")) + 
                                                       sender.LastOrDefault());
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, May 19, 2020 8:42 PM
    Moderator
  • Hi Gani tpt,
    >>if i pass the below "Subject1" ==0, i am getting zero count. but, records will be there for "0.00".
    I made a test with Kareninstructor code and change type "Subject1" to double and set value of "Subject1" to 0.00.
    The result is shown below:
    EM104,Vini,0,0,0,0,0,0,0,0
    EM111,Karen,0,0,0,0,0,0,0,0
    Is the result you want as shown below:
    EM104,Vini,0.00,0,0,0,0,0,0,0
    EM111,Karen,0.00,0,0,0,0,0,0,0
    Could you explain in detail?
    Best Regards,
    Daniel Zhang


    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, May 21, 2020 7:50 AM

All replies

  • This will give you a new DataTable with the results

    var table = new DataTable();
    
    // define table, populate
    
    var test = table.AsEnumerable().Select(x => x)
        .Any(item =>
            item.Field<int>("Subject1") == 0 &&
            item.Field<int>("Subject2") == 0 &&
            item.Field<int>("Subject3") == 0 &&
            item.Field<int>("Subject4") == 0 &&
            item.Field<int>("Subject5") == 0 &&
            item.Field<int>("Subject6") == 0 &&
            item.Field<int>("Subject7") == 0 && item.Field<int>("Average") == 0);
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Tuesday, May 19, 2020 2:52 PM
    Moderator
  • Any alternative way..It means, instead of giving all the columns, 

    shall we mention from this column to this column. like From "Sub..1" to "Sub..7"..

    Any idea...?

    Tuesday, May 19, 2020 3:33 PM
  • It is possible to check interval of columns:

    bool result = dataTable.AsEnumerable( ).Any( r => r.ItemArray.Skip( 2 ).Take( 8 ).All( v => Convert.ToInt32( v ) == 0 ) );



    • Edited by Viorel_MVP Tuesday, May 19, 2020 5:56 PM
    Tuesday, May 19, 2020 5:53 PM
  • Any alternative way..It means, instead of giving all the columns, 

    shall we mention from this column to this column. like From "Sub..1" to "Sub..7"..

    Any idea...?

    Sorry for the late reply, I'm attending a Microsoft virtual event.

    using System;
    using System.Data;
    using System.Linq;
    
    namespace ConsoleApp3
    {
        class Program
        {
            static void Main(string[] args)
            {
                var table = new DataTable();
                table.Columns.Add(new DataColumn() {ColumnName = "EmpNo", DataType = typeof(string)});
                table.Columns.Add(new DataColumn() {ColumnName = "Name", DataType = typeof(string)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject1", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject2", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject3", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject4", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject5", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject6", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Subject7", DataType = typeof(int)});
                table.Columns.Add(new DataColumn() {ColumnName = "Average", DataType = typeof(int)});
    
    
                table.Rows.Add("EM101", "jerald",0,10,80,0,20,10,0,0);
                table.Rows.Add("EM102", "Ruby",10,10,0,40,0,10,0,20);
                table.Rows.Add("EM104", "Vini",0,0,0,0,0,0,0,0);
                table.Rows.Add("EM103", "Zeno",10,10,0,0,40,10,0,0);
                table.Rows.Add("EM111", "Karen", 0, 0, 0, 0, 0, 0, 0, 0);
    
    
                var subjectColumns = table
                    .Columns
                    .Cast<DataColumn>()
                    .Where(x => x.ColumnName
                        .StartsWith("Sub"))
                    .Select(x => $"{x.ColumnName} = 0")
                    .ToArray().JoinWithLastSeparator(" and ");
    
                subjectColumns += " and Average = 0";
    
                DataRow[] results = table.Select(subjectColumns);
    
                if (results.Length >0)
                {
                    foreach (var dataRow in results)
                    {
                        Console.WriteLine(string.Join(",", dataRow.ItemArray));
                    }
                }
    
                Console.ReadLine();
            }
        }
    
        public static class Extensions
        {
            public static string JoinWithLastSeparator(this string[] sender, string pDelimiter) => 
                string.Join(pDelimiter + " ", 
                    sender.Take(sender.Length - 1)) + ((((sender.Length <= 1) ? "" : " and ")) + 
                                                       sender.LastOrDefault());
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, May 19, 2020 8:42 PM
    Moderator
  • Thanks for your other reply...

    This is simple solution and understandable..

    My data table column data type (Subject) has "double" data type in MS Access. and its value is "0.00".

    if i pass the below "Subject1" ==0, i am getting zero count. but, records will be there for "0.00"..

     item.Field<double>("Subject1") == 0 &&

    • Edited by Gani tpt Wednesday, May 20, 2020 12:14 PM
    Wednesday, May 20, 2020 11:45 AM
  • Hi Gani tpt,
    >>if i pass the below "Subject1" ==0, i am getting zero count. but, records will be there for "0.00".
    I made a test with Kareninstructor code and change type "Subject1" to double and set value of "Subject1" to 0.00.
    The result is shown below:
    EM104,Vini,0,0,0,0,0,0,0,0
    EM111,Karen,0,0,0,0,0,0,0,0
    Is the result you want as shown below:
    EM104,Vini,0.00,0,0,0,0,0,0,0
    EM111,Karen,0.00,0,0,0,0,0,0,0
    Could you explain in detail?
    Best Regards,
    Daniel Zhang


    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, May 21, 2020 7:50 AM