loading...

[ASP.NET Core][Entity Framework Core] Errors for inserting and reading

masanori_msl profile image Masui Masanori ・5 min read

Intro

When I tried inserting data and search them, I got some errors.
This time I write about them.

Environments

  • .NET 5: ver.5.0.100-preview.7.20366.6
  • Microsoft.EntityFrameworkCore: ver.5.0.0-preview.7.20365.15
  • Microsoft.EntityFrameworkCore.Design: ver.5.0.0-preview.7.20365.15
  • Npgsql.EntityFrameworkCore.PostgreSQL: ver.5.0.0-preview7-ci.20200722t163648
  • Microsoft.EntityFrameworkCore.Abstractions: ver.5.0.0-preview.7.20365.15
  • Microsoft.EntityFrameworkCore.Relational: ver.5.0.0-preview.7.20365.15
  • Microsoft.AspNetCore.Mvc.NewtonsoftJson: ver.5.0.0-preview.7.20365.19

Insert

When I insert a "Workflow", I get an exception of Npgsql.

Models/Workflow.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Models
{
    [Table("Workflow")]
    public class Workflow
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set;}
        [Required]
        public int ProductId { get; set; }
        [Required]
        [Column("CreateUserMail", TypeName="text")]
        public string CreateUserMail { get; set;}
        [Column(TypeName="timestamp with time zone")]
        public DateTime? CirculationLimit { get; set; }
        [Column(TypeName="timestamp with time zone")]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime LastUpdateDate { get; set; }

        public List<WorkflowReader> Readers {get; set; }
    }
}

Because Entity Framework generated an INSERT statement like below.

INSERT INTO "Workflow" ("CirculationLimit", "CreateUserMail", "ModelName", "ProductId", "SizaiCode")
      VALUES (@p4285, @p4286, @p4287, @p4288, @p4289)
      RETURNING "Id", "LastUpdateDate";

"LastUpdateDate" has no value. But it also doesn't have a default value.

(Because "Id"'s type of database is "serial". So its default value is auto incremented number)

So I add the default value.

CodeFirstSampleContext.cs

...
namespace Models
{
    public class CodeFirstSampleContext: DbContext
    {
 ...
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Workflow>()
                .Property(w => w.LastUpdateDate)
                .HasDefaultValueSql("CURRENT_TIMESTAMP");
            modelBuilder.Entity<Workflow>()
                .HasMany(w => w.Readers);
            modelBuilder.Entity<WorkflowReader>()
                .HasOne(b => b.Workflow);
        }
        public DbSet<Workflow> Workflows { get; set; }
        public DbSet<WorkflowReader> WorkflowReaders { get; set; }
    }
}

Thus, I create a migration file and update database.
After that, I can insert "Workflow".

Resources

Search

I search "Workflow" data and create data like this for client side.

WorkflowController.cs

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Workflow;

namespace Controllers
{
    public class WorkflowController: Controller
    {
        private readonly IWorkflowService _workflow;
        public WorkflowController(IWorkflowService workflow)
        {
            _workflow = workflow;
        }
         [Route("/Workflow/Search")]
         public async Task<IList<SearchedWorkflow>> Search()
         {
             return await _workflow.GetAsync();
         }
    }
}

SearchedWorkflow.cs

namespace Workflow
{
    public class SearchedWorkflow
    {
        public Models.Workflow Workflow { get; set; }
        public int ReaderCount { get; set; }
    }
}

IWorkflowService.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using UploadResults;

namespace Workflow
{
    public interface IWorkflowService
    {
        Task<IList<SearchedWorkflow>> GetAsync();
    }
}

WorkflowService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Models;
using Npgsql;
using UploadResults;

namespace Workflow
{
    public class WorkflowService: IWorkflowService
    {
        private readonly CodeFirstSampleContext _context;
        public WorkflowService(CodeFirstSampleContext context)
        {
            _context = context;
        }
        public async Task<IList<SearchedWorkflow>> GetAsync()
        {            
            return await Task.FromResult(
                _context.Workflows.Where(expression)
                    .Select(w => new SearchedWorkflow
                    {
                        Workflow = w,
                        ReaderCount = w.Readers.Count
                    })
                    .ToList());
        }
    }
}

The result like this.

