LINQ - LINQ Optional Parameter not passing NULL correctly?

Asked By Nick S on 02-Aug-10 09:45 AM

Hi,

I'm using LINQ in my application, and I have an optional int data type in my WCF web service.

So:

public List<Test> GetRecords(int seq1, int? seq2)

{

TestDataContext db = new TestDataContext ();

var myList = (from test in db.test

where test.SEQ_GROUP == seq1 && test.SEQ_STAFF == seq2
select new Test

{

Item = System.Convert.ToString(test.SEQUENCE),

ID = test.ID,

Description = test.DESCRIPTION,

Name = test.Name,

Desc = test.Desc

}

);

Now, if I execute this, the SQL basically queries the database and says SEQ_STAFF = 1

Howvever, if I don't populate seq2 as the optional integer... the SQL code executes as SEQ_STAFF = null

This should be executing SEQ_STAFF IS NULL

Can anyone help here?

THanks,

Nick

Indranil Chatterjee replied to Nick S on 03-Jan-11 06:24 AM

That is a common limitation of LINQ to SQL.
Try this instead:

from test in db.test

where test.SEQ_GROUP == seq1 && ((seq2 == null && test.SEQ_STAFF == null) || test.SEQ_STAFF == seq2)
select new Test

//The point is, when the comparison is explicitly done against null, LINQ to SQL generates a proper IS NULL clause