Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
I have the following n:m relationship:
Clubs - ClubsMembers - Members

I like to load a club with his members sorted by lastname with Entity Framework Core.

What I have tried:

My classes are:

public partial class Club
{
public Club()
{
ClubsMembers = new HashSet<clubmember>();
}
public int Id { get; set; }
public string Name { get; set; }

[InverseProperty("Club")]
public virtual ICollection<clubmember> ClubsMembers { get; set; }
}

public partial class ClubMember
{
public int ClubId { get; set; }
public int MemberId { get; set; }

[ForeignKey("ClubId")]
[InverseProperty("ClubMember")]
public virtual Club Club { get; set; }
[ForeignKey("MemberId")]
[InverseProperty("ClubMember")]
public virtual Member Member{ get; set; }
}

public partial class Member
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}

I can query the members of a club with following statement:

var club = await _context.Clubs
.Include(c => c.ClubsMembers)
.ThenInclude(d => d.Member)
.SingleOrDefaultAsync(m => m.Id == id);

Now I get the club members unsorted, this is working.
But I found no way to sort the club members by LastName, like this (not working)

var club = await _context.Clubs
.Include(c => c.ClubsMembers.OrderBy(o => o.Member.LastName))
.ThenInclude(d => d.Member)
.SingleOrDefaultAsync(m => m.Id == id);
Posted
Updated 22-Aug-16 7:37am

1 solution

Check out this sample, stick it in dotnetfiddle (it wasn't working when I tried to upload it)
or put into your own console app.

The short answer is data will not be ordered within query like that so resulting will be sorted by top of query hierarchy sort default.

to fix:
1. query the data you want to order by into its own variable.
2. use that collection as the main list to get other data from e.g. club names. you just sometimes have to flip your thinking about object queries linq.

Also watch how you do Include statement as you should include parent table data first.

C#
using System.Collections.Generic;
using System.Linq;
using System;

namespace ConsoleApplication1
{
    public class Program
    {
        private static IEnumerable<Club> _Clubs = new Club[]
        {
            new Club { Id = 1, Name = "Club1" },
            new Club { Id = 2, Name = "Club2" }
        };

        private static IEnumerable<Member> _Members = new Member[]
        {
            new Member { Id = 1, Name = "Joey" },
            new Member { Id = 2, Name = "Chandler" },
            new Member { Id = 3, Name = "Ross" },
            new Member { Id = 4, Name = "Phoebe" },
            new Member { Id = 5, Name = "Veronica" },
            new Member { Id = 6, Name = "Rachel" }
        };

        private static IEnumerable<ClubMember> _ClubMembers =
            Associate(_Clubs.Single(x => x.Id == 1), _Members.Where(x => new int[] { 1, 3, 5 }.Contains(x.Id)))
            .Concat(
            Associate(_Clubs.Single(x => x.Id == 2), _Members.Where(x => new int[] { 2, 4, 6 }.Contains(x.Id)))
            );

        public static void Main()
        {
            var clubsAsc = _Clubs.OrderBy(x => x.Name).Select(x => x.Name);
            var clubsDesc = _Clubs.OrderByDescending(x => x.Name).Select(x => x.Name);

            var club1membersAsc = _ClubMembers.Where(x=>x.ClubId==1).OrderBy(x => x.Member.Name);
            var club1membersDesc = _ClubMembers.Where(x=>x.ClubId==1).OrderByDescending(x => x.Member.Name);
            var club2MembersAsc = _ClubMembers.Where(x=>x.ClubId==2).OrderBy(x => x.Member.Name);
            var club2MembersDesc = _ClubMembers.Where(x=>x.ClubId==2).OrderByDescending(x => x.Member.Name);

            Console.WriteLine("Clubs ASC: " + Environment.NewLine + FormatText(clubsAsc));
            Console.WriteLine();
            Console.WriteLine("Clubs DESC: " + Environment.NewLine + FormatText(clubsDesc));
            Console.WriteLine();
            Console.WriteLine("Club1Members DESC:" + Environment.NewLine + FormatClubMembers(club1membersDesc));
            Console.WriteLine();
            Console.WriteLine("Club1Members ASC:" + Environment.NewLine + FormatClubMembers(club1membersAsc));
            Console.WriteLine();
            Console.WriteLine("Club2Members ASC:" + Environment.NewLine + FormatClubMembers(club2MembersAsc));
            Console.WriteLine();
            Console.WriteLine("Club2Members DESC:" + Environment.NewLine + FormatClubMembers(club2MembersDesc));

            Console.ReadKey();
        }

        public static string FormatText(IEnumerable<string> array)
        {
            return array.Aggregate((current, next) => current + Environment.NewLine + next);
        }

        public static string FormatClubMembers(IEnumerable<ClubMember> array)
        {
            return array.Select(x => FormatClubMember(x))
                .Aggregate((current, next) => current + Environment.NewLine + next);
        }

        public static string FormatClubMember(ClubMember cm)
        {
            return "Club: " + cm.Club.Name + " - " + "Member: " + cm.Member.Name;
        }

        private static IEnumerable<ClubMember> Associate(Club club, IEnumerable<Member> members)
        {
            foreach (var mem in members)
            {
                yield return new ClubMember
                {
                    Club = club,
                    ClubId = club.Id,
                    Member = mem,
                    MemberId = mem.Id
                };
            };
        }
    }

    public class Club
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class ClubMember
    {
        public int ClubId { get; set; }
        public int MemberId { get; set; }

        public virtual Club Club { get; set; }
        public virtual Member Member { get; set; }
    }

    public class Member
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}



Output:

Clubs ASC:
Club1
Club2

Clubs DESC:
Club2
Club1

Club1Members DESC:
Club: Club1 - Member: Veronica
Club: Club1 - Member: Ross
Club: Club1 - Member: Joey

Club1Members ASC:
Club: Club1 - Member: Joey
Club: Club1 - Member: Ross
Club: Club1 - Member: Veronica

Club2Members ASC:
Club: Club2 - Member: Chandler
Club: Club2 - Member: Phoebe
Club: Club2 - Member: Rachel

Club2Members DESC:
Club: Club2 - Member: Rachel
Club: Club2 - Member: Phoebe
Club: Club2 - Member: Chandler
 
Share this answer
 
v2
Comments
js0000505 23-Aug-16 12:02pm    
Your solution with flipping the main list to ClubMember is working :-)
As an old SQL guy I have to learn the new thinking in Entity Framework - Linq

Thanx!
njammy 23-Aug-16 12:13pm    
You're welcome happy coding.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900