After reading extensively in this topic, I wanted to implement a multiple many-many relationship of my own. Here is my used case:
FYI: Although, there is no FK relationship between
Program
andProvider
,Program.ProvID
andProvider.ProvID
act as the relationship columns
In my DAL, I have the following Models:
public class Patient
{
public Patient()
{
Programs = new HashSet<Program>();
}
public virtual ICollection<Program> Programs { get; set; }
}
public class Program
{
public Program()
{
Patients = new HashSet<Patient>();
Organization = new HashSet<Organization>();
}
public int ProgramId { get; set; }
public string Name { get; set; }
public string SiteName { get; set; }
public int ProviderId { get; set; }
public virtual ICollection<Patient> Patients { get; set; }
public virtual ICollection<Organization> Organization { get; set; }
}
public class Organization
{
public Organization()
{
Programs = new HashSet<Program>();
}
public int OrgID { get; set; }
public string Name { get; set; }
public virtual ICollection<Program> Programs { get; set; }
}
In my AppContext
I mapped these models as following:
modelBuilder.Entity<Organization>().ToTable("Organization").HasKey(x => x.OrgID);
modelBuilder.Entity<Patient>().ToTable("Patient").HasKey(x => x.PatientID);
modelBuilder.Entity<Program>().ToTable("Program").HasKey(p => p.ProgramId);
modelBuilder.Entity<Program>().ToTable("Program").Property(p => p.ProviderId).HasColumnName("ProvID");
modelBuilder.Entity<Program>()
.HasMany(p => p.Patients)
.WithMany(p => p.Programs)
.Map(pp =>
{
pp.MapLeftKey("ProgID");
pp.MapRightKey("PatientID");
pp.ToTable("PatProg");
});
modelBuilder.Entity<Organization>()
.HasMany(o => o.Programs)
.WithMany(p => p.Organization)
.Map(prov =>
{
prov.MapLeftKey("OrgID");
prov.MapRightKey("ProvID");
prov.ToTable("Provider");
});
Now, I want to select ALL of the Patient
s that are in an Organization
.
return Context.Set<Organization>().AsNoTracking().Where(o => o.OrgID == organizationId)
.SelectMany(o => o.Programs)
.SelectMany(p => p.Patients)
However, this yields 0 return. Furthermore, when I run the profiler during execution, the output query IS NOT anything close to what I've mapped.
SELECT
[Join1].[PatientID1] AS [PatientID],
[Join1].[FirstName] AS [FirstName],
[Join1].[LastName] AS [LastName],
[Join1].[SSN#] AS [SSN#],
[Join1].[Suffix] AS [Suffix]
FROM
[dbo].[Provider] AS [Extent1]
INNER JOIN (
SELECT
[Extent2].[ProgID] AS [ProgID],
[Extent3].[PatientID] AS [PatientID1],
[Extent3].[FirstName] AS [FirstName],
[Extent3].[LastName] AS [LastName],
[Extent3].[SSN#] AS [SSN#],
[Extent3].[Suffix] AS [Suffix]
FROM
[dbo].[PatProg] AS [Extent2]
INNER JOIN [dbo].[Patient] AS [Extent3] ON [Extent2].[PatientID] = [Extent3].[PatientID] ) AS [Join1] ON [Extent1].[ProvID] = [Join1].[ProgID]
WHERE [Extent1].[OrgID] = 111
I'm really not sure what I'm doing wrong here.