[
  {
    "Workflow": {
      "Id": 15730,
      "ProductId": 0,
      "CreateUserMail": "example@exam.ple",
      "CirculationLimit": null,
      "LastUpdateDate": "2020-07-21T19:27:41.825655+09:00",
      "Readers": null
    },
    "ProofreaderCount": 100
  },
...

Why "Readers" are null?

Although I set "HasMany" and "HasOne" in DbContext, the childrens won't be loaded automatically.

To do that, I can use "include()".

WorkflowService.cs

...
namespace Workflow
{
    public class WorkflowService: IWorkflowService
    {
...
        public async Task<IList<SearchedWorkflow>> GetAsync()
        {            
            return await Task.FromResult(
                _context.Workflows.Where(expression)
                    .Include()
                    .Select(w => new SearchedWorkflow
                    {
                        Workflow = w,
                        ReaderCount = w.Readers.Count
                    })
                    .ToList());
        }
    }
}

But I get a JsonException.

An unhandled exception occurred while processing the request.
JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32. 
Consider using ReferenceHandler.Preserve on JsonSerializerOptions to support cycles.
System.Text.Json.ThrowHelper.ThrowJsonException_SerializerCycleDetected(int maxDepth)

Stack Query Cookies Headers Routing
JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32. Consider using ReferenceHandler.Preserve on JsonSerializerOptions to support cycles.
System.Text.Json.ThrowHelper.ThrowJsonException_SerializerCycleDetected(int maxDepth)
System.Text.Json.Serialization.JsonConverter<T>.TryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, ref WriteStack state)
...

Because "Workflow" data like this.

Workflow
    L Readers
        L Workflow
            L Readers
                L ...

There are some solutions.

ReferenceHandler.Preserve

According to the messages, I can use "ReferenceHandler.Preserve".

Startup.cs

using System.Text.Json.Serialization;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Models;
using Workflow;

namespace Asp5Sample
{
    public class Startup
    {
        private IConfigurationRoot configuration { get; }
        public Startup(IHostEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", false, true)
                .AddJsonFile($"appsettings.{env.EnvironmentName}.json", false, true)
                .AddEnvironmentVariables();
            configuration = builder.Build();
        }
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers()
                .AddJsonOptions(options =>
                    {
                        options.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.Preserve;
                    });
            services.AddDbContext<ProofreadingWorkflowContext>(options =>
                options.UseNpgsql(configuration["ConnectionStrings"]));
            services.AddScoped<IWorkflowService, WorkflowService>();
        }
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseRouting();
            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

Though I can avoid the exception, but I have another problem.
The generated JSON like below.

{
  "$id": "1",
  "$values": [
    {
      "$id": "2",
      "workflow": {
        "$id": "3",
        "id": 15730,
        "productId": 0,
        "createUserMail": "example@exam.ple",
        "circulationLimit": null,
        "lastUpdateDate": "2020-07-21T19:27:41.825655+09:00",
        "readers": {
          "$id": "4",
          "$values": [
            {
              "$id": "5",
              "id": 656743,
              "workflowId": 15730,
              "name": "Reader: 79",
              "workflow": {
                "$ref": "3"
              }
            },
...

The data stractures are changed :(

I will learn about "ReferenceHandler.Preserve".

Use Newtonsoft.Json

From ASP.NET Core 3.0, the default JSON library has been "System.Text.Json".
But it doesn't have resolving this "Self Referencing Loop".

And "Newtonsoft.Json" can resolve. So I can install "Microsoft.AspNetCore.Mvc.NewtonsoftJson".

To use "Newtonsoft.Json", I have to add into controller.

Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Newtonsoft.Json;
using Models;
using Workflow;

namespace Asp5Sample
{
    public class Startup
    {
...
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers()
                .AddNewtonsoftJson(options =>
                    options.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore);
            services.AddDbContext<ProofreadingWorkflowContext>(options =>
                options.UseNpgsql(configuration["ConnectionStrings"]));
            services.AddScoped<IWorkflowService, WorkflowService>();
        }
...

This is result.

[
  {
    "workflow": {
      "id": 15730,
      "productId": 0,
      "createUserMail": "example@exam.ple",
      "circulationLimit": null,
      "lastUpdateDate": "2020-07-21T19:27:41.825655+09:00",
      "readers": [
        {
          "id": 656743,
          "workflowId": 15730,
          "name": "Reader: 79"
        },
        {
          "id": 656744,
          "workflowId": 15730,
          "name": "Reader: 78"
        },
...

Use [JsonIgnore]

If the child class doesn't need the parent class instance, I also can use "[JsonIgnore]".

WorkflowReaders.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using System.Text.Json.Serialization;

namespace Models
{
    [Index(nameof(WorkflowId))]
    public partial class WorkflowReaders
    {
        [Key]
        public int Id { get; set; }
        public int WorkflowId { get; set; }
        [Required]
        public string Name{ get; set; }
        [ForeignKey(nameof(WorkflowId))]
        [InverseProperty("WorkflowReaders")]
        [JsonIgnore]
        public virtual Workflow Workflow { get; set; }
    }
}

The result is same as using "Newtonsoft.Json".

Whitch one should I use?

Although I don't want to install duplicate libraries for same purpose, I should use "Newtonsoft.Json" now.
Because I'm afraid I forget add "[JsonIgnore]" into all child classes.

Resources

Posted on by:

masanori_msl profile

Masui Masanori

@masanori_msl

Programmer, husband, father I love C#, TypeScript, etc.

Discussion

pic
Editor guide