使用LINQ lambda表达式在导航表中选择公共值 [英] Select common value in navigation table using LINQ lambda expression

问题描述

我有一个名为InvestigatorGroup的表和一个名为InvestigatorGroupUsers的表，该表用于查看哪些组具有哪些用户.我试图让两个用户之间的共同调查员组

I have a table called InvestigatorGroup and a table called InvestigatorGroupUsers which is used to see what groups have what users. I am trying to get the common investigator group between two users

我的查询如下:

    public InvestigatorGroup GetCommonGroup(string userId, string investigatorUserId)
    {
        using (GameDbContext entityContext = new GameDbContext())
        {
            string[] ids = new[] { userId, investigatorUserId };

            return entityContext.InvestigatorGroups
                .Where(i => i.IsTrashed == false)
                .Include(i => i.InvestigatorGroupUsers)
                .Where(i => i.InvestigatorGroupUsers.Any(e => ids.Contains(e.UserId)))
                .OrderByDescending(i => i.InvestigatorGroupId)
                .GroupBy(i => i.InvestigatorGroupId)
                .Where(i => i.Count() > 1)
                .SelectMany(group => group).FirstOrDefault();
        }
    }

InvestigatorGroup实体如下:

The entity InvestigatorGroup is as follows:

public class InvestigatorGroup : IIdentifiableEntity
{
    public InvestigatorGroup()
    {
        this.InvestigatorGroupGames = new HashSet<InvestigatorGroupGame>();
        this.InvestigatorGroupUsers = new HashSet<InvestigatorGroupUser>();
    }

    // Primary key
    public int InvestigatorGroupId { get; set; }
    public string InvestigatorGroupName { get; set; }
    public bool HasGameAssignment { get; set; }
    public string GroupRoleName { get; set; }
    public bool IsTrashed { get; set; }

    // Navigation property
    public virtual ICollection<InvestigatorGroupUser> InvestigatorGroupUsers { get; private set; }
    public virtual ICollection<InvestigatorGroupGame> InvestigatorGroupGames { get; private set; }


    public int EntityId
    {
        get { return InvestigatorGroupId; }
        set { InvestigatorGroupId = value; }
    }

}

问题是它一直返回值0.在两个用户之间看不到共享组的计数为2.

The problem is that it keeps returning a value of 0. It doesn't see the shared group with a count of 2 between the two users.

我进行了一次测试，以返回组(我删除了count> 1个条件)，它不仅返回了两个用户的所有组，而且还返回了两个用户的所有组.

I did a test to return the groups (I removed the count>1 condition) and it returned all the groups for both users not only the one they have in common

我认为问题出在此行:.Where(i => i.InvestigatorGroupUsers.Any(e => ids.Contains(e.UserId))) 感谢您的帮助！

I believe the issue is with this line: .Where(i => i.InvestigatorGroupUsers.Any(e => ids.Contains(e.UserId))) Thanks for the help!

推荐答案

我已通过更改查询来解决此问题，以便查询包含UserId之一的行.然后，它将查询那些选定的行，并选择包含其他UserId(InvestigatorUserId)的那些行.这样，仅返回包含两者的行

I've resolved this by changing my query so that it searches for the rows containing one of the UserId's. Then it queries through those selected rows and selects the ones containing the other UserId (InvestigatorUserId). This way only the rows containing both are returned

我的新代码如下:

    public InvestigatorGroup GetCommonGroup(string userId, string investigatorUserId)
    {
        using (GameDbContext entityContext = new GameDbContext())
        {
            IEnumerable<InvestigatorGroup> userGroups = entityContext.InvestigatorGroups
                .Where(i => i.IsTrashed == false)
                .Include(i => i.InvestigatorGroupUsers)
                .Where(i => i.InvestigatorGroupUsers.Any(e => e.UserId.Contains(userId)))
                .OrderByDescending(i => i.InvestigatorGroupId);

            return userGroups.Where(i => i.InvestigatorGroupUsers.Any(e => e.UserId.Contains(investigatorUserId))).FirstOrDefault();

        }
    }

这篇关于使用LINQ lambda表达式在导航表中选择公共值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！