TheLazy.Dev
Portfolio GitHub

Blog

aspnetcore |entityframeworkcore

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

masanori_msl

Masui Masanori

Posted on July 23, 2020

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

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; }
    }
}
Enter fullscreen mode Exit fullscreen mode

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";
Enter fullscreen mode Exit fullscreen mode

"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; }
    }
}
Enter fullscreen mode Exit fullscreen mode

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();
         }
    }
}
Enter fullscreen mode Exit fullscreen mode

SearchedWorkflow.cs 

namespace Workflow
{
    public class SearchedWorkflow
    {
        public Models.Workflow Workflow { get; set; }
        public int ReaderCount { get; set; }
    }
}
Enter fullscreen mode Exit fullscreen mode

IWorkflowService.cs 

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

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

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());
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

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
  },
...
Enter fullscreen mode Exit fullscreen mode

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());
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

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)
...
Enter fullscreen mode Exit fullscreen mode

Because "Workflow" data like this.

Workflow
    Ｌ Readers
        Ｌ Workflow
            Ｌ Readers
                Ｌ ...
Enter fullscreen mode Exit fullscreen mode

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();
            });
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

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"
              }
            },
...
Enter fullscreen mode Exit fullscreen mode

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>();
        }
...
Enter fullscreen mode Exit fullscreen mode

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"
        },
...
Enter fullscreen mode Exit fullscreen mode

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; }
    }
}
Enter fullscreen mode Exit fullscreen mode

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

💖 💪 🙅 🚩
masanori_msl
Masui Masanori

Posted on July 23, 2020

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

[ASP.NET Core][Entity Framework Core] Try System.Text.Json
csharp [ASP.NET Core][Entity Framework Core] Try System.Text.Json

March 8, 2022

[ASP.NET Core][EntityFramework Core] Update from .NET 5 to .NET 6
dotnet [ASP.NET Core][EntityFramework Core] Update from .NET 5 to .NET 6

December 6, 2021

[ASP.NET Core][Entity Framework Core] Try concurrency
csharp [ASP.NET Core][Entity Framework Core] Try concurrency

May 2, 2021

Command-Line Scaffolding for Syncfusion Blazor and ASP.NET Core Components: A Time-Saving Essential
scaffolding Command-Line Scaffolding for Syncfusion Blazor and ASP.NET Core Components: A Time-Saving Essential

July 29, 2020

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

July 23, 2020