public List<ClassParticipantViewModel> GetClassParticipants(string studentId)
{
    List<ClassParticipantViewModel> list = new List<ClassParticipantViewModel>();

    var results = (from cp in _DbContext.ClassParticipants
                   join c in _DbContext.Classes
                   on cp.ClassId equals c.ClassId
                   where cp.StudentId.Equals(studentId)
                   select new ClassParticipantViewModel
                   {
                       //ClassParticipant
                       ClassParticipantId = cp.ClassParticipantId,
                       StudentId = cp.StudentId,
                       ClassParticipantCreatedOn = cp.CreatedOn,

                       //Class
                       ClassId = c.ClassId,
                       ClassCode = c.ClassCode,
                       Section = c.Section,
                       CourseCode = c.CourseCode,
                       Description = c.Description,
                       Units = c.Units,
                       CreatedBy = c.CreatedBy,
                       ClassCreatedOn = c.CreatedOn,
                   })
                   .ToList();

    list = list.Concat(results).ToList();

    return list.ToList();
}

Join error:

Models:

ViewModel:

I'm developing a Web API using .NET Core 3.0 then I got an error stating that Ambiguous call to 'Join', this code is worked in my other projects. I'm confused if what is wrong, does the naming convention of my models have an effect?

CodePudding user response：

Quick intro to navigation props in EF:

If your classes looked like:

class Class {
  ICollection<ClassParticipant> ClassParticipants {get; set;}
  ...
}
class ClassParticipant {
  Class Class{get;set;}
  Participant Participant{get;set;}
  ...
}
class Participant{
  ICollection<ClassParticipant> ParticipantClasses {get; set;}
  ...
}

EF would be able to work out that this is a typical 1:M:1 relationship between classes:classparticipants:participants - many classes have many participants and the middle table breaks it down.

Once you've done that property mapping, you can write queries like:

_DbContext.ClassParticipants.Select(cp =>
   new ClassParticipantViewModel
   {
       //ClassParticipant
       ClassParticipantId = cp.ClassParticipantId,
       StudentId = cp.StudentId,
       ClassParticipantCreatedOn = cp.CreatedOn,

       //Class
       ClassId = cp.Class.ClassId,
       ClassCode = cp.Class.ClassCode,
       Section = cp.Class.Section,
       CourseCode = cp.Class.CourseCode,
       Description = cp.Class.Description,
       Units = cp.Class.Units,
       CreatedBy = cp.Class.CreatedBy,
       ClassCreatedOn = cp.Class.CreatedOn,
   }
).ToList();

EF will see you navigating around in the select (cp.Class.ClassId), and know it has to join Classes in to get the data you want. You can also make direct navigational references in a similar way in a Where clause to load the related data, or, if you know you won't refer to the related data in the Select or Where, but you will want the data to be loaded so you can later refer to it in C#, you can use Include:

_DbContext.ClassParticipants.Include(cp => cp.Class).ToList();

footnote, you might want to have e.g. ICollection<ClassParticipant> ClassParticipants {get; set;} = new HashSet<ClassParticipant>(); because it makes creating new entities easier, not having to remember to make the collection first:

var c = new Class(...);
var p = new Participant(...);

//can do this without having to remember to make a new collection
c.ClassParticipants.Add(new ClassParticipant { Class = c, Participant = p });