Linq compare before inserting to database

Question

HI,

I have Ling Table with MyObject.

It has got fields:

Id Number Name

There is a method that adds ne MyObject to Database. In partial class MyOBject Im implementing OnValidate method. I need to check if there is already a row with such number and name.

The problem is where this values are nulls (for this example both can be)

var result = from m in myContext.MyObjects where m.Number == this.Number &&
m.Name == this.Name
select m;

if(result.Count > 0) {
   throw ...
}

if for example this.Name = null and r.Name = null and m.Number == this.Number no rows are selected and I dont know why :/

Any hints on how can I check if row with excactly the same values in fileds except Id (which is PK) are already in database ?

Thanks for help

Answer 1

Linq-To-SQL has a problem when you give it an Equals expression with a nullable parameter that happens to be null - the query it creates is incorrect. You can read all about it here: http://riotingbits.com/2009/int-null-or-why-it-misbehaves-in-linq-to-sql/

You have to treat the cases where this.Number or this.Name are null separately.

IQueryable<MyObject> result = null;

if (this.Name == null && this.Number == null)
    result = from m in myContext.MyObjects
             where m.Name == null && m.Number == null
             select m;
else if (this.Name == null)
    result = from m in myContext.MyObjects
             where m.Name == null && m.Number == this.Number
             select m;
else if (this.Number == null)
    result = from m in myContext.MyObjects
             where m.Name == this.Name && m.Number == null
             select m;
else
    result = from m in myContext.MyObjects
             where m.Name == this.Name && m.Number == this.Number
             select m;

Answer 2

Try this:

var result = from m in myContext.MyObjects where ((m.Number == this.Number) || (m.Number ==null) && (m.Name == this.Name) || (m.Name ==null)) select m;

SQL query that LINQ generates would be:

SELECT Whatewer yourObject contains FROM yourTable as [t0] WHERE (([t0].[Number] = @p0) OR ([t0].[Number] IS NULL)) AND (([t0].[Name] = @p1) OR ([t0].[Name] IS NULL))