locked
The property 'JobTitle' is not a navigation property of entity type 'Job'. The 'Include(string)' method can only be used with a '.' separated list of navigation property names. RRS feed

  • Question

  • User-872746141 posted

    I am attempting to read related data across three tables in ASP.NET CORE MVC 2.2 with Entity Framework Core.

    To be more specific I am using this tutorial to build my project https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/read-related-data?view=aspnetcore-2.2. I am using Eager Loading to read Job.JobTitle all the way from the Problem Index method. Reading this I realize I want to find where Problem navigation property that contains the Job entity of the JobTitle that the Problem is assigned to. But That would be a many to many relationship so I have thrown in a Result composite table to stop that.

    From my research, adding the foreign key attributes can help. Which hasn’t. So how can I Render the JobTitle that the problem belongs too in the job index?

    Job class:

    public class Job
        {  
    
            public int ID { get; set; }
            public string JobTitle { get; set; }
            public string JobDescription { get; set; }
            public DateTime JobStartDate {get;set;}
            public DateTime JobDeadline {get;set;}
            public bool JobIsComplete{get;set;}
            
            public ICollection<Result> Results {get;set;}
        }
    

    Result class:

        public class Result
        {        
            public int JobID {get;set;}
            
            public int ProblemID {get;set;}
            [ForeignKey("Job")]
    public Job Job {get;set;} [ForeignKey("Problem")] public Problem Problem {get;set;} }

    Problem class:

    public class Problem
        {
            public int ID {get;set;}       
            public string ProblemTitle {get;set;}
            public string ProblemDescription {get;set;}
            public DateTime ProblemStartDate {get;set;}
            public string ProblemFileAttachments {get;set;}
            public int ProblemSeverity {get;set;}
            public bool ProblemComplete {get;set;}
    public ICollection<Result> Result {get;set;} }

    Index method of ProblemController (please see comment to find bug):

    public async Task<IActionResult> Index(string sortOrder, string currentFilter, string searchString, int? pageNumber)
            {
                ViewData["CurrentSort"] = sortOrder;
                ViewData["ProblemIDSortParm"] = String.IsNullOrEmpty(sortOrder) ? "ProblemID_desc" : "";
                ViewData["ProblemTitleSortParm"] = String.IsNullOrEmpty(sortOrder) ? "ProblemTitle_desc" : "";
                ViewData["ProblemStartDateSortParm"] = sortOrder == "ProblemStartDate" ? "ProblemStartDate_desc" : "ProblemStartDate";
                ViewData["ProblemSeveritySortParm"] = sortOrder == "ProblemSeverity" ? "ProblemSeverity_desc" : "ProblemSeverity";
                ViewData["ProblemCompleteSortParm"] = sortOrder == "ProblemComplete" ? "ProblemComplete_desc" : "ProblemComplete";          
                ViewData["CurrentFilter"] = searchString;
                
                //READ RELATED DATA HERE
                var problems = from p in _context.Problems
                    .Include(p => p.Result)
                        .ThenInclude(j => j.Job.JobTitle)                    
                                select p;
                //END OF READ RELATED DATA
                if(searchString != null)
                {
                    pageNumber = 1;
                }
                else
                {
                    searchString = currentFilter;
                }
    
                if(!String.IsNullOrEmpty(searchString))
                {
                    problems = problems.Where(p => p.ProblemTitle.Contains(searchString)
                                                || p.ProblemDescription.Contains(searchString));
                }
    
                switch (sortOrder)
                {
                    case "ProblemID_desc":
                        problems = problems.OrderByDescending(p => p.ID);
                        break;
                    case "ProblemTitle_desc":
                        problems = problems.OrderByDescending(p => p.ProblemTitle);
                        break;
                    case "ProblemStartDate":
                        problems = problems.OrderBy(p => p.ProblemStartDate);                    
                        break;
                    case "ProblemStartDate_desc":
                        problems = problems.OrderBy(p => p.ProblemStartDate);                    
                        break;
                    case "ProblemSeverity":
                        problems = problems.OrderBy(p => p.ProblemSeverity);
                        break;
                    case "ProblemSeverity_desc":
                        problems = problems.OrderByDescending(p => p.ProblemSeverity);
                        break;   
                    case "ProblemCompleteSortParm":
                        problems = problems.OrderBy(p => p.ProblemComplete);
                        break;
                    case "ProblemCompleteSortParm_desc":
                        problems = problems.OrderByDescending(p => p.ProblemComplete);
                        break; 
                    default:
                        problems = problems.OrderBy(p => p.ProblemTitle);
                        break;                 
                }
    
                int pageSize = 20;            
                return View(await PaginatedList<Problem>.CreateAsync(problems.AsNoTracking(), pageNumber ?? 1, pageSize));
            }
    

    ProblemIndex view, (please see comment to find where I am having problem):

    @model PaginatedList<Pitcher.Models.Problem>
    
    @{
        ViewData["Title"] = "Problems";
    }
    
    <h1>Index</h1>
    
    <p>
        <a asp-action="Create">Create New</a>
    </p>
     @*COPY AND PASTE THIS TAG HELPER METHOD TEXTBOX CUSTOMIZATION INTO OTHER VIEWS TO ENABLE SEARCHING.*@
    <form asp-action="Index" method="get">
        <div class="form-actions no-color">
            <p>
                Find by name: <input type="text" name="SearchString" value="@ViewData["currentFilter"]" />
                <input type="submit" value="Search" button type="button" class="btn btn-primary" /> |
                <a asp-action="Index">Back to Full List </a>
            </p>
        </div>
    </form>
    <table class="table table-hover">
        <thead>
            <tr>
                <th>
                    <a asp-action="Index" asp-route-sortOrder="@ViewData["ProblemIDSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Problem ID</a>
                </th>
                <th>
                    <a asp-action="Index" asp-route-sortOrder="@ViewData["ProblemTitleSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Problem Title</a>
                </th>
                <th>
                    <a asp-action="Index" asp-route-sortOrder="@ViewData["ProblemStartDateSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Problem Start Date</a>
                </th>
                <th>
                    Problem File Attachments
                </th>
                <th>
                    <a asp-action="Index" asp-route-sortOrder="@ViewData["ProblemSeveritySortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">ProblemSeverity</a>
                </th>
                <th>
                    <a asp-action="Index" asp-route-sortOrder="@ViewData["ProblemCompleteSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">ProblemComplete</a>
                </th>
                <th>
                    Job Title
                </th>
                <th></th>
            </tr>
        </thead>
        <tbody>
    @foreach (var item in Model) {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.ID)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ProblemTitle)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ProblemDescription)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ProblemStartDate)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ProblemFileAttachments)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ProblemSeverity)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ProblemComplete)
                </td>
                @* RENDER Job Title here *@
                    @Html.DisplayFor(modelItem => item.Result)
                @* END render Job Title here *@
                <td>
                    <a asp-action="Edit" asp-route-id="@item.ID" button type="button" class="btn btn-primary btn-block" >Edit</a> 
                    <a asp-action="Details" asp-route-id="@item.ID" button type="button" class="btn btn-info btn-block">Details</a> 
                    <a asp-action="Delete" asp-route-id="@item.ID" button type="button" class="btn btn-primary btn-block">Delete</a>
                </td>
            </tr>}
        </tbody>
    </table>
    @{
        var prevDisabled = !Model.HasPreviousPage ? "disabled" : "";
        var nextDisabled = !Model.HasNextPage ? "disabled" : "";
    }
    <a asp-action="Index"
       asp-route-sortOrder="@ViewData["CurrentSort"]"
       asp-route-pageNumber="@(Model.PageIndex - 1)"
       asp-route-currentFilter="@ViewData["CurrentFilter"]"
       class="btn btn-secondary @prevDisabled"
       button type="button">
        Previous
    </a>
    <a asp-action="Index"
       asp-route-sortOrder="@ViewData["CurrentSort"]"
       asp-route-pageNumber="@(Model.PageIndex + 1)"
       asp-route-currentFilter="@ViewData["CurrentFilter"]"
       class="btn btn-secondary @nextDisabled"
       button type="button">   
        Next
    </a>
    





    Tuesday, May 26, 2020 11:07 AM

