Linq with Null values in the WHERE Clause

[JanAntwerpen]

Hello people!

I have a table with a lot of columns. I want to be able te let the user search on every column. Afterwards I perform a linq query and this should show all the correct data. Problem is that all empty cells return a null value. Making the Linq query not working.
I have following result:

var q = from table in dataContext.table1
        where table.column1.contains(dgv.currentrow.cells[0].value.tostring()) && table.column2.contains(dgv.currentrow.cells[1].value.tostring()) && ...... table.columnN.contains(dgv.currentrow.cells[N].value.tostring())
        select table;

This doesn't work, but I don't know how to fix it so it does work!

 

[JanAntwerpen]

Possible answer

OK!! GOT IT!

I know this is not the best way to do it, returning all records and then start the filtering, but it is the only way I could come up with. So if anyone knows a better way to handle this, let me know!!!
What I have is this:

var q = from table in dataContext.table1
        select table;
if(!string.IsNullOrEmpty(dgv.currentrow.cells[0].value.tostring())
{
     q = q.Where(table => table.column1.contains(dgv.currentrow.cells[0].value.tostring())
}

if(!string.IsNullOrEmpty(dgv.currentrow.cells[1].value.tostring())
{
     q = q.Where(table => table.column1.contains(dgv.currentrow.cells[1].value.tostring())
}

if(!string.IsNullOrEmpty(dgv.currentrow.cells[N].value.tostring())
{
     q = q.Where(table => table.column1.contains(dgv.currentrow.cells[N].value.tostring())
}

This way, first all data is collected, then filtered by every possible entered field. I'm not sure how performance will be with thousands/millions of rows in that table.

So any suggestion for improvement is welcome!

 

[JanAntwerpen]

I am using a datagridview which is normally bound to a datacontext, using Linq.

var q = from table in dataContext.table1
select table;
As I want to search for data in this table, I unbound the datagridview and have only one row. The user can put in filters. Afterwards, I want to fill the datagridview with the filtered data.

 

[jmcilhinney]

I am using a datagridview which is normally bound to a datacontext, using Linq.

var q = from table in dataContext.table1
select table;
As I want to search for data in this table, I unbound the datagridview and have only one row. The user can put in filters. Afterwards, I want to fill the datagridview with the filtered data.

I actually removed my original post because, after re-reading your posts, I realised that it wasn't the question that I wanted to ask.

I have to say, it sounds very dodgy that the user in inputting filter values in the same place that the data will be displayed. I strongly suggest that you change that so that the filters are entered into TextBoxes or whatever; just not the grid itself.

Regardless of where the filter values come from, here is an example of how you can filter with multiple values using a single query that will effectively ignore empty filters:

string filter1;
string filter2;
string filter3;

// ...

var rows = from row in context.Table
           where (filter1 == string.Empty || row.Column1 == filter1) &&
                 (filter2 == string.Empty || row.Column2 == filter2) &&
                 (filter3 == string.Empty || row.Column3 == filter3)
           select row;

If a filter value is an empty string then the first condition of the corresponding pair matches every row, effectively ignoring that filter.