none
where statement doesn't get != right RRS feed

  • Question

  • Hello,
    I have following query:
    var legalArtikels = (from prod in masterContext.FileProductie
                                             where prod.Blok != "y"
                                             where prod.SoortVerpakking == "pot"
                                             where prod.Labelen == false
                                             select prod);
    

     The result is zero objects, if I run:
    var legalArtikels = (from prod in masterContext.FileProductie
                                             where prod.SoortVerpakking == "pot"
                                             where prod.Labelen == false
                                             select prod);
    

    I get some articles now.
    But! Interesting to know is dat the "Blok" value of all articles is null, so I should get the same result with the first code. Why not?
    It's driving me crazy! I just want to select all articles where "Blok" isn't "y".
    Blok is a nullable char(1)
    Any help verry much appreciated! thanks!

    • Edited by Denny007 Friday, January 27, 2012 1:29 PM
    Friday, January 27, 2012 1:28 PM

Answers

  • Okay. 

    I've heard if you don't declare the property "Blok" as nullable, the null comparision may fail. http://www.brentlamborn.com/post/LINQ-to-SQL-Null-check-in-Where-Clause.aspx

     

    Can you try this?

    var legalArtikels = (from prod in masterContext.FileProductie

                                             where (prod.Blok != "y" || object.Equals(prod.Blok, null))
                                             && prod.SoortVerpakking == "pot"
                                             && prod.Labelen == false
                                             select prod);


    "En los momentos de crisis, sólo la imaginación es más importante que el conocimiento"
    Friday, January 27, 2012 4:17 PM

All replies

  • Hello Denny

    I think you're writing wrong Linq statatemen

    var legalArtikels = (from prod in masterContext.FileProductie
                                             where prod.Blok != "y"
                                             && prod.SoortVerpakking == "pot"
                                             && prod.Labelen == false
                                             select prod);

     

    However, you can try adding

    && prod.Blok != null


    "En los momentos de crisis, sólo la imaginación es más importante que el conocimiento"
    • Proposed as answer by SOW Bano Friday, January 27, 2012 4:18 PM
    Friday, January 27, 2012 2:54 PM
  • I tested your code, and it doesn't works.

    Although! I've got some details. Of some articles I changed the 'Blok' value from null to '' (empty) and guess what? Your code, get's all the articles that I want but without the ones where 'Blok' is still null.

    So, if my DB contains 3 articles, 2 where 'Blok' is empty, or just isn't null or y, and 1 article where 'Blok' is null, then I get only the first 2 articles but I should get all three of them (because imo null != "y").

    So it has something to do with the null value, unfortunatly I can't change it because it's a third party DataBase.

    Ow yes, in my n00bness I have written this in the wrong subforum I guess, because I use the entity framework, so I'm actually using LINQ to entities and not LINQ to SQL I guess, sorry.

    I use following successive code now: (but I still find it weird)

    var legalArtikels = (from prod in masterContext.FileProductie
                                              where (prod.Blok != "y"
                                              || prod.Blok == null)
                                              && prod.SoortVerpakking == "pot"
                                              && prod.Labelen == false
                                              select prod);

    • Edited by Denny007 Friday, January 27, 2012 3:45 PM
    Friday, January 27, 2012 3:39 PM
  • Okay. 

    I've heard if you don't declare the property "Blok" as nullable, the null comparision may fail. http://www.brentlamborn.com/post/LINQ-to-SQL-Null-check-in-Where-Clause.aspx

     

    Can you try this?

    var legalArtikels = (from prod in masterContext.FileProductie

                                             where (prod.Blok != "y" || object.Equals(prod.Blok, null))
                                             && prod.SoortVerpakking == "pot"
                                             && prod.Labelen == false
                                             select prod);


    "En los momentos de crisis, sólo la imaginación es más importante que el conocimiento"
    Friday, January 27, 2012 4:17 PM
  •  

    Hi

    That is not LINQ behaviour but SQL.
    Please read here how NULL is considered usually http://www.w3schools.com/sql/sql_null_values.asp

    As per my understanding and my using to it, the NULL value should not be involved in any result until it explicit been called by IS NULL and you cannot get NULL by using operators (=, <, > , <>) unless you change the setting of SET ANSI_NULLS but that will affect only the field being in WHERE. For example

    let say your table soortverpakking
    blok SoortVerpakking Labelen
    ----- --------------------- ----------
    y     pot1                     0
    null pot2                     1


    SELECT * FROM soortverpakking WHERE (blok <> 'y')
    Result : 0 Row (where there is blok is not 'y' and it is null, but not returned) if you want to get it, you have to ask for it by Is Null

    SELECT * FROM soortverpakking WHERE (blok <> 'y' or blok Is Null)

    IMO? leave the SET ANSI_NULLS as default and use Is Null when you want null value field to be included in the result.

    [edit] or as I did in my last project, I give the field a default value such in your case a empty string '' just for not using Is Null.

     


    Friday, January 27, 2012 4:20 PM