ASP.NET Core Web API Not Returning List of Child Items

Question

I am creating a ASP.NET Core web API that uses EF Core. I have a GET endpoint that returns a list of reports from the database. I have a related table which stores screenshots for the reports. The reportId is the foreign key in the images table.

I have a List item in the reports class which points to the ImagesList class. I have the foreign key reportId in the ImageList class and identified as a foreign key. I also have a navigation property setup to the Reports class.

Reports Class:

[Table("Vw_ReportsList", Schema = "pbi")]

public class Reports
{
    [Key]
    public string reportId { get; set; }
    [Required]
    public string reportName { get; set; }

    public string reportDescription { get; set; }

    public string reportType { get; set; }

    public string reportAuthor { get; set; }

    public string reportLastUpdate { get; set; }

    public string reportLastExecution { get; set; }

    public List<ImagesList> Screenshots { get; set; }
    //collection navigation property

}

ImageList Class:

[Table("Vw_ScreenshotsList", Schema = "pbi")]

public class ImagesList
{
    [Key]
    public int id { get; set; }
    public string fileNameTest { get; set; }
    public string imageData { get; set; }
    public string created { get; set; }
    public string reportId { get; set; }

    [ForeignKey("reportId")]
    public virtual Reports Reports { get; set; }
    //navigation property
}

Context:

public class ServiceCatalogContext : DbContext
{

    public ServiceCatalogContext(DbContextOptions<ServiceCatalogContext> options) : base(options) { }

    public DbSet<Reports> Reports { get; set; }
    public DbSet<ImagesList> ImagesLists { get; set; }

    public DbSet<Images> Images { get; set; }
    //used for the image upload POST call


    protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
         // modelBuilder.Entity<ImagesList>().HasOne<Reports>().WithMany().HasForeignKey(s => s.reportId);
        modelBuilder.Entity<ImagesList>().HasOne(s => s.Reports).WithMany(s => s.Screenshots).HasForeignKey(s => s.reportId);
        modelBuilder.Entity<Reports>().HasMany(r => r.Screenshots).WithOne().HasForeignKey(r => r.reportId);
    }        
}

My API works and returns the list of reports with no errors but I do not receive the screenshots list that I am expecting.

Here is a sample of the API output:

{
        "reportId": "AC79F4CD-3771-42B2-B7F8-46AE4CE8DC80",
        "reportName": "Dashboard Usage Metrics Report",
        "reportDescription": "DESCRIPTION HERE - Dashboard Usage Metrics Report",
        "reportType": "Excel",
        "reportLastUpdate": "07/22/2020",
        "reportLastExecution": "07/23/2020"
    },
    {
        "reportId": "138CD5FA-6B5A-4C63-A449-DA9A9BBBF689",
        "reportName": "Report Usage Metrics Report",
        "reportDescription": "DESCRIPTION HERE - Report Usage Metrics Report",
        "reportType": "Excel",
        "reportLastUpdate": "07/22/2020",
        "reportLastExecution": "07/23/2020"
    }

I not receiving any error message from the API so I am not sure what I missed in order for each report to return the related images.

Edit: Adding Controller action

    [HttpGet]
    [EnableQuery()] //enabled OData querying
    public IQueryable<Reports> Get()
    {
        return _context.Reports;        

    }

Edit: Updated ImagesList class

I also have Odata installed so here the metadata if that is of help:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:DataServices>
        <Schema Namespace="ServiceCatalog.API.Entities" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityType Name="Reports">
                <Key>
                    <PropertyRef Name="reportId" />
                </Key>
                <Property Name="reportId" Type="Edm.String" Nullable="false" />
                <Property Name="reportName" Type="Edm.String" Nullable="false" />
                <Property Name="reportDescription" Type="Edm.String" />
                <Property Name="reportType" Type="Edm.String" />
                <Property Name="reportLastUpdate" Type="Edm.String" />
                <Property Name="reportLastExecution" Type="Edm.String" />
                <NavigationProperty Name="Screenshots" Type="Collection(ServiceCatalog.API.Entities.ImagesList)" />
            </EntityType>
            <EntityType Name="ImagesList">
                <Key>
                    <PropertyRef Name="id" />
                </Key>
                <Property Name="id" Type="Edm.Int32" Nullable="false" />
                <Property Name="fileNameTest" Type="Edm.String" />
                <Property Name="imageData" Type="Edm.String" />
                <Property Name="created" Type="Edm.String" />
                <Property Name="reportId" Type="Edm.String" />
                <NavigationProperty Name="Reports" Type="ServiceCatalog.API.Entities.Reports">
                    <ReferentialConstraint Property="reportId" ReferencedProperty="reportId" />
                </NavigationProperty>
            </EntityType>
        </Schema>
        <Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityContainer Name="Container">
                <EntitySet Name="reports" EntityType="ServiceCatalog.API.Entities.Reports" />
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Where's the action code? What you posted doesn't generate any output. Most likely your action is using a LINQ query without .Include<> — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 23, 2020 at 15:15
@PanagiotisKanavos - I updated the question with my controller action. — Tom
– Tom, Commented Jul 23, 2020 at 19:39
@PanagiotisKanavos - I updated the return line to: return _context.Reports.Include(r => r.Screenshots); When I do I get the following error: Invalid column name 'ReportsreportId1'. This column name is not in my code. Appears to the be the model name + primary key field + 1 — Tom
– Tom, Commented Jul 23, 2020 at 19:40
I updated the override OnModelCreating updating the modelBuilder for ImagesList and while I get the same error instead of invalid column being ReportsreportId1 it is ReportsreportId. The 1 at the end is no longer there. Still appears to be trying to create a column using the table and primary key. I am using EF database first. — Tom
– Tom, Commented Jul 23, 2020 at 20:47

Rena · Accepted Answer · 2020-07-24 08:47:06Z

Design your model like below:

[Table("Vw_ReportsList", Schema = "pbi")]

public class Reports
{
    [Key]
    public string reportId { get; set; }
    [Required]
    public string reportName { get; set; }
    public string reportDescription { get; set; }
    public string reportType { get; set; }
    public string reportAuthor { get; set; }
    public string reportLastUpdate { get; set; }
    public string reportLastExecution { get; set; }
    public List<ImagesList> Screenshots { get; set; }
    //collection navigation property
}
[Table("Vw_ScreenshotsList", Schema = "pbi")]
public class ImagesList
{
    [Key]
    public int id { get; set; }
    public string fileNameTest { get; set; }
    public string imageData { get; set; }
    public string created { get; set; }
    public string reportId { get; set; }

   // [ForeignKey("reportId")]
    public virtual Reports Reports { get; set; }
    //navigation property
}

And your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ImagesList>().HasOne(s => s.Reports)
                .WithMany(s => s.Screenshots).HasForeignKey(s => s.reportId);
    //modelBuilder.Entity<Reports>().HasMany(r => r.Screenshots).WithOne().HasForeignKey(r => r.reportId);
}

Your controller:

[HttpGet]
public IQueryable<Reports> Get()
{
    return _context.Reports.Include(r=>r.Screenshots);
}

Be sure to install Microsoft.AspNetCore.Mvc.NewtonsoftJson then use the following code:

services.AddControllers().AddNewtonsoftJson(options =>
{
    options.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
});

Collectives™ on Stack Overflow

ASP.NET Core Web API Not Returning List of Child Items

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