CODEWITHSUNDEEP
c#entity-frameworklinqmany-to-many
gsmith140
gsmith140

Reputation: 53

EF Code First- Return Records from Many to Many Table

I'm trying to get all records in an m:m table in a flat fashion with entity framework and linq.

Data Models:

public partial class Group
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid GroupId { get; set; }

    [StringLength(100)]
    public string Name { get; set; }

    public virtual ICollection<User> Users { get; set; }

}

public partial class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid UserId { get; set; } = new Guid();

    [StringLength(100)]
    public string FirstName { get; set; }

    [StringLength(100)]
    public string LastName { get; set; }

    [StringLength(255)]
    public string DisplayName { get; set; }


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

}

Here's the query in SQL which pulls back the dataset I'm looking for:

select g.GroupId, g.[Name], u.DisplayName, u.UserId 
from [Group] g, [User] u, [GroupUsers] gu
where g.GroupId = gu.Group_GroupId
and gu.User_UserId = u.UserId
order by g.[Name]

Here's what I'm trying in Linq, but I'm getting a self-referencing loop error:

using (RequestContext ctx = new RequestContext())
{
     return ctx.Groups.SelectMany(x => x.Users).Include(x => x.Groups).ToList();
}

This seems like it should be relatively easy but I've found m:m in Entity Framework can be a bit tricky. Any help would be appreciated.

Upvotes: 0

Views: 52

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

I'm glad you defined your many-to-many as two virtual collections, without specifying the junction table!

You do realize, that if you don't fetch "groups with their users", but instead query a left outer join of groups and users (you call that a flat fashion), the group properties will be repeated over and over again for every user?

But hey, that's your decision, and it is up to you to convince your project leader that it is better to transfer the same group properties a hundred times instead of transferring them only once.

You were right, for a left outer join, you need to do a SelectMany. I'm not sure why you decide to use Include instead of Select.

When querying data always use Select, and select only the properties that you actually plan to use. Only use Include if you plan to update the fetched included data.

The reason for this is especially meaningful in a one-to-many relation. If you fetch "Schools with their Students", and School with Id 4 has 1000 Students, then you know that every Student of this School will have a foreign key with a value 4. What a waste to transfer this value 4 over a 1000 times!

One of the overloads of Enumerable.SelectMany has a parameter resultSelector, which will take one Group and one User as input to create the result. This version is perfect for your needs

var result = dbContext.Groups.SelectMany(
    group => group.Users,
    (group, user) => new
    {
        // Select the Group properties you plan to use
        GroupId = group.GroupId,
        GroupName = group.Name,
        ...

        // Select the User properties you plan to use
        UserId = user.UserId,
        UserName = user.DisplayName,
        ...
    })

    // if desired do some ordering
    .OrderBy(joinedItem => joinedItem.GroupName);

Upvotes: 1

Related Questions