use query linq in search with empty field

Question

i have table with ten field . i need search query in LINQ that does this searching. my field is :

FirstName (string) , LastName (string), FatherName (string), NotationId (int), DebtPrice (int), BranchName (string), DebtId (int), MeliCode (string)

my problem is : when user don't fill field , this field shouldn't use in query search
thanks.

collection is Legal.View_Dossiers

These are only 8 fields. I don't understand - is this a LINQ to SQL query? LINQ to a collection? — Aidan, Nov 22 '11 at 10:41

score 4 · Accepted Answer · answered Nov 22 '11 at 10:40

4

you can use this code :

var query = from d in datacontext.sample where   
                            (TBoxFName.Text=="" || d.FirstName.Contains(TBoxFName.Text.Trim()))
                            &&(TBoxLName.Text == "" || d.LastName.Contains(TBoxLName.Text.Trim()))
                            &&(TBoxFatherName.Text == "" || d.FatherName.Contains(TBoxFatherName.Text.Trim()))
                            && (TBoxPriceDebt.Text == "" || d.DebtPrice.ToString().Contains(TBoxPriceDebt.Text.Trim()))
                            && (CBoxBranch.Text == "" || d.BranchName.Contains(CBoxBranch.Text.Trim()))
                            &&(TBoxDebt.Text == "" || d.DebtId.Contains(TBoxDebt.Text.Trim()))
                            &&(TBoxMeliCode.Text == "" || d.MeliCode.Contains(TBoxMeliCode.Text.Trim()))
                                select d;

answered Nov 22 '11 at 10:40

aya

1,597
4
29
59

Why you do `TBoxFName.Text==""`??? if you run `d.FirstName.Contains(TBoxFName.Text.Trim())` and `d.FirstName` has value this returns true. – Saeed Amiri Nov 22 '11 at 10:49
because if `TBoxFName.Text==""`then *firstname* field is not factor in search query. – aya Nov 22 '11 at 10:53
Your way is not usefull in all if `TBoxFName.Text==""` is empty then sure `d.FirstName.Contains(TBoxFName.Text.Trim())` returns true if d.FirstName is not null, But, if you want to null checking you should do: `d.FirstName != null && d.FirstName.Contains(TBoxFName.Text.Trim())` In fact in your current way if textbox has value and input string is d.FirstName is null then you will get an exception. In fact I'm sure your current way doesn't helps the OP. – Saeed Amiri Nov 22 '11 at 11:04
2

in code when user not fill TBoxFName, TBoxFName.Text is "" and Condition is true. So FirstName no effect in Search result. But if user fill TBoxFName, check d.FirstName.Contains(TBoxFName.Text.Trim()) and FirstName effect in Search result. – Mehrdad Nov 22 '11 at 11:14
with code you can fill foo `textbox` or not fill foo `textbox` , this means you can search on `FirstName` with filling corresponding `textbox`. – aya Nov 22 '11 at 11:20
1

Including 7 conditionals for a simple 1-value search is a good way to get your local friendly DBA to lynch you. Just sayin' – Marc Gravell Nov 22 '11 at 11:33

score 2 · Answer 2 · answered Nov 22 '11 at 10:42

2

I think (it is unclear) you mean something like:

IQueryable<YourType> query = /* some basic query; maybe db.TheTable */

if(!string.IsNullOrEmpty(firstName))
    query = query.Where(row => row.FirstName == firstName);
if(!string.IsNullOrEmpty(lastName))
    query = query.Where(row => row.LastName == lastName);
if(!string.IsNullOrEmpty(fatherName))
    query = query.Where(row => row.FatherName == fatherName);
// etc

var matches = query.Take(50).ToList();

this uses query composition to issue the most appropriate underlying query it can; for example, if this is LINQ-to-SQL and firstName and fatherName are supplied, you would get something like:

select top 50 {some columns} from [dbo].[TheTable] t
where t.FirstName = @p0 and t.FatherName = @p1

where @p0 and @p1 are the parameters holding the values.

answered Nov 22 '11 at 10:42

Marc Gravell

1,026,079
266
2,566
2,900

tanks marc.problem is search query without 'if' – Mehrdad Nov 22 '11 at 10:49
@Mehrdad please rephrase, I can't follow that comment. However, note that the *query* **does not have** any `if` - see the example TSQL code I included – Marc Gravell Nov 22 '11 at 10:51
where are set parameters '@p0' and '@p1' – Mehrdad Nov 22 '11 at 10:59
@Mehrdad they are from the "captured" variable values from `firstName`, `lastName` and `fatherName` which I assume you have available somewhere (perhaps from UI controls). LINQ-to-SQL and EF etc will parameterize those for you. – Marc Gravell Nov 22 '11 at 11:04
hi Marc if @p0 equal `Marc` you find all record that FirstName equal this value and when @p0 equal `""` you find all record that have FirstName equal `""` but when use aya answer if @p0 equal `""` this field not effect in query search. thanks Marc – aya Nov 22 '11 at 11:30
@aya the scenario with `p0` as `""` would never arise, as we are only including that filter when the value is non-trivial (`!string.IsNullOrEmpty(...)`). – Marc Gravell Nov 22 '11 at 11:32

score 0 · Answer 3 · answered Nov 22 '11 at 11:24

you can try to write:

IEnumerable<YourType> query = //data taken from database
var queryWhere = query 
    .Where(x => x.FirstName  == varFirstName   || string.IsNullOrEmpty(x.FirstName ))
    .Where(x => x.LastName == varLastName      || string.IsNullOrEmpty(x.LastName ))
    .Where(x => x.FatherName == varFatherName  || string.IsNullOrEmpty(x.FatherName ))
    //...and so on... 
    .ToList();

use query linq in search with empty field

3 Answers3

Linked