Query from a many to many relationship table-CodePudding

I have two tables Documents and Group like below. I joined the two tables creating a DocumentsGroup using code first.

Documents Table:

public class Documents
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<Group> Groups { get; set; 
}

Groups Table:

public class Group
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<Documents> Documents { get; set; }
}

Here is the DocumentsGroup table. This is the junction table and it does not have a model rather just showing how its looking.

{
    public int DocumentsId { get; set; }

    public int GroupsId{ get; set; }
}

I am trying to get all the documents which belong to one group from the junction table. I have the Group ID so am trying to get all the documents which belong to that ID like below:

int groupId = 4;
var documents = _database.Groups.Where(d => d.Id == groupId).Include(i => i.Documents).ToList();

I tried that but am not getting all the documents belonging to that group. Is there anything am doing wrong?

CodePudding user response：

Use the following query:

int groupId = 4;

var query = 
    from g in _database.Groups
    from d in g.Documents
    where g.Id == groupId
    select d;

var documents = query.ToList();

Or via method chain syntax:

int groupId = 4;

var documents = _database.Groups
    .Where(g => g.Id == groupId)
    .SelectMany(g => g.Documents)
    .ToList();

CodePudding user response：

Try reach the the Documents through the mapping table

            int groupId = 4;
            var documents = _database.DocumentsGroup
            .Where(x => x.GroupId == groupId)
            .Include(x => x.Documents)
            .Select(x => new Documents
            {
                Id = x.Documents.Id,
                // add all props you need
            })
            .ToList();

But if you don't have the mapping table just create it or you can try:

            int groupId = 4;
            var documents = _database.Groups
            .Include(x => x.Documents)
            .Where(x => x.Id == groupId )
            .ToList();

CodePudding user response：

The query you have written will return a result set of groups, not the documents. If "Group" table's "Id" column is unique, you should write this as:

var group = dbContext.Groups.Include(g => g.Documents).FirstOrDefault(g => g.Id == 4); //Given group id is 4
if (group != null) { 
      var documents = group.Documents.ToList(); // Here you should get the desired Documents, given that the the tables are correctly configured 
}

If this does not help you, please give more detail. If this helps, please vote up :)