none
Compare data between two columns and update the result in another column on the same table using linq. RRS feed

  • Question

  • hi,

    I have a table like below

    Col1 Col1  ColRes ... ColN

    1       1      

    2       3

    2        2

    And I want to update the ColRes Column based on the COL1 and Col2 value

    Col1 Col1  ColRes ... ColN

    1       1       True

    2       3       False

    2        2      True

    I want to achieve this by linq not other method.

    Please help

    Thursday, September 12, 2019 9:21 AM

All replies

  • No Update but a new resultset with updated data:

    void Main()
    {
    	var data = new List<TestData>();
    	data.Add(new TestData { F1 = 1, F2 = 1, F3 = false });
    	data.Add(new TestData { F1 = 1, F2 = 2, F3 = false });
    	data.Add(new TestData { F1 = 2, F2 = 3, F3 = false });
    	data.Add(new TestData { F1 = 2, F2 = 2, F3 = false });
    
    	var result = data.Select(i => new TestData() {F1 = i.F1, F2=i.F2, F3=(i.F1 == i.F2)});
    }
    
    public class TestData
    {
    	public Int16 F1 { get; set; }
    	public Int16 F2 { get; set; }
    	public Boolean F3 { get; set; }
    }

    Thursday, September 12, 2019 10:08 AM
  • LINQ is query only. You cannot modify data with it. As Christoph mentioned you can generate a new set of data with the stuff you want via LINQ but you are still responsible for getting it back to whatever back end you have using a standard data access library as LINQ cannot do it.

    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, September 12, 2019 1:55 PM
    Moderator
  • Hi Arivazhagan K,

    Thank you for posting here.

    As CoolDadTx said, we could not change the table cell value by using linq. However, you could select the collection that you want to update. Then, Please use DataRowExtensions.SetField Method to do it.

    I make a simple code in windows forms, and you could have a look.

     private void button1_Click(object sender, EventArgs e)
            {
                DataTable dt =(DataTable) dataGridView1.DataSource;
                var collection = dt.AsEnumerable().Select(i => new { result = (i.Field<int>("Col1")) == (i.Field<int>("Col2"))}).ToArray();
                int m = 0;
                foreach (DataRow item in dt.Rows)
                {
                    item.SetField(2, collection[m].result);
                    m++;
                }
                dataGridView1.DataSource = dt;
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                table.Columns.Add("Col1",typeof(int));
                table.Columns.Add("Col2",typeof(int));
                table.Columns.Add("ColRes",typeof(bool));
                table.Rows.Add(1,1,false);
                table.Rows.Add(2,3, true);
                table.Rows.Add(2,2 ,false);
                dataGridView1.DataSource = table;
    
            }

    Result:

    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, September 13, 2019 1:43 AM
    Moderator
  • I have to generate many result columns by comparing data between columns using linq.

    Col1 Col2  ColRes Col3 Col4  ColRes Col5 Col6  ColRes Col7 Col8  ColRes .....

    1       1       True     1       3       False      2      5       False      1       1       True 

    2       3       False    3       3       True       4       3       False       2       3       False

    2        2      True     4        4      True        1        2      False      4        2      False

    Friday, September 13, 2019 12:10 PM
  • I really think you might be approaching this problem wrong. Please provide the actual code you are using rather than examples of table structures. If you're using DataTable then the solution doesn't require LINQ. If you are using custom types then you can solve the problem with calculated properties. LINQ is only needed when they aren't available.

    Michael Taylor http://www.michaeltaylorp3.net

    Friday, September 13, 2019 1:42 PM
    Moderator
  • Actually I have to compare the value between columns and produce the multiple result columns for the datatable in the fastest way. That is why I prepared LINQ apart from using 'for' or 'for each' loop.
    Sunday, September 15, 2019 6:49 AM
  • It is a common misconception that LINQ is faster, it isn't in most cases. In fact LINQ mostly uses the same foreach code you'd write, just you don't have to do it. The recommendation is to avoid LINQ in code that has to be highly performant and deterministic. LINQ is simply a cleaner way to query data. If you are doing something that requires more than querying then LINQ is not the correct answer.

    If you could post some sample code as to how you're loading your data and how you're going to use the produced values that may give us better insight into your solution but most likely LINQ is simply going to be for querying. If you're using a DataTable then computed columns are used to calculate additional values. For business objects you'd use calculated properties instead. The only time LINQ would be beneficial is if you needed to get a value temporarily and in that case it is just for the initial results anyway. You'd be using an anonymous type to actually store the values.

    Does the solution Christoph gave not work for you? If not then why?



    Michael Taylor http://www.michaeltaylorp3.net

    Sunday, September 15, 2019 8:40 PM
    Moderator