Answers

  • User711641945 posted

    Hi 287papa,

    1.Your model should be changed like below:

    public class Result
    {
        public int JobID { get; set; }
        public int ProblemID { get; set; }
        //[ForeignKey("Job")]
        public Job Job { get; set; }
        //[ForeignKey("Problem")]
        public Problem Problem { get; set; }
    
    }
    public class Problem { public int ID { get; set; } public string ProblemTitle { get; set; } public string ProblemDescription { get; set; } public DateTime ProblemStartDate { get; set; } public string ProblemFileAttachments { get; set; } public int ProblemSeverity { get; set; } public bool ProblemComplete { get; set; } public ICollection<Result> Result { get; set; } } public class Job { public int ID { get; set; } public string JobTitle { get; set; } public string JobDescription { get; set; } public DateTime JobStartDate { get; set; } public DateTime JobDeadline { get; set; } public bool JobIsComplete { get; set; } public ICollection<Result> Results { get; set; } }

    2.Be sure your DbContext configure like below:

    public class MyDbContext:DbContext
    {
    
        public MyDbContext(DbContextOptions<MyDbContext> options):base(options)
        {
        }
        public DbSet<Result> Results { get; set; }
        public DbSet<Job> Jobs { get; set; }
        public DbSet<Problem> Problems { get; set; }
    
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Result>()
                .HasKey(bc => new { bc.JobID, bc.ProblemID });
            modelBuilder.Entity<Result>()
                .HasOne(bc => bc.Job)
                .WithMany(b => b.Results)
                .HasForeignKey(bc => bc.JobID);
            modelBuilder.Entity<Result>()
                .HasOne(bc => bc.Problem)
                .WithMany(c => c.Result)
                .HasForeignKey(bc => bc.ProblemID);
        }
    }

    3.Your controller:

    var problems = from p in _context.Problems
        .Include(p => p.Result)
            .ThenInclude(j => j.Job)
                    select p;

    4.Your View:

    <td>
        @Html.DisplayFor(modelItem => item.ProblemComplete)
    </td>
    
    <td>
        @foreach (var title in item.Result)
        {
            @Html.DisplayFor(modelItem => title.Job.JobTitle)
            <br />                         
    } </td>

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 27, 2020 8:52 AM

