开发者

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();
        }
    }
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