asp.net mvc linq to sql issue with nullable variables

Question

I have an asp.net mvc project where have the search filter form and 8 parameters which arrive from my form and must be null. Everything work fine when I populate all fields in my search form. But when I hold missing fields it returns all rows from the table. I have the next expression:

var model = repository.GetRows()
.Where(x => x.Var1 == Var1 || x.Var2 == Var2 || 
x.Var3 == Var3 || x.Var4 == Var4 || 
x.Var5 == Var5 || x.Var6 == Var6 || 
x.Var7 == Var7 || x.Var8 == Var8).ToList();

Have any trick for checking the null variables? Because I have to write a lot of if/else statement, which I think it's not right.

Because when I use && it's return null statement, etc. none rows — BorHunter
– BorHunter, Commented Sep 19, 2013 at 12:20

Sergey Berezovskiy · Accepted Answer · 2013-09-19 12:36:59Z

1

You can use the null coalescing operator "??"

where(x => x.Var1 == (Var1 ?? x.Var1));

This simply says if Var1 is null then use the string "SomeValue".

edited Sep 19, 2013 at 12:36

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

answered Sep 19, 2013 at 12:21

Steven Wood

2,8053 gold badges31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BorHunter Over a year ago

But I need when my variable is null it mustn't include into my expression. What I should need to do in this scenario?

Steven Wood Over a year ago

where(x => x.Var1 == (Var1 ?? x.Var1)); This will say if Var1 is null then return when x.var1 is equal to itself, making the clause cancel itself out.

Jagz S Over a year ago

where (x => x.Var1 == null || x.Var1 == Var1)

DK Chauhan · Accepted Answer · 2013-09-19 12:52:20Z

0

First of all I suggest you to return IQueryable from repository.GetRows() then you can execute the query like :-

public List<Entity.Student> SearchStudent(string Name, int Age, string EmailAddress, string CountryName)
    {
        IQueryable<Entity.Student> lstStudents = this.GetAllStudent();
        if (!string.IsNullOrEmpty(Name))
        {
            lstStudents = lstStudents.Where(node => node.FirstName.Equals(Name) || node.LastName.Equals(Name));
        }
        if (Age > 0)
        {
            lstStudents = lstStudents.Where(node => node.Age == Age);
        }
        if (!string.IsNullOrEmpty(EmailAddress))
        {
            lstStudents = lstStudents.Where(node => node.Email.Equals(EmailAddress));
        }
        if (!string.IsNullOrEmpty(CountryName))
        {
            lstStudents = lstStudents.Where(node => node.Country .Equals(CountryName));
        }
        return lstStudents.ToList<Entity.Student>();
    }

this way is the recommended by DLinq experts.

answered Sep 19, 2013 at 12:52

DK Chauhan

1941 silver badge11 bronze badges

4 Comments

BorHunter Over a year ago

And when name != null & age != null, does it return only rows by Name?

BorHunter Over a year ago

or to lstStudents by name added lstStudents by Age?

DK Chauhan Over a year ago

if name and age are not null then it will generate the sql query like :- select * from tblStudent where name = 'name' and age =10 then it will throw the result for you

DK Chauhan Over a year ago

can u think, what sql query this code will generate "where(x => x.Var1 == (Var1 ?? x.Var1));" it is adding one more condition to your sql query while there is no need of this condition. why we are adding one more condition in our sql query. it will hit the performance

Collectives™ on Stack Overflow

asp.net mvc linq to sql issue with nullable variables

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related