Unable to pass multiple values in the where clause of a LINQ query

Question

I have a LINQ query something like below:

var results=from p in inputTable.AsEnumerable()
          where inputParameter.Contains(p.Field<String>(inputField))&&
                p.Field<string>(CurrencyCode)==currencyCode
          group p by new
          {
           p[CurrencyCode],
          } into groupedTable
          select new
          {
           Amount = groupedTable.Sum(r => r.Field<System.Decimal>(amountField))
          };
if (results.Count() > 0)
            {
                retVal = results.ElementAt(0).Amount;
            }

My inputParameter is basically a List<string> which will have values like {October, November, December}.

The inputField is November.

My thought is that since the where condition has a Contains method it will just filter rows by November, since inputField is November.

I basically need to pass all the elements of the list, i.e. October, November and December and then get records filtered by these months.

I tried to use where-in stuff of LINQ but was not successful.

Experts please help to crack this question. Any help/pointer will be highly appreciable.

EDIT:

Let me try to make this question very simple.

My List<string> inputParameter can contain variable strings, like {October, November, December} or {January, February, March, April} and so on.

I need my query to pass in all these values and filter the records accordingly.

The simplified query which I tried is follows:

var results=from p in inputTable.AsEnumerable()
            where p.Field<string>(FiscalMonth)==inputParameter[0] ||
                  p.Field<string>(FiscalMonth)==inputParameter[1] ||
                  p.Field<string>(FiscalMonth)==inputParameter[2]
            select new
                {
                 p.Amount
                 };

In the above instance I have basically hardcoded the individual elements of the List inputParameter, but my list will be variable at times. i.e it may hold 3 items, 4 items, or even 12 items.

How to mould the above query to avoid individual hard-coding?

Regards

Anurag

Answer 1

Responding to your edit, it should be :

var results = from p in inputTable.AsEnumerable()
              where inputParameter.Contains(p.Field<string>(FiscalMonth))
              select new
                {
                    p.Amount
                };

This is a working console example that demonstrate query with Contains :

//create datatable with column FiscalMonth
var table = new DataTable();
table.Columns.Add("FiscalMonth");

//add two rows, January and October
var row1 = table.NewRow();
row1["FiscalMonth"] = "January";
var row2 = table.NewRow();
row2["FiscalMonth"] = "October";
table.Rows.Add(row1);
table.Rows.Add(row2);

//query from data table where FiscalMonth in (October, November, December)
var inputParameter = new List<string> {"October", "November", "December"};
var result = from r in table.AsEnumerable()
             where inputParameter.Contains(r.Field<string>("FiscalMonth"))
             select r.Field<string>("FiscalMonth");

//the result will be only one row, which is October. January is successfully filtered out
foreach (var r in result)
{
    Console.WriteLine(r);
}

Answer 2

Do not really understand exactly what you are looking for, since you are stating that you want to query on only Month and your code is querying on month and currency code. but looks like you are returning the sum of the Amount. So he is a first stab at what you want to do.

class MonthCurrency
    {
        public string Month { get; set; }
        public int CurrencyCode { get; set; }
        public decimal Amount { get; set; }
    }
    static List<MonthCurrency> inputTable  = null;

    static void Main(string[] args){
        inputTable = new List<MonthCurrency>()
        {     new MonthCurrency() { Month = "October", CurrencyCode= 1, Amount = 1},
              new MonthCurrency() { Month = "October", CurrencyCode= 1, Amount = 2},
              new MonthCurrency() { Month = "November", CurrencyCode= 2, Amount = 1},
              new MonthCurrency() { Month = "November", CurrencyCode= 2, Amount = 2},
              new MonthCurrency() { Month = "December", CurrencyCode= 3, Amount = 1},
              new MonthCurrency() { Month = "December", CurrencyCode= 3, Amount = 2},
        };   
        var result = GetCurrencyCode("November");
    }

    static public decimal GetCurrencyCode(string inputParameter)
    {
        decimal retVal = 0.0M;
        var results = from p in inputTable.AsEnumerable()
                      where p.Month == inputParameter
                      group p by new
                      {
                          p.CurrencyCode,
                      } into groupedTable
                      select new MonthCurrency
                      {                              
                          Amount = groupedTable.Sum(r => r.Amount)
                      };
        if (results.Count() > 0)
        {
            retVal = results.ElementAt(0).Amount;
        }
        return retVal;
    }

Hopefully this will help you out