I can't understand GroupJoin where i will need it in database relationships.

Question

Hello Everybody

I am thinking 5-6 hours to understand something about GroupJoin. I am talking about query with linq for Database tables.I understand what Join and GroupJoin does but I really couldn't understand where i will need it.I feel that Join does all what i will need but if there is something which Join can't without GroupJoin so i feel that situation is meaningless or useless.

For example:

Persons table in relation with Phones and Phones table hold foreign keys of Persons and one person can have one more phone number.If we want to brings all persons who has phone numbers and who hasn't too so we will need GroupJOin but what for we will need this query ?

Can u give me good reason , example or explenation for using GroupJoin ?

Answer 1

From MSDN:

In relational database terms, Join implements an inner join, a type of join in which only those objects that have a match in the other data set are returned. The GroupJoin method has no direct equivalent in relational database terms, but it implements a superset of inner joins and left outer joins.

So, if you've ever had a reason to use an outer join in SQL, you would likely use GroupJoin for similar purposes in Linq.

Answer 2

In SQL, you'd write:

SELECT *
FROM Persons p LEFT JOIN Phones ph on p.PersonID = ph.PersonID

You'd get a row column shape with some rows having null phone numbers, and some rows having the same Person as other rows.

Bob, null
Joe,  111-1111
Mike, 222-2222
Mike, 333-3333
Mike, 444-4444

---

In LinqToSql, you'd write:

from p in Persons
join ph in Phones on p.PersonID equals ph.PersonID into phones
select new {Person = p, Phones = phones.ToList()};

You'd get one person instance per person and one phone instance per phone, all properly related.

Bob, [] <- (empty)
Joe, [111-1111]
Mike, [222-2222, 333-3333, 444-4444]