locked
How do I include the Navigation ID (foreign key ID) in a LINQ result? RRS feed

  • Question

  • I have two entities:

    Job (Id, Name)

    Job Detail (Id, Name, Job_Id)

    I have a class called "JobContainer." I am having trouble including "Job_Id" (the foreign key / navigation property in EF) in the results of my query. The highlighted line below does not work. Any ideas how I can do this?

    Thanks!

    List<JobContainer> JobContainerList = new List<JobContainer>();

    using (MyDBdb = new MyDB())

    {

    JobContainerList = (from j in db.JobDetails

    select new JobContainer()

    {

    Id = j.Id,

    Name = j.Name,

    ParentId = j.JobReference.Value.Id

    }).ToList<JobContainer>();

    }

     

     

    public class JobContainer

    {

    public Int16 Id { get; set; }

    public string Name { get; set; }

    public Int16 ParentId { get; set; }

    }

    Monday, October 3, 2011 4:14 PM

Answers

All replies

  • Hi Matt;

    If I understand your question correctly the following change to your code should do what you want.

    List<JobContainer> JobContainerList = new List<JobContainer>();
     
    using (MyDBdb = new MyDB())
    {
        JobContainerList = (from j in db.JobDetails
                            select new JobContainer() 
                            { 
                                Id = j.Id,
                                Name = j.Name, 
                                ParentId = j.Job_Id 
                            }).ToList<JobContainer>(); 
    }
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 3, 2011 6:13 PM
  • Fernando, thanks for the reply - VS is only displaying "JobReference".  "Job_Id" is not available.  Although, in the database, "Job_Id" is clearly a column.  Any idea?  Do I need to set some property to expose the foreign key or something?
    Monday, October 3, 2011 6:17 PM
  •  

    What version of the Entity Framework? The one used in .Net Framework 3.5 or the one in .Net Framework 4.0?

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 3, 2011 6:24 PM
  •  

    Or was this a project that was upgraded from 3.5 to 4.0?

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 3, 2011 6:25 PM
  • I'm using EF 4.1. I verified this following one of your previous helpful posts (http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/45610cfb-cae3-452d-8117-371b8d851252)

    Monday, October 3, 2011 6:28 PM
  • I've been using EF 4.1. from the beginning.

    Monday, October 3, 2011 6:31 PM
  • I should be more clear - in the Model designer:

    - My "Job" entity only has "Id" and "Name" as properties

    - My "JobDetail" entity also only has "Id" and "Name" as properties 

    -  There is one-to-many relationship between Job and JobDetail (A job can have many JobDetail). 

     

    When I look at the SQL table for "JobDetails" there are three columns - "Id","Name", and "Job_Id"

    Thanks!

    Monday, October 3, 2011 6:36 PM
  •  

    When you created the Entity Model / EDMX in the dialog box "Entity Data Model Wizard" was the check box "Include foreign key columns in the model" check? If not that would be the reason.

     

     

    Note the last checkbox on the page

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 3, 2011 6:40 PM
  • I'm pretty sure I checked that.  Is there anything I can do after the fact to solve this?
    Monday, October 3, 2011 7:03 PM
  • In the Model Browser, the foreign key Job_Id is blank for the "Value / Property" column, and I'm guessing this is the issue.  However, the drop down does not have an option for "Job_Id : Int16" as I would expect.
    Monday, October 3, 2011 7:06 PM
  • Hi Matt;

    Not sure how to put it in after the model has been created but you should still be able to get to the value using the navigation property name. Try changing the NavigationPropertyName with the one shown in your model.

    JobContainerList = (from j in db.JobDetails
                        select new JobContainer() 
                        { 
                            Id = j.Id,
                            Name = j.Name, 
                            ParentId = j.NavigationPropertyName.Job_Id 
                        }).ToList<JobContainer>();
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, October 3, 2011 8:00 PM
  • Thanks for the help Fernando, I really appreciate it.  Unfortunately, the navigation property (JobReference) does not contain any of the scalar properties for JobDetail.  If I use the "Value" property, then the next "level" shows the properties - but this is where the error is occuring (see highlighted yellow line from first post which demonstrates this).

    Monday, October 3, 2011 8:46 PM
  • Hi Matt,

    Welcome!
    I think your problem relates to Lazy loading, please add Include like is:

    JobContainerList = (from j in db.JobDetails.Include("NavigationPropertyName")
                        select new
                        { 
                            Id = j.Id,
                            Name = j.Name, 
                            ParentId = j.NavigationPropertyName.Job_Id 
                        }).ToList<JobContainer>();
    
    

    I think this link is help: http://blogs.msdn.com/b/adonet/archive/2011/01/31/using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Friday, October 14, 2011 1:31 AM
    Tuesday, October 4, 2011 1:05 AM
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, October 7, 2011 7:07 AM