Entity Framework- Doing a join when there won't always be a match

发布于 2020-11-30 21:17:29

I am building a query in entity framework, im trying do do a right join, but it on the child object.

I have a table of of calls, that logs all phone calls

Call:    -Id-      -PhoneNumber-    -CallTime-  
          1         101             10
          2         102             20
          3         102             10

There is a PhoneNumbers table that has phone numbers of known numbers

   PhoneNumber:    -Id-      -PhoneNumber-    -PersonId-  
                    1         102             10
                    2         103             11
                    3         104             12

And finally there is a person table that has there name, the reason for this structure is a person could have many phone numbers.

   Person:         -Id-      -Name-       
                    10         John         
                    11         Paul             
                    12         George

What I want is a list of name off all the calls with the persons name if known instead of there number so

Call:    -Id-      -PhoneNumber-    -CallTime-  
          1         101               10
          2         John              20
          3         John              10

I have tried the following, but there setting of the phone number did not work, I know its the join, but cant seem to work it out.

 var query = from call in Call    
                join phoneNumber in PhoneNumber on call.PhoneNumber              equals phoneNumber.PhoneNumber  
                join person      in Person  on phoneNumber.PersonID       equals person.Id  
                into r_join
                from rEmpty in r_join.DefaultIfEmpty()
                            
 select new {
               call.Id,
               PhoneNumber = rEmpty?.Name ?? call.Number
               call.CallTime
 }

Questioner

Ashley Kilgour

Viewed

Original

var result = dbContext.PhoneCalls.Select(phoneCall => new { Id = phoneCall.Id, PhoneNumber = phoneCall.PhoneNumber, // from this phone call, get the corresponding item in table PhoneNumbers // from this PhoneNumber get the Person that the foreign key PersonId refers to // from this Person get the name PersonName = dbContext.PhoneNumbers .Where(phoneNumber => phoneNumber.PhoneNumber == phoneCall.PhoneNumber) .Select(phoneNumber => dbContext.Persons .Where(person => person.Id == phoneNumber.PersonId) .Select(person.Name)) .FirstOrDefault(); });

Entity Framework- Doing a join when there won't always be a match

热门帖子

热门github