问将SQL转换为Linq到实体

EN

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

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 => p.ContactId == customerTable.ContactId && p.Address.Contains("a"))
    let phoneMatch = Phones.Where (p => p.ContactId == customerTable.ContactId && p.Number.Contains("a"))

    where 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}