select null entries using linq

Question

I have this linq query that filters using a dropdown select list. The filter works, however, i would like to select all the fields from the table where a city is null. this criteria gives empty results set. which is wrong as there are over 100 entries with no cities.

my model has this:

public class classA
{
   public string city {get; set;}   
   public ClassB somethingfromClassB{get; set;}    
}

public class classB
{
    //get set methods here
}

controller for class A looks like this:

public class classA : controller
{
   public actionresult index()
   {
      //everything here works except this linq gives me an empty result sets:

      var a = db.classA.inlcude(t=>t.somethingfromClassB);
      if(value =="")
      {
          a=db.classA.where(u=>u.city==null).inlcude(t=>t.somethingfromClassB);
      }
      return view(a.tolist())
   }
}

Are you sure your `city` field in `database` is `null` but `empty`? I doubt you may **inadvertently** save the city field as `empty` — King King, Sep 25 '13 at 17:18
city is nvarchar, and users are allowed to leave it blank when they submit the form — NULL, Sep 25 '13 at 17:21
Just check for `null` or an empty string. `.Where(u => u.city == null || u.city == String.Empty)`. — AtinSkrita, Sep 25 '13 at 17:42
none of these work, i was wondering if i can use hasvalue() i don't see that in visual studio function helpers? — NULL, Sep 25 '13 at 19:21
@Menew *"...as there are over 100 entries with no cities."* Would you please post the SQL Select statement you used to get these 100 rows? — Alireza, Sep 25 '13 at 19:28
a good question, how do i see the sql statements after linq intepretates the query? sorry this is so much easier in other languages than linq with c# in mvc — NULL, Sep 25 '13 at 19:36

doorstuck · Answer 1 · 2013-09-26T12:47:43.217

0

Does the following change work:

a=db.classA.where(u=>u.city == null || u.city == "").inlcude(t=>t.somethingfromClassB);

edited Sep 26 '13 at 12:47

answered Sep 25 '13 at 17:24

doorstuck

2,299
1
22
29

@HamletHakobyan I missed he was using EntityFramework. Edited my answer from using String.IsNullOrEmpty to test for null and the empty string. It was not the problem but it will still catch the most obvious mistake in this scenario. – doorstuck Sep 26 '13 at 12:49

score 0 · Answer 2 · edited May 23 '17 at 12:01

0

I believe you want to perform a left outer join?

Please see: Converting a LEFT OUTER JOIN to Entity Framework

and / or

How to: Perform Left Outer Joins (C# Programming Guide) http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx

edited May 23 '17 at 12:01

Community

1
1

answered Sep 25 '13 at 17:36

Jonathan

4,916
2
20
37

score 0 · Answer 3 · answered Sep 25 '13 at 17:43

0

If you perceive an Empty string as NULL, first you should change your mind! Second, you can check both of these two cases like this:

a= db.classA
     .where(u=>String.IsNullOrEmpty(u.city))
     .inlcude(t=>t.somethingfromClassB);

answered Sep 25 '13 at 17:43

Alireza

10,237
6
43
59

1

@HamletHakobyan This Linq query is something every developers even beginners know how to write. Many many of us know how to check a string against null and empty.This is somehow the best and single way. But did you see the comments I posted and the answers the OP gave me? This is the way of solving problems. I followed simple rules to find where the problem is. We are not here to show off ourselves – Alireza Sep 25 '13 at 17:54
`Linq` just query language not a technology. Do you know what technology OP uses? – Hamlet Hakobyan Sep 25 '13 at 17:59
Sorry! How is this related to your previous comment? – Alireza Sep 25 '13 at 18:08
`Linq` is used in many technologies: Linq2Objects Linq2sql ... In each technologies `Linq` translated in different ways. Also, there are `IQueryable` interface on which `Linq` realized in another way. – Hamlet Hakobyan Sep 25 '13 at 18:15
I don't know even one IQueriable provider which translates String.IsNullOrEmpty to something different than expected. Moreover, isn't is obvious that OP's problem is with Linq2Entities/Linq2Sql? – Alireza Sep 25 '13 at 18:18
none of these work, i was wondering if i can use hasvalue() i don't see that in visual studio function helpers? – NULL Sep 25 '13 at 19:21
@Menew *"...as there are over 100 entries with no cities."* Would you please post the SQL Select statement you used to get these 100 rows? – Alireza Sep 25 '13 at 19:28

select null entries using linq

3 Answers3