[ASP.NET Core][Entity Framework Core] Errors for inserting and reading
Tags: aspnetcore, entityframeworkcore
Published: 2020-07-23 06:44:03

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

* [[ASP.NET Core][Entity Framework Core] Try code first & DB first](https://dev.to/masanori_msl/asp-net-core-entity-framework-core-npgsql-try-code-first-db-first-32pj)

### 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
```csharp
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.
``` sql
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.<br>
(Because "Id"'s type of database is "serial". So its default value is auto incremented number)

So I add the default value.

#### CodeFirstSampleContext.cs
```csharp
...
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
* [Generated Values - EF Core | Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations)
* [Entity Framework Core code first default values for PostgreSQL - Stack Overflow](https://stackoverflow.com/questions/53298033/entity-framework-core-code-first-default-values-for-postgresql)

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

#### WorkflowController.cs
```csharp
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
```csharp
namespace Workflow
{
    public class SearchedWorkflow
    {
        public Models.Workflow Workflow { get; set; }
        public int ReaderCount { get; set; }
    }
}
```
#### IWorkflowService.cs
```csharp
using System.Collections.Generic;
using System.Threading.Tasks;
using UploadResults;

namespace Workflow
{
    public interface IWorkflowService
    {
        Task<IList<SearchedWorkflow>> GetAsync();
    }
}
```
#### WorkflowService.cs
```csharp
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.
```json
[
  {
    "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?<br>
Although I set "HasMany" and "HasOne" in DbContext, the childrens won't be loaded automatically.

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

#### WorkflowService.cs
```csharp
...
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
    Ｌ Readers
        Ｌ Workflow
            Ｌ Readers
                Ｌ ...
```
There are some solutions.

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

#### Startup.cs
```csharp
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.

```json
{
  "$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
```csharp
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.
```json
[
  {
    "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
```csharp
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
* [Loading Related Data - EF Core | Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/querying/related-data)
* [Fixing JSON Self Referencing Loop Exceptions - .NET Core Tutorials](https://dotnetcoretutorials.com/2020/03/15/fixing-json-self-referencing-loop-exceptions/)
* [Using Newtonsoft.Json In .NET Core 3+ Projects - .NET Core Tutorials](https://dotnetcoretutorials.com/2019/12/19/using-newtonsoft-json-in-net-core-3-projects/)
* [System.Text.Json Reference Loop Handling · Issue #29900 · dotnet/runtime · GitHub](https://github.com/dotnet/runtime/issues/29900)