Search code examples
linq

How to filter a table using linq?

I have data table like below

Metal value
silver 5
silver_ref Y
gold 2
gold_ref N
iron N
iron_ref N

From this data table I want to filter like below way if silver,gold,iron != 'N' and silver_ref,gold_ref,iron_ref = 'Y'. So data table will become

Id value
silver 5

How can I achieve using linq

Solution

  • Your data design is far from ideal, but we can work with what you've got.

    One way to get your desired results would be to self-join the table with itself, matching up the non-"_ref" rows with the "_ref" rows. A where clause could then filter out the results that don't pass the remaining requirements.

    The result would be something like:

    var results =
    from d1 in data
    join d2 in data on (d1.Metal + "_ref") equals d2.Metal
    where d1.value != "N" && d2.value == "Y"
    select d1;

    See this .NET Fiddle.

    The above is not specifically limited to "gold", "silver", and "iron" metals.