All replies

  • User1034446946 posted

    includes only include tables, job title is a field so doesn't need an include

    problems.Results.Job.JobtTitle

    i think should get it.

    Tuesday, May 26, 2020 5:37 PM
  • User-872746141 posted

    problems.Results.Job.JobtTitle

    Hi, thanks for your post but I'm confused there. Where would I write that?

    Tuesday, May 26, 2020 7:58 PM
  • User1034446946 posted
    var problems = from p in _context.Problems
                    .Include(p => p.Result)
                        .ThenInclude(j => j.Job.JobTitle)   <- this is wrong                 
                                select p;

    for two reasons,

    1, you can only go one navigation property at a time
    2, job title isn't a navigation property is a field in a table

    if you remove job title it will solve the query

    if you need the jobtitle, get to it though the problems variable (as above), where every you want to

    Tuesday, May 26, 2020 8:04 PM
  • User711641945 posted

    Hi 287papa,

    1.Your model should be changed like below:

    public class Result
    {
        public int JobID { get; set; }
        public int ProblemID { get; set; }
        //[ForeignKey("Job")]
        public Job Job { get; set; }
        //[ForeignKey("Problem")]
        public Problem Problem { get; set; }
    
    }
    public class Problem { public int ID { get; set; } public string ProblemTitle { get; set; } public string ProblemDescription { get; set; } public DateTime ProblemStartDate { get; set; } public string ProblemFileAttachments { get; set; } public int ProblemSeverity { get; set; } public bool ProblemComplete { get; set; } public ICollection<Result> Result { get; set; } } public class Job { public int ID { get; set; } public string JobTitle { get; set; } public string JobDescription { get; set; } public DateTime JobStartDate { get; set; } public DateTime JobDeadline { get; set; } public bool JobIsComplete { get; set; } public ICollection<Result> Results { get; set; } }

    2.Be sure your DbContext configure like below:

    public class MyDbContext:DbContext
    {
    
        public MyDbContext(DbContextOptions<MyDbContext> options):base(options)
        {
        }
        public DbSet<Result> Results { get; set; }
        public DbSet<Job> Jobs { get; set; }
        public DbSet<Problem> Problems { get; set; }
    
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Result>()
                .HasKey(bc => new { bc.JobID, bc.ProblemID });
            modelBuilder.Entity<Result>()
                .HasOne(bc => bc.Job)
                .WithMany(b => b.Results)
                .HasForeignKey(bc => bc.JobID);
            modelBuilder.Entity<Result>()
                .HasOne(bc => bc.Problem)
                .WithMany(c => c.Result)
                .HasForeignKey(bc => bc.ProblemID);
        }
    }

    3.Your controller:

    var problems = from p in _context.Problems
        .Include(p => p.Result)
            .ThenInclude(j => j.Job)
                    select p;

    4.Your View:

    <td>
        @Html.DisplayFor(modelItem => item.ProblemComplete)
    </td>
    
    <td>
        @foreach (var title in item.Result)
        {
            @Html.DisplayFor(modelItem => title.Job.JobTitle)
            <br />                         
    } </td>

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 27, 2020 8:52 AM
  • User-872746141 posted

    Hi Rena,

    Thanks for your help. I did everything you said and applied the changes in a new migration. I don't get the error anymore but there is no data rendering in the JobTitle field.

    Also I used the SQL Server extension in VS Code and selected all records in tblResult. There is no data there. I don't know what I'm doing wrong from this point on. Was I supposed create a view and controller to create tblResult records manually, please? 

    Kind Regards,

    Jordan

    Friday, May 29, 2020 8:32 AM
  • User711641945 posted

    Hi 287papa,

    It seems what I did resolve your issue for this thread.Please remember to mark as answer.

    287papa

    but there is no data rendering in the JobTitle field.

    Also I used the SQL Server extension in VS Code and selected all records in tblResult. There is no data there.

    Not sure what do you mean for tblResult. Does it the table in database?I suggest that you could post a new thread and explain to us.And be sure it actually has data for JobTitle in your Job table.

    Best Regards,

    Rena

    Monday, June 1, 2020 5:30 AM
  • User-872746141 posted

    Rena Ni

    Not sure what do you mean for tblResult. Does it the table in database?I

    Yes tblResult means the table in the SQL Server database, generated in EF Core. Firstly I will add the context class (should have done it before I know) and an ERD diagram just to be clear to show what it looks like currently.

    public class TeamContext : DbContext
        {
            public TeamContext(DbContextOptions<TeamContext> options) : base(options)
            {
            }
    
            public DbSet<User> Users { get; set; }
            public DbSet<Registration> Registrations {get;set;}
            public DbSet<Job> Jobs {get;set;}     
    
            public DbSet<Problem> Problems { get; set; }   
    
            public DbSet<Result> Results {get;set;}
            
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<User>().ToTable("tblUser");
                modelBuilder.Entity<Registration>().ToTable("tblRegistration");
                modelBuilder.Entity<Job>().ToTable("tblJob");
                modelBuilder.Entity<Problem>().ToTable("tblProblem");
                modelBuilder.Entity<Chat>().ToTable("tblChat");
                modelBuilder.Entity<Result>().ToTable("tblResult");
    
                modelBuilder.Entity<Result>()
                    .HasKey(bc => new { bc.JobID, bc.ProblemID });
                modelBuilder.Entity<Result>()
                    .HasOne(bc => bc.Job)
                    .WithMany(b => b.Results)
                    .HasForeignKey(bc => bc.JobID);
                modelBuilder.Entity<Result>()
                    .HasOne(bc => bc.Problem)
                    .WithMany(c => c.Result)
                    .HasForeignKey(bc => bc.ProblemID);
            }        
        }

    ERD and the way the app works now

    Anyway so I decided to manually insert in a query window into tblResult the JobID matching the JobTitle I wanted with the ProblemID. It has now rendered the correct JobTitle in the Problem Index view record which is the value of  "Pitcher". But that's only because I manually inserted it in tblResult which doesn't really help the end user. I'm wondering what's a way of them getting past that manual insertion.

    Kind regards,

    Jordan Nash

    Monday, June 1, 2020 9:01 AM