Not strong in the link I have a query that selects contacts from the three tables.

He looks like this

SELECT * FROM persons p LEFT JOIN phones ph ON p.id = ph.person_id LEFT JOIN emails e ON p.id = e.person_id

I want to rewrite it on Linq and I can not. Can someone help?

Closed due to the fact that the issue is too general for participants aleksandr barakin , Aries , xaja , Saidolim , Sergey Rufanov on Oct 4 '15 at 16:20 .

Please correct the question so that it describes the specific problem with sufficient detail to determine the appropriate answer. Do not ask a few questions at once. See “How to ask a good question?” For clarification. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • 3
    what kind of linq exactly? EF or Linq 2 SQL? - PashaPash ♦
  • four
    And add a data model. Because It is logical to assume that the Person entity has Emails and Phones properties. In this case, Linq is not needed. - Vlad

4 answers 4

Suppose data models look like this:

 public class Person { public int Id {get;set;} public string FirstName {get;set} } public class Phone { public int Id {get;set;} public int PersonId {get;set;} public string Phone1 {get;set;} public string Phone2 {get;set;} } public class Email { public int Id {get;set;} public int PersonId {get;set;} public string Email1 {get;set;} }

then the query might look something like this

 var person = from p in persons join ph in phones on ph.PersonId equals p.Id into phonesPerson from personWithPhone in phonesPerson.DefaultIfEmpty() join e in emails on e.PersonId equals p.Id into emailsPerson from personWithEmail in emailsPerson.DefaultIfEmpty() select new { PersonId = p.Id, Phone1 = personWithPhone == null ? string.Empty: personWithPhone.Phone1, Phone2 = personWithPhone == null ? string.Empty: personWithPhone.Phone2, Email = personWithEmail == null ? string.Empty: personWithEmail.Email1 }

if EF is used and the navigation fields are present, then they could be used to obtain the necessary additional information

  • You still need to add a check that personWithPhone and personWithEmail not null , otherwise your code may NullReferenceException . - ApInvent
  • one
    @ApInvent added verification. in C # 6 it would have looked less cumbersome - Bald

Suppose a variable with an EF context is called db. Then

 from p in db.person join ph in db.phones on p.id equals ph.person_id into ljoin1 from phone in ljoin1.DefaultIfEmpty() join e in db.emails on p.id equals e.person_id into ljoin2 from mail in ljoin2.DefaultIfEmpty() select new { person = p, mail = mail, phone = phone }

Accordingly, in the select, you can vary the resulting values. for example

 select new { person_id = p.id, person_name = p.name, mail = mail?.details, phone_code = phone?.code }
  • mail? .details, if I'm not mistaken, this is C # 6 - Bald
  • @ Bald56rus yes, you are right. very nice feature by the way. - nikita
  • indicate in your answer this small nuance, and then you never know. I for example still on C # 5 - Bald
  • @ Bald56rus is a LINQ, it’s likely to be and phone_code = phone.code normally with phone == null . After all, this is not an appeal to the property directly, but simply an instruction - to take the value from the corresponding column. - PashaPash ♦
  • @PashaPash phone_code = phone.code will raise an exception. because DefaultIfEmpty() returns null - nikita
 public class Person { public int Id {get;set;} public IEnumerable<Phone> Phones {get;set}; public IEnumerable<Email> Emails {get;set}; } public class Phone { public int Id {get;set;} public virtual Person Person {get;set;} } public class Email { public int Id {get;set;} public virtual Person Person {get;set;} }

and you can simply refer from the code to phones and emails as

 var person = context.Persons.Single(....); int emailsCount = person.Emails.Count(); Phone firstPhone = person.Phones.First();
  • Do you trust the creation of EF links (public IEnumerable <Phone> Phones {get; set;}) ?! I understood correctly? - Bald
  • @ Bald56rus yes, but I wrote code at random. could miss something. - PashaPash ♦
  • With such an implementation of the models, it would be possible to use automapper, although it would be necessary to create another class - Bald
  • @ Bald56rus still have the option without LazyLoad, with Include. In fact, TopiCaster is likely Database First, and there are navigation fields with lazy load. - PashaPash ♦

Well, if you just rewrite, then so:

 var linq = from p in persons join ph in phones on p.id equals ph.person_id join e in emails on p.id equals e.person_id select new { Person = p, Phones = ph, Email = e };

Only with selectable fields, decide without using *

  • it was necessary like the left connection - Bald
  • @ Bald56rus will connect in an incomprehensible way, so I just gave a generalized view of what the query might look like. Then the matter of technology, but in general you have already painted everything here without me) - Alex Krass
  • @ Bald56rus thanks, blunted ... just now I understood what you mean) - Alex Krass