none
Copy only columns which matches a certain criteria using LINQ

    Question

  • I have a Datatable as below. Now my requirement is I need to extract the columns from the below datatable where value equal to Yes ( in yellow color -2 columns only..or sometimes it may be 3 or 4 columns..because all columns are dynamic) and copy it into another datatable. Can u please help me how to do using LINQ

    Note: I am using Entity framework..All of the above values are stored in an entity called "Batch"

    Important: The value Yes I will be checking based upon a field called "IsinAverage" which can be "Yes" or "No". When the IsinAverage= yes I need to extract only those values.



    Chemical Element

    AlloyCode

    E1H2323

    E2H3434

    E3H23424

    IsinAverage

    34

    Yes

    Yes

    No

    Fe

    W34

    23

    45

    67

    Ch

    4524

    345

    45

    78

    Mn

    234234

     

    8678

    657

    Lu

    34234

    6

    89

    567


    ie: How many columns are there in the DataTable where status="Yes" ...I want to extract.

    Some references I have found but I am not able to fix it..:

    http://www.jphellemons.nl/post/Filter-a-DataTable-with-LINQ-to-Objects.aspx

    If some one can not give me the exact answer for this scenario at least a general answer also will work ...thanks -pep






    • Edited by pepcoder Wednesday, February 29, 2012 7:38 AM
    Wednesday, February 29, 2012 7:13 AM

Answers

  • Hi pepcoder,

    I agree with your idea. Only extract a specific part of the table seems too difficult. If the columns are fixed 'E1H2323', 'E2H3434', 'E2H34334', you can write 3 if statements to judge whether these columns contain 'Yes', if there's a 'Yes' contained in the column, you can write a Linq query to select an anonymous type. About judge whether the column should be extracted, you can use the statement below,

    dt.AsEnumerable().Any(e => e["E2H34334"].ToString().Contains("Yes"))

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Friday, March 02, 2012 6:19 AM
  • Hi pepcoder,

    Based on the issue, I think you can use dynamic linq to concatenate string to build the Linq query. More information about dynamic linq, please refer here. Then you can define a variable to store the dynamic value and use string concatenating to build the linq statement.

    I hope this can help.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Monday, March 05, 2012 6:58 AM

All replies

  • Hi pepcoder,

    Welcome to MSDN Forum.

    Based on the picture, I don't know the datatable schema. You have mentioned 'IsinAverage' is a filed, but why it isn't the column of the table? If the picture present columns as verticle(IsinAverage, Fe, Ch, Mn, Lu are the table's columns), the records seems no sense. The picture is more like a two-dimensional table. Could you please clarify more clearly?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Thursday, March 01, 2012 7:27 AM
  • Hi Allen,

    Thanks for your reply. I have tried to explain the requirement below in a much more simpler way and added the piece of code to create the DataTable as well. Please let me know if you require more information.

    Chemical Element

    AlloyCode

    E1H2323

    E2H3434

    E2H34334

    IsinAverage

     

    Yes

    Yes

    No

    Fe

    4545254

    0.00344

    0.344

    0.567

    Ch

    34545

    2.344

    2.3434

    0.000

    Mn

    345345

    0.00344

    1.234234

    0.3434

    Lu

    56467

    0.000

    4.2345234

    .1111

    Za

    67567

    0.344

    .566546

    0.000

    K

    567567

    0.000

    .3445

    .44344

    A2

    546546

    0.324234

    0.000

    0.000

     

    Yellow color: Columns I need to extract

     

    Green Color row: A separate row dynamically created at run time based upon a status.

     

    Red Color : Headings

     

    Here is the structure of the datatable where many rows and columns are added runtime from many methods and the final DataTable would be sent back to the mainscreen and display.

     

    DataTable IModelFacade.GetBatchResultsforMachines(Batch batch)

            {

              try

                {

                     Bool isActual= true;   

                     DataTable_datatableCompleteSet = CreateDataTableForAllmachines(batch);

                    

                   1: GetAllMachineNames(batch, ref_datatableCompleteSet);

                   2: GetAllComponents(batch, ref_datatableCompleteSet, isActual);

                   3: GetOtherCodesandValues(batch, ref_datatableCompleteSet);

                                     return datatableCompleteSet;

            

                }

                catch(Exceptionex)

                {

                    ExceptionManager.HandleException(ex, ExceptionPolicies.Domain);

                }

                returnnull;

            }

    The methods I have numbered as 1,2,3 we are adding many rows and columns dynamically and making a complete datatable as per the format I have mentioned above. And now my requirement is for the columns (in yellow) where ever IsinAverage is appearing as “Yes” I should be able to extract only those columns and copy into another datatable. This is the actual requirement. I am working on an existing project where I cannot change the structure of the DataTable too.

     

    Regards

    -pep

    Thursday, March 01, 2012 11:47 AM
  • I am thinking of checking as mentioned below because raw wise checking would be more difficult. since I will have values E1H2323,E2H3434,E2H34334 I am thinking of checking whether value "Yes" comes in those columns and extract only those columns..

    I think this would be much more easier to extract than checking raw wise .All suggestions are welcome

    regards

    -pep

    Thursday, March 01, 2012 12:16 PM
  • Hi pepcoder,

    I agree with your idea. Only extract a specific part of the table seems too difficult. If the columns are fixed 'E1H2323', 'E2H3434', 'E2H34334', you can write 3 if statements to judge whether these columns contain 'Yes', if there's a 'Yes' contained in the column, you can write a Linq query to select an anonymous type. About judge whether the column should be extracted, you can use the statement below,

    dt.AsEnumerable().Any(e => e["E2H34334"].ToString().Contains("Yes"))

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Friday, March 02, 2012 6:19 AM
  • Thanks Allen. Your idea is brilliant.But I have an issue here. Columns E1H2323', 'E2H3434', 'E2H34334' are not fixed . Only item which would be common in all the columns would be "E" and based upon the values which comes from the database we append values to E as 

    eg: E and value is 1H2323 then it would be E1H2323 etc.

    ie: in SQL its easy to get the result set I want.

    select * from <table> where elements_name LIKE '%E%' and chemical_value="Yes".

     But in front-end since the columns  E1H2323', 'E2H3434', 'E2H34334 are dynamic and will change based upon the values which comes from DB I can  not specify the column name.Only common value which will be present would be "E" .

    Awaiting for your reply.

    Thanks again for your help.

    Regards

    -pep 


    • Edited by pepcoder Saturday, March 03, 2012 5:08 AM
    Saturday, March 03, 2012 4:52 AM
  • Hi pepcoder,

    Based on the issue, I think you can use dynamic linq to concatenate string to build the Linq query. More information about dynamic linq, please refer here. Then you can define a variable to store the dynamic value and use string concatenating to build the linq statement.

    I hope this can help.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Monday, March 05, 2012 6:58 AM