How do I return only one contact per organization?

Question

I have to return only the primary contact within an account of a person that is a member, but my query is returning all all members within the organization. I have tried reordering it and using stuff like Single() but with no luck. I need a way to put a where clause that says I only want the Primary Contact with an account.

if ((!string.IsNullOrEmpty(organization)) && (!string.IsNullOrEmpty(city)) && state != null)
        {
            var corporatemembers = (from a in crmContext.bpt_membertypeSet
                                    where a.bpt_membertypename == "Member (Individual)" || a.bpt_membertypename == "Courtesy"
                                    || a.bpt_membertypename == "Affiliate" || a.bpt_membertypename == "Member"
                                    select new { a.Id }).ToList();

            foreach (var corporatemember in corporatemembers)
            {
                var directories = (from b in crmContext.AccountSet
                                   join a in crmContext.ContactSet
                                   on b.Id equals a.ParentCustomerId.Id
                                   where a.bpt_MemberTypeId.Id == corporatemember.Id
                                   where a.bpt_memberstatus == (int)bpt_memberstatus.Active
                                   where b.Name.Contains(organization)
                                   where a.Address1_City.Contains(city)
                                   where a.bpt_stateorusterritory.Value == state.Value
                                   select new { b.PrimaryContactId, b.EMailAddress1, a.Address1_City, b.Name, b.WebSiteURL, a.bpt_stateorusterritory }).ToList();

                foreach (var directory in directories.ToList().OrderBy(o => o.Name))
                {
                    var cityState = String.Empty;
                    if (directory.bpt_stateorusterritory != null)
                        cityState = directory.Address1_City + ", " + Utility.GetOptionSetValueLabel(crmContext, new Microsoft.Xrm.Sdk.Entity(Xrm.Contact.EntityLogicalName), "bpt_stateorusterritory", new Microsoft.Xrm.Sdk.OptionSetValue(directory.bpt_stateorusterritory.Value));
                    else
                        cityState = directory.Address1_City;
                    oMemberList.Add(new Members { FullName = directory.PrimaryContactId, FullNameEmail = directory.EMailAddress1, OrganizationName = directory.Name, OrganizationUrl = directory.WebSiteURL, CityState = cityState });
                }

            }
        }

this code returns all if the search categories are all filled. I have 4 clauses for all scenarios. But at the end of the whole thing I have:

oMembers.ToList()

Thanks

Edit: here is sample data but the output is wrong. There should only be one organization and one contact

Will be much easier to help if you show some sample data and expected output — Gilad Green, Aug 29 '16 at 16:12

score 1 · Accepted Answer · answered Aug 29 '16 at 18:40

1

I think you are using the wrong field for the join here. This would return all contacts who are a child of that account - which is probably why you are getting multiple results.

on b.Id equals a.ParentCustomerId.Id

The primary contact field on the account is primarycontactid so I suggest you update your query to reference that attribute instead.

answered Aug 29 '16 at 18:40

James Wood

17,286
4
46
89

1

I got it to work by doing on b.PrimaryContactId.Id equals a.Id thank you for pointing out that I needed primarycontactid – John Raesly Aug 30 '16 at 14:47

How do I return only one contact per organization?

1 Answers1