CODEWITHSUNDEEP
c#asp.netdatabaselinq-to-entities
Bob Avallone
Bob Avallone

Reputation: 979

Linq to Entities Joins

I have a question about joins when using Linq to Entities. According to the documentation the use on the join without a qualifier performs like a left outer join. However when I execute the code below, I get a count returned of zero. But if I comment out the three join lines I get a count of 1. That would indicate that the join are acting as inner join. I have two questions. One which is right inner or outer as the default? Second how do I do the other one i.e. inner or outer? The key words on inner and outer do not work. 

 var nprs = (from n in db.FMCSA_NPR
                            join u in db.FMCSA_USER on n.CREATED_BY equals u.ID
                            join t in db.LKUP_NPR_TYPE on n.NPR_TYPE_ID equals t.ID
                            join s in db.LKUP_AUDIT_STATUS on n.NPR_STATUS_ID equals s.ID
                             where  n.ROLE_ID == pRoleId
                             && n.OWNER_ID == pOwnerId
                             && n.NPR_STATUS_ID == pNPRStatusId
                             && n.ACTIVE == pActive

                             select n).ToList();


                if (nprs.Count() == 0)
                    return null;

Upvotes: 2

Views: 1088

Answers (2)

Justin Morgan
Justin Morgan

Reputation: 30700

Joins in LINQ are inner by default. To do an outer join, you have to either do a manual "if-null-then" or write your own custom join operation.

Left-outer example with manual if-null:

var query = from person in people
            join pet in pets on person equals pet.Owner into gj    //inner
            from subpet in gj.DefaultIfEmpty()
            select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };    //left outer

Upvotes: 1

Joel Etherton
Joel Etherton

Reputation: 37543

joins without keys operate like inner joins. I can't find it in the documentation at the moment, but I'll post an edit when I do. However, to perform a left outer join, here is a sample from 101 LINQ Samples: http://msdn.microsoft.com/en-us/vcsharp/ee908647#leftouterjoin

Upvotes: 1

Related Questions