Sometimes I wonder how I ever got by in C# without the LINQ query syntax. Even though only a small part of this code is actually database-related, the composable nature of LINQ and the range variables make it so much cleaner than the equivalent code using primitive looping constructs. http://josheinstein.com/blog/2014/03/nothing-exciting-really-…

NormalizeContactInfo.cs

void Main()
{

    // Query the OpsLog database for MACD records that have contact
    // information in the record. A customer must also be associated
    // with the record.
    // No need to bother sorting here. That will happen in-memory in
    // the next step.
    
    var query = (
        from wi in WorkItems
        join c in Customers on wi.CustomerID_String equals c.CustomerID 
        where wi.WorkItemTypeName == "MACDs"
        where wi.CustomerID != null
        where wi.ContactName != null && wi.ContactName.Length > 0
        where wi.ContactPhone != null && wi.ContactPhone.Length > 0
        select new {
            c.GroupID,
            c.ID,
            c.Name,
            wi.ContactName,
            wi.ContactPhone,
        }
    );
    
    // Normalize the data by parsing the name and phone numbers to clean
    // them up and standardize the form as much as we can.
    // We also exclude contacts that have 555-555 numbers which are
    // assumed to be employees.
    // Remove duplicates, after normalization. This will only catch
    // contacts that have exactly the same normalized name and phone #.
    
    var results = (
        from x in query.AsEnumerable()
        let contactName = NormalizeName(x.ContactName)
        let contactPhone = NormalizePhone(x.ContactPhone)
        where !contactPhone.StartsWith("555555") && !contactPhone.StartsWith("555-555")
        orderby x.GroupID, x.ID, x.Name, contactName, contactPhone
        select new {
            x.GroupID,
            x.ID,
            x.Name,
            ContactName = contactName,
            ContactPhone = contactPhone
        }
    )
    .Distinct();
    
    // "Pivot" the data so that duplicate contact names with different
    // phone numbers result in a single contact with up to 3 phone
    // numbers per record.
    // First group the flat list into "buckets" by customer/contact name.
    // Then for each contact "bucket", take the first 3 phone numbers.
    // For contacts with less than 3 phone numbers, use null.
    
    var contactList = (
        from contact in results.ToLookup( x => new { x.GroupID, x.ID, x.Name, x.ContactName } )
        let phone1 = contact.Select( x=> x.ContactPhone ).ElementAtOrDefault(0)
        let phone2 = contact.Select( x=> x.ContactPhone ).ElementAtOrDefault(1)
        let phone3 = contact.Select( x=> x.ContactPhone ).ElementAtOrDefault(2)
        select new {
            Group = contact.Key.GroupID,
            Account = contact.Key.ID,
            Customer = contact.Key.Name,
            Contact = contact.Key.ContactName,
            Phone1 = phone1,
            Phone2 = phone2,
            Phone3 = phone3
        }
    );
    
    contactList.Dump();

}

// Given a messy phone number string, attempts to parse it using the
// Einstein.Telecom.TelephoneNumber class and returns the cleaned 
// phone number in the form of 555-555-5555 x5555.
// If the phone number is not in a recognizable format, the original
// string is returned unmodified.
private static string NormalizePhone(string str) {
    
    str = str.Trim();
    
    TelephoneNumber tn;
    if (TelephoneNumber.TryParse(str, out tn) && tn.CountryCode == "1") {
        return tn.ToString("LD");
    }
    else {
        return str;
    }

}

// Given a messy contact name (ie. sometimes they have comments
// added after a dash, or in parenthases) attempts to parse out a
// first, optional middle, and last name from the string and
// puts them back together with extra spaces removed. If the
// contact name cannot be parsed, the original string is returned
// unmodified.
private static string NormalizeName(string str) {

    str = str.Trim().ToUpper();

    var match = Regex.Match(str, @"^([A-Z\.]+)(\s+[A-Z\.]+)?(\s+[A-Z\.]+)");
    if (match.Success) {
        if (match.Groups[2].Success) {
            return
                match.Groups[1].Value.Trim() + " " +
                match.Groups[2].Value.Trim() + " " +
                match.Groups[3].Value.Trim();
        }
        else {
            return
                match.Groups[1].Value.Trim() + " " +
                match.Groups[3].Value.Trim();
        }
    }
    else {
        return str;
    }

}