I have looked at a lot of articles on this and either the solutions presented did not work for me or perhaps I am just not understanding them clearly enough. Anyway, at risk of being duplicitous I present my problem for your review.
I have a contact database that I want to search ...
Each contact is associated with a single person (ignore the institution relationship for now) Each contact is also associated with multiple email and phone records in other tables (each email and phone are exclusive to the contact record).
I would like to be able to return the contact that satisfies a query against the email records and/or the phone records. So a contact may have multiple email addresses. If my query "hits" on ANY of the emails I want the SINGLE contact returned with ALL the emails (and phones) associated with the contact included in the results.
So I search on the email domain "hollywood". There is one contact with an email domain that equals "hollywood". This contact also has has two other email addresses associated to it. I want to return a SINGLE record containing all these records. (I'm a Morgan Freeman fan so I made up the following data - it's FAKE!)
Of course running this kind of query in SQL Server Management Studio will return you 3 records. One for each email record associated with the contact. However, I am projecting the results into a single object in which the multiple emails (and phones) will be placed so I don't have to deal with multiple objects for the same contact.
The query is constructed in Linq. I added the "group" line thinking that would eliminate the duplicates ...
var query = from rContact in qryContacts
group rContact by rContact.contact_id into grpContact
join rPerson in ctx.people
on grpContact.FirstOrDefault().person_id equals rPerson.person_id
join rEmail in ctx.emails
on grpContact.FirstOrDefault().contact_id equals rEmail.contact_id into emailGroup
from subEmails in emailGroup.DefaultIfEmpty()
select new PersonalContact
{
ID = grpContact.FirstOrDefault().contact_id,
Label = grpContact.FirstOrDefault().label,
Notes = grpContact.FirstOrDefault().notes,
Prefix = rPerson.prefix,
FirstName = rPerson.first_name,
MiddleName = rPerson.middle_name,
LastName = rPerson.last_name,
Suffix = rPerson.suffix,
AKA = rPerson.aka,
DOB = rPerson.dob,
IsFemale = rPerson.is_female,
Emails = emailGroup.Select(e=> new Email
{
ID = e.email_id,
Label = e.label,
Notes = e.notes,
Preferred = e.is_preferred,
Category = e.email_category,
LocalPart = e.email_local_part,
Domain = e.email_domain,
TopLevelDomain = e.email_top_level_domain,
}),
}
This does OK ... it returns the PersonalContact object, populated as expected ...only it returns 3 of them! I thought that grouping on the Contact would eliminate the duplicates but ... nope.
Here's what the result looks like (I only pasted one but I get back three) ...
{
"FirstName": "Morgan",
"LastName": "Freeman",
"DOB": "1937-06-01T00:00:00",
"Prefix": "Mr",
"IsFemale": false,
"ID": 18,
"Label": "Personal Contact",
"Emails": [{
"ID": 12,
"LocalPart": "mfreeman",
"Domain": "hollywood",
"TopLevelDomain": "com",
"Category": "Personal",
"Preferred": true,
"Display": "mfreeman@hollywood.com"
},
{
"ID": 13,
"LocalPart": "morgan.freeman",
"Domain": "studiocity",
"TopLevelDomain": "net",
"Category": "Personal",
"Preferred": false,
"Display": "morgan.freeman@studiocity.net"
},
{
"ID": 14,
"LocalPart": "bob.smith",
"Domain": "gmail",
"TopLevelDomain": "com",
"Category": "Private",
"Preferred": false,
"Display": "bob.smith@gmail.com"
}],
}
The problem only compounds as I add multiple phones to the contact as well.
Any help would be appreciated!
UPDATE
I thought it was worthwhile to respond to response to NetMage's question "What is qryContacts? Where did you filter on an email address?"
I came up with the following pattern, which allows me to define a LINQ query dynamically ... sort of. I create a variable which holds a LIST of expressions. This allows me to add whatever criteria I want at runtime.
var contactFilter = new List<Expression<Func<contact, bool>>>();
contactFilter.Add(e => e.emails.Where(x=>x.email_domain == "gmail").FirstOrDefault() != null);
I then get an IQueryable which represents the entire table.
var qryContacts = ctx.contacts;
Now I add the filter contents to the query.
if (contactFilter != null)
{
foreach (var item in contactFilter)
{
qryContacts = qryContacts.Where(item);
}
}
Then the query is constructed on top of that as shown in the original post. Hope that's helpful. It has some limitations, but It's been a very productive pattern for me, easing the task of writing queries where you don't know exactly what fields will be defined. My understanding of the underpinnings of Linq and Expressions is very limited so I've found this technique much easier to implement than some of the "Builders" out there. What do you think about this technique? Would love to hear some feedback.
