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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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 

Answer 6

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