Int? and int comparisons when LEFT OUTER JOIN in Linq release

Question

I am working on a WinForms project that requires the use of Linq-To-Sql . I was able to create my DataContext using the SqlMetal tool and make some queries. But now I have a problem that I could not solve.

I am trying to make the LEFT OUTER JOIN as follows:

 MyDatabase db = new MyDatabase(...); var query = from p in db.ParentTable join t in db.ChildTable on new {A = p.child_ID, B = p.OtherID} equals new {A = t.ID, B = t.OtherID} into j1 from c in j1.DefaultIfEmpty() select new { ... }; 

When I write this query, an error occurs while compiling the word join :

The type of one of the expressions in the join clause is invalid. Type input error in 'GroupJoin' call

I know this error is caused by a comparison between p.child_ID and t.ID , since p.child_ID is int? and t.ID is int . But, how can I solve this? How can I execute a LEFT OUTER JOIN without this error?

p.child_ID int? since this column is marked as IS NULL in SQL .

Hope someone can help me, thanks in advance.

Answer 1

You can use the GetValueOrDefault () method. It retrieves the value of the current Nullable object or the default value of the object.

In terms of your example:

 var query = from p in db.ParentTable join t in db.ChildTable on new {A = p.child_ID.GetValueOrDefault(0), B = p.OtherID} equals new {A = t.ID, B = t.OtherID} into j1 from c in j1.DefaultIfEmpty() select new { ... }; 

If p.child_ID becomes zero, which returns 0. Hope this helps !!

Answer 2

The first problem was that the property names should be the same as casperOne ♦, but the second problem is that you are comparing the value of nullable-int, p.child_ID with non-nullable-int, t.ID , Thus , you can use the operator with zero coalescent as follows:

 (int)(p.child_ID ?? default(int)) 

In this case, returns p.child_ID , if it is not null else, returns default(int) , that is, 0 .

The request will look like this:

 var query = from p in db.ParentTable join t in db.ChildTable on new {A = (int)(p.child_ID ?? default(T)), B = p.OtherID} equals new {A = t.ID, B = t.OtherID} into j1 from c in j1.DefaultIfEmpty() select new { //... };