locked
Foreign key tables not listed after adding Entity Data Model RRS feed

  • Question

  • User-2012457684 posted

    I have various foreign key tables in the database.   These tables contain the relationship between two other tables.   For example the Job_Locations table holds the JobID which is the primary key of the Jobs table  and the LocationID which is the primary key of the Locations table.  None of these foreign key tables is listed in my models.

    I can see in the edmx where it shows Jobs in the Navigation Properties of Location and Locations in the Navigation Properties of Job.  

    I figured out how to get a record count to be able to tell when the position is posted at a single location by using this

    var count = db.Jobs.Where(a => a.JobID == JobID).SelectMany(a => a.Locations).Count();

    Now I need to find the LocationID when there is only a single location  but am not sure how to do it  

     

    Thursday, February 9, 2017 3:58 PM

All replies

  • User-707554951 posted

    Hi muybn,

    For your problem, you want to load related entities.

    As far as I know. Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include

    method.

    https://msdn.microsoft.com/en-us/data/jj574232.aspx

    Best Regards

    Cathy

    Friday, February 10, 2017 6:39 AM
  • User-2012457684 posted

    OK this works

    var loc = db.Jobs.Where(a => a.JobID == JobID).Include(a => a.Locations);  

    However, it pulls all of the info for that location.  I tried adding Select to it

    var loc = db.Jobs.Where(a => a.JobID == JobID).Include(a => a.Locations).Select(a => a.LocationID);  

    but LocationID is not a property of Jobs


    Friday, February 10, 2017 3:47 PM
  • User1503189645 posted

    I'm still very new to this but I would guess:

    .Include(a => a.Locations.LocationID)

    Saturday, February 11, 2017 4:05 AM
  • User-2012457684 posted

    no,  that does not work.  I had already tried that.   I gave up trying to do it via entity and I ended up writing a stored procedure in the database and then using ExecuteScalar to get my result.  

    Monday, February 13, 2017 3:56 PM
  • User-707554951 posted

    Hi mj1223,

    but LocationID is not a property of Jobs

    Try to use the following code:

      var loc = db.Jobs.Where(a => a.JobID == JobID).Include(a => a.Locations..Select(loc => loc.LocationID));

    Best regards

    Cathy

    Friday, February 17, 2017 8:03 AM