EF 6 creates query with non-existent table name when querying many-to-many relationship

Question

I've got the following classes:

public class Report
{
    [Key]
    public int ReportId { get; set;}

    public virtual ICollection<ReportDataSection> ReportDataSectios { get; } = new DbSet<ReportDataSection>();

    public virtual ICollection<DataSection> DataSections { get; } = new DbSet<DataSection>();

    // Other properties 
}

public class DataSection
{
    [Key]
    public int DataSectionId { get; set; }

    public virtual ICollection<ReportDataSection> ReportDataSections { get; } = new DbSet<ReportDataSection>();

    public virtual ICollection<Report> Reports { get; } = new DbSet<Report>();

    // Other properties
}

public class ReportDataSection
{
    [Key]
    [Column(Order = 0)]
    [DatabaseGenerated( DatabaseGeneratedOption.None)]
    public int ReportId { get; set; }

    [Key]
    [Column(Order = 1)]
    [DatabaseGenerated( DatabaseGeneratedOption.None)]
    public int DataSectionId { get; set; }

    public int OrderSeq { get; set; }

    public virtual DataSection { get; set; }

    public virtual Report { get; set; }

}

public class DbModel : DbContext
{
    public virtual DbSet<DataSection> DataSections { get; set; }

    public virtual DbSet<Report> Reports { get; set; }

    protected override OnModelCreating(DbModelBuilder modelBuilder)
    {
        // skipping irrelevant calls

        modelBuilder.Entity<DataSection>()
        .HasMany(e => ReportDataSection)
        .WithRequired(e => e.DataSection)
        .WillCascadeOnDelete(false);

        modelBuilder.Entity<Report>()
        .HasMany(e => ReportDataSection)
        .WithRequired(e => e.Report)
        .WillCascadeOnDelete(false);
    }
}

I'm trying to retrieve all of the data for a particular DataSection with code like this:

IQueryable<DataSection> query = (from ds in _db.DataSections
                                 where ds.DataSectionId == id
                                 select ds;
query = query.Include(ds => ds.ReportDataSections>)
.Include(ds => ds.Reports);

This produces a SQL statement that tries to join with table dbo.ReportDataSections1, which doesn't exist.

I found a question about a similar problem that had the same result for Entity Framework Core 5, but that involved owned tables. The fix was to make changes in the ModelBuilder calls, but I don’t know if that will work in this case as it’s a many-to-many relation ship in this case.

How do I get my query to work correctly?

Edit:

I neglected to mention that the OrderSeq column in the ReportDataSection table is required because it specifies the order in which the DataSections are listed within each Report. The answers given both indicate that the ReportDataSection table should be removed from the model, but how do I get the OrderSeq property/column on the table?

Answer 1

Fabio is correct in that for basic many-to-many relationships you can let EF manage the joining entity/tables. The error you are seeing is because you have doubled up the relationship so EF is attempting to map it twice, once for Report-DataSections/DataSection-Reports and once for the ReportDataSections on each as well. However, when you want to introduce additional information into the relationship, such as the OrderSeq(uence) then you need to introduce a joining entity. This is an either-or scenario, never both.

So if the joining table just needs to link Report and DataSection and has no other fields, Fabio's answer is what you need. Report can reference a set of DataSections and DataSection can reference a set of Reports. EF will expect and resolve a table called ReportDataSections or possibly DataSectionReports, but you may want to explicitly configure what joining table and columns to use. This can, but does not need to reference an Entity for ReportDataSections, the main point is that even if you do define an entity for the join, you wouldn't reference both a set of DataSections and ReportDataSections in Report for example.

Now since you want to add a column for OrderSequence to the joining table, likely for things like sorting, this is a scenario where you do need the joining entity. Mapping joining entities like this where there are additional columns so we need to access instances of that joining entity is a bit different. The many-to-many is mapped as a many-to-one-to-many:

public class Report
{
    [Key]
    public int ReportId { get; set;}

    public virtual ICollection<ReportDataSection> ReportDataSections { get; } = new List<ReportDataSection>();

    // Other properties 
}

public class DataSection
{
    [Key]
    public int DataSectionId { get; set; }

    public virtual ICollection<ReportDataSection> ReportDataSections { get; } = new List<ReportDataSection>();

    // Other properties
}

public class ReportDataSection
{
    [Key]
    [Column(Order = 0)]
    [DatabaseGenerated( DatabaseGeneratedOption.None)]
    public int ReportId { get; set; }

    [Key]
    [Column(Order = 1)]
    [DatabaseGenerated( DatabaseGeneratedOption.None)]
    public int DataSectionId { get; set; }

    public int OrderSeq { get; set; }

    public virtual DataSection { get; set; }

    public virtual Report { get; set; }

}

public class DbModel : DbContext
{
    public virtual DbSet<DataSection> DataSections { get; set; }

    public virtual DbSet<Report> Reports { get; set; }

    protected override OnModelCreating(DbModelBuilder modelBuilder)
    {
        // skipping irrelevant calls

        modelBuilder.Entity<DataSection>()
        .HasMany(e => ReportDataSections)
        .WithRequired(e => e.DataSection)
        .WillCascadeOnDelete(false);

        modelBuilder.Entity<Report>()
        .HasMany(e => ReportDataSections)
        .WithRequired(e => e.Report)
        .WillCascadeOnDelete(false);
    }
}

Note: Also when initializing collection references for navigation properties, I wouldn't use DbSet<T> (honestly didn't know that even works:) just initialize them with either List<T> or HashSet<T>. When data is loaded via EF the resulting lazy-load proxies will substitute the concrete implementation for the property, where this initialized concrete implementation is just default storage for new entities and any relations you want prior to persisting.

