Filtering datatable based on criteria if criteria has null values using linq ..plz help!!

Question

User483994611 posted

I want to filter a data table based on a crieteria provided some of the criteria values are null..plz help me out...some thing is going wrong

DataTable filtereddt = new DataTable();

var filteredrows = ds.Tables[0].AsEnumerable()
.Where(r => r.Field<string>("Country") != string.Empty).Select(r => r.Field<string>("Country") == "Ind");

filtereddt=filteredrows .CopyToDataTable() is not working

Answer 1

User2103319870 posted

DataTable filtereddt = new DataTable();

var filteredrows = ds.Tables[0].AsEnumerable()
.Where(r => r.Field<string>("Country") != string.Empty).Select(r => r.Field<string>("Country") == "Ind");

filtereddt=filteredrows .CopyToDataTable()

You have a condition in select clause so you will get only True or false as results. Also if you have null  values in table, you can use String.IsNullOrEmpty method like below

DataTable filteredrows = ds.Tables[0].AsEnumerable()
            .Where(r => !(string.IsNullOrEmpty(r.Field<string>("Country"))) && (r.Field<string>("Country") == "Ind")).CopyToDataTable();

Answer 2

User483994611 posted

var filteredrows = ds.Tables[0].AsEnumerable()
.Where(r => !(string.IsNullOrEmpty(r.Field<string>("Country"))) && (r.Field<string>("Country") == _context.Request.QueryString["userCountry"]));

if (filteredrows.Any())
{
filteredVC = filteredrows.ToList().CopyToDataTable();
}

The above linq query is fetching data table with no rows.

Cant understand why it is happening

Answer 3

User2103319870 posted

The above linq query is fetching data table with no rows.

Cant understand why it is happening

Try debugging the code and see what value you have in "_context.Request.QueryString["userCountry"]" at runtime. Also while comparing do a case insensitive search.

var filteredrows = ds.Tables[0].AsEnumerable()
.Where(r => !(string.IsNullOrEmpty(r.Field<string>("Country"))) && (r.Field<string>("Country").ToLower() == _context.Request.QueryString["userCountry"].ToString().ToLower()));

Please find a sample working demo of above code which I posted earlier