title: SQL conversion to Linq to entities
body: <p>The SQL request:</p>
  
  <pre><code>DECLARE @search varchar(20)
  set @search = '%a%'
  
  Select distinct top (500) 
      Customer.Number,Contact.name, Address.StreetAddress, Phone.Number, 
      Customer.Type               
  from 
      Customer
  left join 
      dbo.Address on dbo.Customer.ContactId  = dbo.Address.ContactId
  left join 
      dbo.Contact on dbo.Customer.ContactId  = dbo.Contact.Id
  left join 
      dbo.Phone on dbo.Customer.ContactId  = dbo.Phone.ContactId 
                and Phone.Sequence = 1
  where 
      Customer.IsActive = 1 
      and Customer.ContactId 
          in (--Primary Contact
              SELECT  Customer.ContactId
              FROM dbo.Customer
              INNER JOIN dbo.Contact ON dbo.Contact.id = dbo.Customer.ContactId
              LEFT JOIN dbo.Email ON dbo.Customer.ContactId  = dbo.Email.ContactId
              LEFT JOIN dbo.Phone ON dbo.Customer.ContactId  = dbo.Phone.ContactId
              LEFT JOIN dbo.Address ON dbo.Customer.ContactId  = dbo.Address.ContactId
              WHERE 
                  Contact.FirstNameCareOf LIKE @search
                  OR Contact.Name LIKE @search
                  OR Email.Address LIKE @search
                  OR Phone.Number LIKE @search
                  OR Address.StreetAddress LIKE @search
                  OR Address.City LIKE @search
                  OR Address.ZipCode LIKE @search
  
            union
  
            --Secondary Contacts
            SELECT Customer.ContactId
            FROM dbo.Customer
            INNER JOIN dbo.Relationship ON dbo.Contact.Id = dbo.Relationship.TargetContactId
            INNER JOIN dbo.Contact on dbo.Contact.id = dbo.Relationship.SourceContactId
            LEFT JOIN dbo.Email ON dbo.Contact.Id = dbo.Email.ContactId
            LEFT JOIN dbo.Phone ON dbo.Contact.Id = dbo.Phone.ContactId
            WHERE 
              Contact.FirstNameCareOf LIKE @search
              OR Contact.Name LIKE @search
              OR Email.Address LIKE @search
              OR Phone.Number LIKE @search)
  order by
      Customer.Number
  </code></pre>
  
  <p>This is what I've got so far : </p>
  
  <pre><code>from customerTable in Customers
  
      join contactTable in Contacts
          on customerTable.ContactId equals contactTable.Id
  
      join addressTable in Addresses
          on customerTable.ContactId equals addressTable.ContactId
  
      // the following may no exists for the customer so we dont want to join them since the customer will not be in the request results because of this  
      //  join phoneTable in Phones
      //      on customerTable.ContactId equals phoneTable.ContactId 
      //      
      //  join emailTable in Emails
      //      on customerTable.ContactId equals emailTable.ContactId
  
      // alternate method to query email and phone table without effecting the results    
      let emailMatch = Emails.Where (p =&gt; p.ContactId == customerTable.ContactId &amp;&amp; p.Address.Contains("a"))
      let phoneMatch = Phones.Where (p =&gt; p.ContactId == customerTable.ContactId &amp;&amp; p.Number.Contains("a"))
  
      where customerTable.IsActive &amp;&amp; ( contactTable.Name.Contains("a") || contactTable.FirstNameCareOf.Contains("a") ||addressTable.StreetAddress.Contains("a") || addressTable.City.Contains("a") ||    
                                            addressTable.ZipCode.Contains("a") || emailMatch.Any()|| phoneMatch.Any() )
   orderby customerTable.Number 
              select new {CustomerNumber = customerTable.Number, contactTable.Name, addressTable.StreetAddress, customerTable.Type.EnumId}
  </code></pre>
  
  <p>Questions </p>
  
  <ol>
  <li><p>Is there an easy way to convert my SQL request to linq to entities? After a day I'm still stock playing with the linq request above</p></li>
  <li><p>What is the real and most performant way to do an inner join and a left join like in my SQL request?</p></li>
  <li><p>Is it make sense to try to convert the EXACT SQL request "statement" to linq? Or is it better to do it with a totally different approach in linq? I mean the most performant way to do this in SQL is not necessarily the same approach at all in linq?</p></li>
  <li><p>When uncommenting the join to the phones.... it will not return any customer without phone... is it possible to return in in the result even if there is nothing to join on the phone table for that customer?</p></li>
  </ol>
  
  <p>Thanks for any help or anything to start with.</p>