How to apply filter on an enumerable collection?

Question

I have a Datatable "tblFields" with two columns and it's values are as follows.

FieldId     FieldValue
-------     ----------
0_Name      test0
0_Amount    100
1_Name      test1
1_Amount    100
2_Name      test2
2_Amount    200
3_Name      test3
3_Amount    0.00
4_Name      test4
4_Amount    

The below linq gives me all the rows that does not start with "0_" from the data table. Now I need to apply a filter on this collection to get the only rows where the amount (_Amount) is not equal to 0.00 or empty. Also, it should exclude the _Name row when the _Amount equals 0.00 or empty.

var pairs = from row in tblFields.AsEnumerable()
                        where !row.Field<string>("FieldId").StartsWith("0_")
                        select new
                        {
                            key = GetId(row.Field<string>("FieldId")),
                            value = row.Field<string>("FieldValue")
                        };

I am looking for an output as follows. Is it possible to get this result using linq? Does anyone know any other ways to achieve this? Thanks for any ideas.

1_Name      test1
1_Amount    100
2_Name      test2
2_Amount    200

Answer 1

This should work (Assuming the Field<T> method returns type T and that it works with numeric types):

var pairs = from row in tblFields.AsEnumerable()
            where !row.Field<string>("FieldId").StartsWith("0_")
            let value = row.Field<decimal>("FieldValue")
            where value != 0.00M
            select new
            {
                Id = row.Field<string>("FieldId"),
                Value = value
            }

If the Field<T> method does not work with numeric types, then try this:

var pairs = from row in tblFields.AsEnumerable()
            where !row.Field<string>("FieldId").StartsWith("0_")
            let fieldValueString  = row.Field<string>("FieldValue")
            let value = String.IsNullOrEmpty(fieldValueString)? 0M : Decimal.Parse(fieldValueString)
            where value != 0.00M
            select new
            {
                Id = row.Field<string>("FieldId"),
                Value = value
            }

Here it should remove both the field ID and Value, but has a slightly different (but still simple) format:

var pair= from row in tblFields.AsEnumerable()
          group row by row.Field<string>("FieldId")[0] into idValueGroup
          where idValueGroup.Key != "0"
          select new
          {
              IdLine = idValueGroup.ElementAt(0),
              ValueLine = idValueGruop.ElementAt(1)
          } into linePair
          where linePair.ValueLine.Field<decimal>("FieldValue") != 0.00M
          select new
          {
              NameId = linePair.IdLine.Field<string>("FieldId"),
              Name = linePair.IdLine.Field<string>("FieldValue"),
              ValueId = linePair.ValueLine.Field<string>("FieldId"),
              Value = linePair.ValueLine.Field<decimal>("FieldValue")
          }

Answer 2

You can do this

var pairs = from row in tblFields.AsEnumerable()
                    where !row.Field<string>("FieldId").StartsWith("0_")
                    && row.Field<string>("FieldValue") != "0.00"
                    select new
                    {
                        key = GetId(row.Field<string>("FieldId")),
                        value = row.Field<string>("FieldValue")
                    };

Test Code

public class MyObject
{
    public string FieldId      { get; set; }
    public string FieldValue { get; set; }
}


List<MyObject> list = new List<MyObject> {
    new MyObject { FieldId = "0_Name", FieldValue = "test0"},
    new MyObject { FieldId = "1_Name", FieldValue = "test1" },
    new MyObject { FieldId = "2_Name", FieldValue = "test2" }
};

var pairs = from row in list
            where !row.FieldId.StartsWith("0_")
            && row.FieldValue != "0.00")
            select new
            {
                key = row.FieldId,
                value = row.FieldValue
            };