Entity Framework Where Clause Odd Behavior

Question

So I am using EF and trying to make a pretty simple database call. I have made an identical call on other objects, and don't get this behavior. The behavior is that if I chain two clauses in my where statement, I no longer get any results. Here is some example code:

public List<Widgets> FindAll(long? relatedGadgetID)
using (myDBContext db = new myDBContext())
{
    return db.Widgets.Where(w => w.Deleted == false && w.GadgetID == relatedGadgetID).ToList();
    // This does not get any of the objects in the database
}

public List<Widgets> FindAll(long? relatedGadgetID)
using (myDBContext db = new myDBContext())
{
    return db.Widgets.Where(w => w.Deleted == false).Where(w => w.GadgetID == relatedGadgetID).ToList();
    // This does not get any of the objects in the database
}

public List<Widgets> FindAll(long? relatedGadgetID)
using (myDBContext db = new myDBContext())
{
    var x =  db.Widgets.Where(w => w.Deleted == false).ToList();
    return x.Where(w => w.GadgetID == relatedGadgetID).ToList();
    // This DOES!
}

Why?

Edit: Going off of the comments, I tried the workaround, and it still didn't work. Here is what I tried, did I do something wrong?

public List<Widgets> FindAll(long? relatedGadgetID)
using (myDBContext db = new myDBContext())
{
    return db.Widgets.Where(w => w.Deleted == false && (relatedGadgetID == null ? w.GadgetID == null : w.GadgetID == relatedGadgetID)).ToList();
    // Still doesn't work =(
}

Edit2: Tried another fix, still no luck.

public List<Widgets> FindAll(long? relatedGadgetID)
using (myDBContext db = new myDBContext())
{
    return db.Widgets.Where(w => (w.Deleted == false) && (w.GadgetID == relatedGadgetID || (relatedGadgetID == null && w.GadgetID == null))).ToList();
    // Still doesn't work =(
}

Edit3: Tried the Object.Equals, again, nothing.

public List<Widgets> FindAll(long? relatedGadgetID)
using (myDBContext db = new myDBContext())
{
    return db.Widgets.Where(w => w.Deleted == false && w.GadgetID.Equals(relatedGadgetID)).ToList();
    // Still doesn't work =(
}

possible duplicate of [How can i query for null values in entity framework?](http://stackoverflow.com/questions/682429/how-can-i-query-for-null-values-in-entity-framework) — BrokenGlass, Nov 15 '11 at 02:04
problem is that `relatedGadgetID` is nullable - above SO thread details a workaround for EF — BrokenGlass, Nov 15 '11 at 02:05
it's interesting that in the last example, you were actually using Linq to Objects and not Linq to SQL so I'd start looking at the SQL (google SQL Profiler) that is generated by Entity Framework for the answer. — enamrik, Nov 15 '11 at 02:07
I tried the workaround @BrokenGlass but it didn't work. Did I do it incorrectly? — Alec, Nov 15 '11 at 02:29
i would do it like this: `return db.Widgets.Where(w => w.Deleted == false && ((relatedGadgetID == null && w.GadgetID == null) || w.GadgetID == relatedGadgetID)).ToList(); ` — Gary.S, Nov 15 '11 at 02:40
@Gary.S I had just added a second edit with very similar syntax when you posted. Just in case I tried it with exactly the syntax you posted. Still no go. — Alec, Nov 15 '11 at 02:43

score 0 · Answer 1 · answered Nov 15 '11 at 03:00

In my attempts to fix it early on, I accidentally changed my class property to not be nullable, while the table column still was. Once I realized this, the original attempts still had the same result, but the workaround worked. I ended up using that first edit workaround. Thanks Broken Glass!

Entity Framework Where Clause Odd Behavior

1 Answers1