In this case we remove Report.DataSections and DataSection.Reports, each accesses the other through the common collection ReportDataSections.

THis approach is optional where ReportDataSections is just the Report & DataSection references, but is required when you want to introduce additional values like OrderSeq into the relationship. This changes how you would get DataSections for a given report. So for instance to eager load the data sections for a report you would need to switch from:

var report = _context.Reports
    .Include(x => x.DataSections)
    .Single(x => x.ReportId == reportId);

to

var report = _context.Reports
    .Include(x => x.ReportDataSections)
    .Include(x => x.ReportDataSections.Select(rds => rds.DataSection))
    .Single(x => x.ReportId == reportId);

The same applies to accessing the report's data sections. We don't have Report.DataSections, so we have to navigate through Report.ReportDataSections:

var dataSections = report.ReportDataSections
    .Select(rds => rds.DataSection)
    .ToList();

One option to make this a little more palatable is to use a non-mapped collection in Report:

[NotMapped]
public IEnumerable<DataSection> DataSections
{
   get { return ReportDataSections.Select(rds => rds.DataSection); }
} 

The caveat of something like this is that you cannot use this in a Linq expression that would be translated to SQL as it needs to be a NotMapped accessor otherwise EF will be confused about mapping an extra many-to-many relationship like what you are seeing. You also need to be wary of lazy-loading risks if you use this without having eager-loaded the ReportDataSections.

This does allow you to do stuff like returning the Report's DataSections ordered by OrderSeq:

var dataSections = report.ReportDataSections
    .OrderBy(rds => rds.OrderSeq)
    .Select(rds => rds.DataSection)
    .ToList();

or

[NotMapped]
public IEnumerable<DataSection> DataSections
{
    get 
    { 
       return ReportDataSections
           .OrderBy(rds => rds.OrderSeq)
           .Select(rds => rds.DataSection); 
    }
} 

Update: Dealing with changing associations with the many-to-many with the ReportDataSections. Many-to-Many relationships are typically related by association. What this means is that you have a set of Report records, and these are "related" to a set of DataSections. When you edit a report to change the data sections for a report, you aren't editing specific data sections but rather you want to remove any data sections that aren't related to this report, and add any new ones that currently aren't associated. We don't alter the data in the data section or attempt to alter ReportDataSections references which will get messy given the PK for the ReportDataSecton should be the ReportID + DataSectionID and databases do not allow altering a PK.

Say we take an UpdateReportViewModel into a controller action, and that view model contains a collection of DataSectionIDs for the current Data Sections we want the report to associate to. Updating the Report entity would look something like this:

var report = _context.Reports
    .Include(r => r.ReportDataSections)
    .Include(r => r.ReportDataSections.Select(rds => rds.DataSection)
    .Single(r => r.ReportID == updateReportVM.ReportId);
var existingDataSectionIDs = report.ReportDataSections
    .Select(rds => rds.DataSectionId)
    .ToList();

var dataSectionIDsToAdd = updateReportVM.DataSectionIDs
    .Except(existingDataSectionIDs);
var dataSectionIDsToRemove = existingDataSectionIDs
    .Except(updateReportVM.DataSectionIDs);

if(dataSectionIDsToRemove.Any())
{
    var reportDataSectionsToRemove = report.ReportDataSections
        .Where(rds => dataSectionIDsToRemove.Contains(rds.DataSectionID))
        .ToList();
    foreach(var reportDataSection in reportDataSectionsToRemove)
         report.ReportDataSections.Remove(reportDataSection);
}
if(dataSectionIDsToAdd.Any())
{
    var reportDataSectionsToAdd = _context.DataSections
        .Where(ds => dataSectionIdsToAdd.Contains(ds.DataSectionID))
        .Select(ds => new ReportDataSection { DataSection = ds })
        .ToList();
    foreach(var reportDataSection in reportDataSectionsToAdd)
        report.ReportDataSections.Add(reportDataSection);
}
int orderSeq = 0; 
foreach(var dataSectionID in updateReportVM.DataSectionIDs)
{
    var reportDataSection = report.ReportDataSections
        .FirstOrDefault(rds => rds.DataSectionID == dataSectionID);
    if (reportDataSection != null) // should never be #null, but handle softly...
        reportDataSection.OrderSeq = orderSeq++;
}
_context.SaveChanges();

The above assumes we get a view model to update that contains a simple Enumerable (array/list) of data section IDs in the order that the user wants to save. We load our report, eager loading the current set of data sections. We then extract our deltas by using Except to find what IDs have been added or removed. We find the actual ReportDataSection references for the items to remove, and remove them from the Report. This doesn't delete the DataSection, only the association. We then load the DataSections that need to be added, building new ReportDataSection using that and adding it to the Report.ReportDataSections which EF will complete the wiring for us when the data is saved.

The last step goes through the list of passed in DataSection IDs where we find the associated object and update the OrderSeq to reflect the order of IDs. Alternatively the Update Report view model could include a list of data section view models with the DataSectionID and OrderSeq which could be used to save specific values if the value is significant.