0

After reading extensively in this topic, I wanted to implement a multiple many-many relationship of my own. Here is my used case:

enter image description here

FYI: Although, there is no FK relationship between Program and Provider, Program.ProvID and Provider.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 Patients 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.

Ali Khakpouri
  • 801
  • 8
  • 24
  • Where did you get this syntax for many to many without explicit joining table. Afaik EF didn’t support that yet and you need to specify joining tables. – Vidmantas Blazevicius Jun 10 '19 at 19:43
  • As it turns out, this is actually a one -> many on both sides. `Programs` 1 -> many to `Providers` and `Provider` is 1 -> many to `Organization`. – Ali Khakpouri Jun 18 '19 at 13:21

0 Answers0