use linq to query a cell from datatable

Question

I want to write a function "getCount" to return a result of int for different usernames from a datatable, as follows:

static void Main(string[] args)
{
    DataTable table = new DataTable();
    table.Columns.Add("username", typeof(string));
    table.Columns.Add("count", typeof(int));
    table.Columns.Add("code", typeof(string));
    table.Rows.Add("Peter", 300, "wer");      
    table.Rows.Add("Peter", 299, "sdf");
    table.Rows.Add("Peter", 298, "34d");
    table.Rows.Add("Peter", 297, "4ed");
    table.Rows.Add("Paul", 200, "1vc");
    table.Rows.Add("Paul", 200, "64f");
    table.Rows.Add("Paul", 200, "45d");
    table.Rows.Add("Paul", 200, "dcd");

    //expectedly 300, i.e. the first row of count column for peter
    int x1 = getCount(table, "Peter");   

    //expectedly 200, i.e. the first row of count column for paul
    int x2 = getCount(table, "Paul");   
}

static int getCount(DataTable dt, string name)
{
    var query = dt.AsEnumerable().Where(p => p.Field<string>("username") == name)
                                 .Select(p => p.Field<string>("count")).Take(1);
    return Convert.ToInt32(query); //error here

}

but error occurs in the Convert statment:

Unable to cast object of type 'd__3a`1[System.String]' to type 'System.IConvertible'.

what is the problem? thx

Answer 1

Instead of Take(1) use FirstOrDefault() if you want to get single item. Currently your query has type of IEnumerable<string>, which cannot be converted to Int32.

Also use Field<int> instead of trying to get string and convert it to integer manually, because your column actually stores integer values:

static int getCount(DataTable dt, string name)
{
    return dt.AsEnumerable()
             .Where(p => p.Field<string>("username") == name)
             .Select(p => p.Field<int>("count"))
             .FirstOrDefault();
}

Actually you will receive an error if you will try to cast integer field value to string, because Field<T> method does not perform type conversions.