SQL conversion to Linq to entities -

the sql request:

declare @search varchar(20) set @search = '%a%'  select distinct top (500)      customer.number,contact.name, address.streetaddress, phone.number,      customer.type                     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                , phone.sequence = 1      customer.isactive = 1      , customer.contactid          in (--primary contact             select  customer.contactid             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                              contact.firstnamecareof @search                 or contact.name @search                 or email.address @search                 or phone.number @search                 or address.streetaddress @search                 or address.city @search                 or address.zipcode @search            union            --secondary contacts           select customer.contactid           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                        contact.firstnamecareof @search             or contact.name @search             or email.address @search             or phone.number @search) order     customer.number 

this i've got far :

from customertable in customers      join contacttable in contacts         on customertable.contactid equals contacttable.id      join addresstable in addresses         on customertable.contactid equals addresstable.contactid      // following may no exists customer dont want join them since customer not in request results because of       //  join phonetable in phones     //      on customertable.contactid equals phonetable.contactid      //           //  join emailtable in emails     //      on customertable.contactid equals emailtable.contactid      // alternate method query email , phone table without effecting results         let emailmatch = emails.where (p => p.contactid == customertable.contactid && p.address.contains("a"))     let phonematch = phones.where (p => p.contactid == customertable.contactid && p.number.contains("a"))      customertable.isactive && ( 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} 

questions

1. is there easy way convert sql request linq entities? after day i'm still stock playing linq request above

2. what real , performant way inner join , left join in sql request?

3. is make sense try convert exact sql request "statement" linq? or better totally different approach in linq? mean performant way in sql not same approach @ in linq?

4. when uncommenting join phones.... not return customer without phone... possible return in in result if there nothing join on phone table customer?

thanks or start with.

your first 3 question answered by: use navigation properties. reason people sql background start out writing linq join statements. has @ least 3 major drawbacks: join statement -

• is verbose

• doesn't reveal multiplicity of association. statement

  from customertable in customers join contacttable in contacts ... 

  doesn't tell me if customer-contact 1-n, n-1, or 1-1.

• is error prone: have enter join columns, , pick wrong ones (i've seen happen).

this changes if use named navigation properties, like

from cust in customers cont in cust.contacts ... 

it short, shows association 1-n, , configure association once , never care join columns more.

so if this, query shaped like

from cust in customers cust.contacts           .any(cont => cont.name.contains("a")                     || cont.firstnamecareof.contains("a")                     || cont.address.streetaddress.contains("a")                     || cont.address.city.contains("a")                     || ...  )    || cust.relationships           .any(rel => rel.targetcontact.name.contains("a")                    || rel.targetcontact.firstnamecareof.contains("a")                    || rel.targetcontact.address.streetaddress.contains("a")                    || rel.targetcontact.address.city.contains("a")                    || ...  ) select cust 

(as see, have guess multiplicity of customer-relationship)

this generate sql query in main predicates consist of exist statement. think better combination of in , distinct, because exist efficient search strategy.

as fourth question: can include matches on phones in query, || cont.phone == null.