Query common contacts (self many to many relationship) in entity framework
I have this classic scenario where I have a User table and a Contact table containing only UserId and ContactId columns (so it is a self many to many relationshsip). What I would like is a query that gives me a list of userIds with number of common contacts with the specified User. In plain old SQL I have the following query (contacts of user and user itself is filtered out to get facebook like friend suggestions):
SELECT COUNT(c1.ContactId) as CommonContact, c2.UserId
from Contacts as c1
inner join Contacts as c2 on c1.ContactId = c2.ContactId
Where c1.UserId = @Id AND c2.UserId != @Id
AND c2.UserId NOT IN (SELECT ContactId from Contacts Where UserId = @Id)
Group By c2.UserId
ORDER BY CommonContact Desc
开发者_如何学编程
This simple query works great but I can not figure out how to write the same query in LINQ to Entity, because in the Entity Framework model I have User entity that entity have Contact navigation property but the connection table is not there directly....
Thanks a lot for any help...
Didn't have time and try to run it but something like this should work.
public class Test
{
//simulate an IQueryable
private readonly IQueryable<Person> _people = new List<Person>().AsQueryable();
public void FindContactMatchCount(Guid personId)
{
//we'll need the list of id's of the users contacts for comparison, we don't need to resolve this yet though so
//we'll leave it as an IQueryable and not turn it into a collection
IQueryable<Guid> idsOfContacts = _people.Where(x => x.Id == personId).SelectMany(x => x.Contacts.Select(v => v.Id));
//find all the people who have a contact id that matches the selected users list of contact id's
//then project the results, this anonymous projection has two properties, the person and the contact count
var usersWithMatches = _people
.Where(x => idsOfContacts.Contains(x.Id))
.Select(z => new
{
Person = z, //this is the person record from the database, we'll need to extract display information
SharedContactCount = z.Contacts.Count(v => idsOfContacts.Contains(v.Id)) //
}).OrderBy(z => z.SharedContactCount)
.ToList();
}
}
精彩评论