EF Core unable to return data from multiple tables

Question

I am working with EF Core. I have a table structure like this:

public class User
{
    public User()
    {
        this.Projects = new HashSet<Project>();
    }

    [Key]
    public int Id { get; set; }
    public string name { get; set; }
    public string emailId { get; set; }

    public virtual ICollection<Project> Projects { get; set;}
}

public class Project
{   
    public Project()
    {
        this.TimeSheetData = new HashSet<TimeSheetData>();
    }

    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public int userId { get; set; }

    [ForeignKey("userId")]
    public virtual User User {get; set; }
    public virtual ICollection<TimeSheetData> TimeSheetData { get; set;}
}

public class TimeSheetData
{
    [Key]
    public int id { get; set; }
    public int project_id { get; set; }
    [ForeignKey("project_id")]
    public virtual Project Project {get; set; }
    public string hours_logged { get; set; }
}

------------

public List<User> GetTimeSheet(int userid)
{
    var data = _context.Users.Include(u => u.Projects)
                             .ThenInclude(p => p.TimeSheetData)
                             .AsNoTracking()
                             .Where(a => a.Id == userid)
                             .ToList();
    return data;
}

Which returns:

[
    {
        "id": 101,
        "name": "Niranjan",
        "emailId": "[email protected]",
        "projects": [
            {
                "id": 1,
                "name": "Niranjan",
                "userId": 101

This object does not include timesheetdata. But when I debug my data returned from query shows all the data. So Do I need to change my user table to accommodate timesheet data but user table contains projects which in turn contains timesheet.

Can someone help me to figure it out?

Did you verify in database whether project Id#1 has Timesheetdata? Asking because, may be in debug, you might have noticed Timesheetdata for different project ids. Are you getting any exception which your app might be ignoring? Also, could you just grab _context.Users.Include(u => u.Projects) .ThenInclude(p => p.TimeSheetData) .AsNoTracking() .Where(a => a.Id == userid) into a variable and see what query is being executed. Execute the same query in database and post your observations here please. — sam
– sam, Commented Jan 23, 2020 at 6:04

Ryan · Accepted Answer · 2020-01-23 08:27:44Z

1

You may come across circular reference between User and Projects.

To prevent Reference Looping ,you could use below code in startup ConfigureServices:

For asp.net core 2.2:

services.AddMvc()
    .AddJsonOptions(
        options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
);

For asp.net core 3.0 (MVC/Web API), just follow below steps to overcome circular reference using NewtonsoftJson.

1.Install Microsoft.AspNetCore.Mvc.NewtonsoftJson package(version depends on your project)

Install-Package Microsoft.AspNetCore.Mvc.NewtonsoftJson -Version 3.0.0

2.Add below code in startup.cs

services.AddControllersWithViews().AddNewtonsoftJson(x =>
        {
            x.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
        });

answered Jan 23, 2020 at 8:27

Ryan

20.3k10 gold badges44 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EF Core unable to return data from multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related