int? and int comparison when LEFT OUTER JOIN in Linq issue

Question

I am working on a WinForms project which requires to use Linq-To-Sql. I have been able to create my DataContext using the SqlMetal tool, and make some queries. But right now I have a problem that I havent been able to solve.

I am trying to make a LEFT OUTER JOIN as following:

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 is raised at compile-time in the join word:

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'

I know this error is caused by the 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 perform the LEFT OUTER JOIN without having this error??

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

Hope someone can help me, thanks in advance.

What if you tried this... on new {p.child_ID.Value,...} I'm not sure if that would work. — Randy Minder, Sep 13 '12 at 16:57

score 6 · Accepted Answer · answered Sep 13 '12 at 17:28

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

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 the p.child_ID become null than it will return 0. Hope this will help !!

score 4 · Answer 2 · answered Sep 13 '12 at 18:19

The first problem was that the property names should be the same as said casperOne♦, but the second problem is that you're comparing a nullable-int, p.child_ID, with a non-nullable-int, t.ID. So you could use the null-coalescing operator in this way:

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

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

The query will be:

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
            {
               //...
            };

Thanks. could be shortened as (p.child_ID ?? 0) – Rm558 Apr 21 '17 at 16:12 — Rm558, Apr 21 '17 at 16:12

int? and int comparison when LEFT OUTER JOIN in Linq issue

2 Answers2

Linked

Related