Goal

I have two tables related by a many-to-many relationship. I want to get a single item from one side of the many-to-many after filtering the other side.

Say tables Person and PersonRole. In this scenario a role is a customer champion. I want to get a single champion for a given customer id.

Code

Entities:

public class Person
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string MiddleName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public ICollection<PersonRole> PersonRole { get; set; } = new List<PersonRole>();
}

public class PersonRole
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PersonRoleId { get; set; }
    public int PersonRoleLUId { get; set; }
    public string RoleTargetType { get; set; }
    public int RoleTargetId { get; set; }
    public ICollection<Person> Person { get; set; } = new List<Person>();
}

Repository class (works, but requires collection return type):

// Get *single* Champion for given customer.
// Only way to make this compile is to have a Collection as the return type.
// This just doesn't feel right, and I'm looking for a more correct way to do this.  
public async Task<IEnumerable<Person>> GetChampionForCustomerAsync(int customerId,
         int championId)
{
    const int CustomerChampionRoleId = 1;

    return await _context.PersonRoles
                         .Where(personRole => personRole.PersonRoleLUId == CustomerChampionRoleId)
                         .Where(personRole => personRole.RoleTargetType == "Customer")
                         .Where(personRole => personRole.RoleTargetId == customerId)
                         .Include(personRole => personRole.Person)
                         .Select(person => person.Person
                                                 .Where(person => person.Id == championId))
                         .FirstOrDefaultAsync();
}

Repository class (doesn't work):

public async Task<Person> GetChampionForCustomerAsync(int customerId, int championId)
{
    const int CustomerChampionRoleId = 1;

    return (Person) await _context.PersonRoles
                                  .Where(personRole => personRole.PersonRoleLUId == CustomerChampionRoleId)
                                  .Where(personRole => personRole.RoleTargetType == "Customer")
                                  .Where(personRole => personRole.RoleTargetId == customerId)
                                  .Include(personRole => personRole.Person)
                                  .Select(person => person.Person
                                                          .Where(person => person.Id == championId))
                                  .SingleOrDefaultAsync();
}

Errors

CS0266 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<MaelstromPlatform.API.Entities.Person>' to 'MaelstromPlatform.API.Entities.Person'. An explicit conversion exists (are you missing a cast?) MaelstromPlatform.API

System.InvalidCastException: Unable to cast object of type 'System.Collections.Generic.List`1[MaelstromPlatform.API.Entities.Person]' to type 'MaelstromPlatform.API.Entities.Person'.

Without the cast suggested I get the first error when I compile. If I add the cast like it suggests, I can compile but I get an error in the log & Swagger when I run the API call.

Questions

1. Why does a return type of a collection make this all work as expected?
2. Why doesn't the linq query with the .SingleOrDefault (which I understand should return one and only one or a default) still return a collection?
3. Why does adding a cast like the message suggested still fail at runtime but not at compile-time?
4. Is there a different way to go about my table structure or queries to make this work?

CodePudding user response：

Use SelectMany to flatten Person collection:

public Task<Person> GetChampionForCustomerAsync(int customerId, int championId)
{
    const int CustomerChampionRoleId = 1;

    return _context.PersonRoles
        .Where(personRole => personRole.PersonRoleLUId == CustomerChampionRoleId)
        .Where(personRole => personRole.RoleTargetType == "Customer")
        .Where(personRole => personRole.RoleTargetId == customerId)
        .SelectMany(personRole => personRole.Person)
        .Where(person => person.Id == championId))
        .SingleOrDefaultAsync();
}