Linq to SQL: Using int? as a parameter in a query....

Question

So I have a query that looks something like this:

var clams = from clam in Clams
            where clam.ClamTypeID == 143 &&
                  clam.ParentClamID == null
            select clam;

Nothing too crazy, returns the results that I need. But when I have it in a function where I'm passing in the possible null value as a int? I start to run into an issue. Here is what my function looks like:

public IQueryable<Clam> getAllClams(int clamTypeID, int? parentClamID)
{
     return from clam in Clams
            where clam.ClamTypeID == clamTypeID &&
                  clam.ParentClamID == parentClamID
            select clam;
}

This function returns nothing - I checked the params to make sure they were correct and sure enough, parentClamID == null and still, no results. If I change clam.ParentClamID == parentClamID to clam.ParentClamID == null it works so I'm assuming I'm using int? incorrectly...any idea's what I'm doing wrong here?

Answer 1

I've had this problem in the past. The workaround I found to work was this:

public IQueryable<Clam> getAllClams(int clamTypeID, int? parentClamID)
{
     return from clam in Clams
            where clam.ClamTypeID == clamTypeID &&
                  object.Equals(clam.ParentClamID, parentClamID)
            select clam;
}

Answer 2

Try this:

public IQueryable<Clam> getAllClams(int clamTypeID, int? parentClamID)
{
     var result = from clam in Clams where clam.ClamTypeID == clamTypeID select clam;

      if(parentClamID.HasValue) result = result.Where(c => c.ParentClamID == parentClamID);
      else result = result.Where(c => c.ParentClamID == null);

      return from clam in result select clam;
}

Answer 3

public IQueryable<Clam> getAllClams(int clamTypeID, int? parentClamID)
{
     return from clam in Clams
            where clam.ClamTypeID == clamTypeID &&
                  clam.ParentClamID == parentClamID.HasValue ? parentClamID.Value : null 
            select clam;
}