EntityFramework - One to many relationship

问题:

I have a one-to-many relationship between MapHeader and MapDetail tables therefore there can be multiple mapdetails for a single mapheader. In the database, table MapDetail has foreign key MapHeaderID which maps to the pk(MapHeaderId) in MapHeader table.

I have defined it in EntityFramework Code-first as follows:

public class MapHeader
{
    public int MapHeaderID { get; set; }
    ....
    public virtual ICollection<MapDetail> mapDetails
    {
        get;
        set;
    } 
 }

public class MapDetail
{
    public int MapDetailID { get; set; }
    public int MapHeaderID { get; set; }
    ....
    public virtual MapHeader mapheader { get; set; }
}

FLUNETAPI

modelBuilder.Entity<MapDetail>()
            .HasRequired<MapHeader>(md => md.mapheader)
            .WithMany(mh => mh.mapDetails)
            .HasForeignKey(md => md.MapHeaderID);

It does not link!! my mapheader property inside mapdetail record/object is still null...What am i doing wrong?

CODE - WRITTEN IN RAZOR VIEW

foreach (MapDetail geMapDetail in Model.mapDetails)
   {
     if(...)
     {
       if(...){...}
       else{
          <td>
          foreach(..)
          {
              var term = geMapDetail.Term == 0 ? geMapDetail.mapheader.Term : geMapDetail.Term;
          }
          </td>
       }
     }

the code crashes on the above geMapDetail.mapheader since mapheader is null

My query is in a stored procedure

select distinct md.yearid, md.assessmentid, md.resulttypeid,
    concat(ah.name, ' - ', a.name) as Name, md.term, md.semester, md.month, md.week,
   md.MapDetailID, md.MapHeaderID, md.ColourFormatType, md.ResultTypeIDs,
    md.RowOrder, md.Attendance, md.EffectSize, md.Growth, md.IndicatorID,
    md.TeacherNameRequired, md.AllowEdit, md.PageHeaderID, md.IncludePreviousTerms,
    md.IncludePreviousSemesters, y.year
from mapdetail md
left outer join assessments a on a.assessmentid = md.assessmentid
left outer join assessments ah on ah.assessmentID = a.headerID
left outer join years y on md.yearID = y.yearID
left outer join assessmentresulttypes art on a.assessmentid = art.assessmentid
left outer join resulttypes rt on rt.resulttypeid = art.resulttypeid
where  md.mapheaderid = 22;

DBCONTEXT

 public MapDetailResultSet Find_MapDetails(int mapHeaderId, int yearId, string classIds, int indicatorGroup, string indicatorIds)
    {
        var query = "CALL Find_MapDetails(@mapHeaderId, @yearId, @classIds, @indicatorGroup, @indicatorIds)";

        MySqlParameter[] mySqlParams = new MySqlParameter[] { new MySqlParameter("mapHeaderId", mapHeaderId),
                                                                new MySqlParameter("yearId", yearId),
                                                                new MySqlParameter("classIds", classIds),
                                                                new MySqlParameter("indicatorGroup", indicatorGroup),
                                                                new MySqlParameter("indicatorIds", indicatorIds)
                                                            };

        MapDetailResultSet mapdetails = new MapDetailResultSet();

        using (var multiResultSet = DbContextExtensions.MultiResultSetSqlQuery(this, query, mySqlParams))
        {
            mapdetails.mapDetails = multiResultSet.ResultSetFor<MapDetail>().ToList();
            //other result sets
            ...

        }
  return mapdetails;
  }

I have also disabled lazyloading in DBContext: (didnt help)

 public geContext(string connString):base(connString) 
    {
        this.Configuration.LazyLoadingEnabled = false;
        Database.SetInitializer(new MySqlInitializer());
    }

UPDATE

select distinct md.yearid, md.assessmentid, md.resulttypeid,
    concat(ah.name, ' - ', a.name) as Name, md.term, md.semester, md.month, md.week,
   md.MapDetailID, md.MapHeaderID, md.ColourFormatType, md.ResultTypeIDs,
    md.RowOrder, md.Attendance, md.EffectSize, md.Growth, md.IndicatorID,
    md.TeacherNameRequired, md.AllowEdit, md.PageHeaderID, 
    md.IncludePreviousTerms,
    md.IncludePreviousSemesters, y.year
   from mapdetail md
   left outer join assessments a on a.assessmentid = md.assessmentid
   left outer join assessments ah on ah.assessmentID = a.headerID
   left outer join years y on md.yearID = y.yearID
   left outer join assessmentresulttypes art on a.assessmentid = art.assessmentid
   left outer join resulttypes rt on rt.resulttypeid = art.resulttypeid
   left outer join mapheader mh on mh.mapheaderID = md.mapheaderID
    where  md.mapheaderid = 22;

回答1:

This worked fine for me with just your model classes. You don't even need the FluentAPI code because your class definition is following the Code-First foreign key convention. I used these model classes:

public class MapHeader
{
    public int MapHeaderId { get; set; }

    public virtual List<MapDetail> MapDetails { get; set; }
}

public class MapDetail
{
    public int MapDetailId { get; set; }

    public int MapHeaderId { get; set; }
    public virtual MapHeader MapHeader { get; set; }
}

Then I wrote a simple command line program to save some data and read it back from the database:

private static void InsertMapHeader()
{
    var header = new MapHeader
    {
        MapDetails = new List<MapDetail>
        {
            new MapDetail(),
            new MapDetail(),
            new MapDetail(),
            new MapDetail()
        }
    };

    using (var context = new BreakAwayContext())
    {
        context.MapHeaders.Add(header);
        context.SaveChanges();
    }
}

private static void ReadMapDetails()
{
    using (var context = new BreakAwayContext())
    {
        var detail = context.MapDetails.FirstOrDefault();
        Console.WriteLine("Header id: {0}", detail.MapHeader.MapHeaderId);
    }
}

This code gets the first MapDetail and follows the navigation property to MapHeader. All of this work fine with no issue because Code-First is using lazy loading to get the MapHeader navigation property when it's asked for.

If you want to eagerly load the dependent property, you can change your query to use the Include method to join the MapHeaders table, like this:

var detail = context.MapDetails.Include(d => d.MapHeader).FirstOrDefault();

You're still not showing the code for your query (it's probably in your Control class), so it's hard to say what the exact issue might be.

回答2:

not the ideal way to do but it got me through anyway.. i loaded the particular mapheader from the repository and then populated each mapdetail object in the list manually

Controller

   MapHeaderRepository repMapHeader = new MapHeaderRepository("name=ge");
   MapDetailsRepository repMapDetail = new MapDetailsRepository("name=ge");

   MapDetailResultSet mapDetailResultSet = repMapDetail.FindMapDetails(mapHeaderId, yearId, classIds, 
                                                    indicatorGroup, indicatorIds);
   var mapHeader = repMapHeader.Get(22);

   foreach (MapDetail md in mapDetailResultSet.mapDetails)
   {
        md.mapheader = mapHeader;
   }