向SQL Server发送POST请求时遇标识列插入错误求助

遇到的错误信息 #

前端错误 #

Permission denied to access property "matches"

后端SQL错误 #

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'Authors' when IDENTITY_INSERT is set to OFF.

         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)

         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()

         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

      --- End of stack trace from previous location ---

         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

         at System.Threading.Tasks.Task.Exec…

错误原因及解决方案 #

1. SQL插入错误核心解决 #

这个错误是因为Authors表的主键是自增IDENTITY列，但插入操作时手动传递了主键值，而SQL Server默认禁止手动插入自增列的值（IDENTITY_INSERT为OFF）。

解决步骤：

• 检查实体类配置：确保Authors实体的主键标记为自增（以EF Core为例）：

  using System.ComponentModel.DataAnnotations;
  using System.ComponentModel.DataAnnotations.Schema;
  
  namespace blazorTestApp.Shared.Entities
  {
      public class Authors
      {
          [Key]
          [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
          public int Id { get; set; } // 标记为自增列
  
          // 其他属性示例
          public string Name { get; set; }
      }
  }
  

• 避免前端传递主键值：前端创建Authors实例时，不要给Id赋值（即使默认是0，序列化后也会传递给后端）。可以通过以下方式处理：
  • 前端序列化时忽略Id：修改HttpService的序列化逻辑，添加忽略配置：

    public async Task<HttpResponse<object>> POST<T>(string url, T data)
    {
        var options = new JsonSerializerOptions
        {
            IgnoreReadOnlyProperties = true,
            // 或者针对特定属性，在Authors的Id上标记[JsonIgnore]（注意是否影响其他场景）
        };
        var JsonData = JsonSerializer.Serialize(data, options);
        var StringContent = new StringContent(JsonData, Encoding.UTF8, "application/json");
        var ResponseMessage = await httpClient.PostAsync(url, StringContent);
        return new HttpResponse<object>(null, ResponseMessage, ResponseMessage.IsSuccessStatusCode);
    }
    

  • 后端手动重置主键：在控制器的Post方法中，强制将Author.Id设为0，让EF自动处理自增：

    [HttpPost]
    public async Task<ActionResult<int>> Post(Authors Author)
    {
        Author.Id = 0; // 重置自增列值
        DB.Add(Author);
        await DB.SaveChangesAsync();
        return Ok(Author.Id); // 返回生成的主键
    }
    

2. 前端"Permission denied"错误解决 #

这个错误通常是浏览器安全限制导致，多和响应内容处理、JSON序列化/反序列化逻辑有关。

解决步骤：

• 检查响应处理逻辑：确保HttpResponse的GetBodyString方法正确读取响应内容，避免使用可能触发权限问题的DOM相关方法。示例修改：

  // 假设HttpResponse类的GetBodyString方法实现如下
  public async Task<string> GetBodyString()
  {
      return await ResponseMessage.Content.ReadAsStringAsync();
  }
  

• 确保后端返回合法JSON：控制器返回Ok()时，如果需要返回数据，建议返回具体对象（比如生成的Author Id），避免返回空响应导致前端解析错误。
• 检查CSP配置：如果项目有内容安全策略（CSP），确保允许相关脚本执行，避免限制了JSON处理的相关操作。

相关代码参考 #

控制器 #

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using blazorTestApp.Shared.Entities;
using Microsoft.AspNetCore.Mvc;

namespace blazorTestApp.Server.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class AuthorsController : ControllerBase
    {
        private readonly Database DB;
        public AuthorsController(Database DB)
        {
            this.DB = DB;
        }

        [HttpPost]
        public async Task<ActionResult<int>> Post(Authors Author)
        {
            Author.Id = 0; // 添加此行重置自增列
            DB.Add(Author);
            await DB.SaveChangesAsync();
            return Ok(Author.Id); // 返回生成的主键ID
        }
    }
}

Http服务文件 #

using System.Text.Json;
using System.Text;
using System.Net.Http;
using System.Threading.Tasks;

namespace blazorTestApp.Client.Classes_FE
{
    public class HttpService : IHttpService
    {
        private readonly HttpClient httpClient;
        public HttpService(HttpClient httpClient)
        {
            this.httpClient = httpClient;
        }

        public async Task<HttpResponse<object>> POST<T>(string url, T data)
        {
            var options = new JsonSerializerOptions
            {
                IgnoreReadOnlyProperties = true
            };
            var JsonData = JsonSerializer.Serialize(data, options);
            var StringContent = new StringContent(JsonData, Encoding.UTF8, "application/json");
            var ResponseMessage = await httpClient.PostAsync(url, StringContent);
            return new HttpResponse<object>(null, ResponseMessage, ResponseMessage.IsSuccessStatusCode);
        }
    }
}

发送Http请求的仓储 #

using System.Threading.Tasks;

namespace blazorTestApp.Client.Repositories
{
    public class AuthorsRepo
    {
        private IHttpService httpService;
        private string url = "api/Authors";
        public AuthorsRepo(IHttpService httpService)
        {
            this.httpService = httpService;
        }

        public async Task AddAuthor(Authors Author)
        {
            var response = await httpService.POST(url, Author);
            if(!response.Success)
            {
                throw new ApplicationException(await response.GetBodyString());
            }
        }
    }
}

调用AddAuthor方法的页面 #

@page "/Authors/Add"
@inject AuthorsRepo authorRepo
@inject NavigationManager NavMan

@code
{
    public Authors Author {get; set;} = new Authors();
    public async Task OnSubmit()
    {
        //Unrelated code
        await authorRepo.AddAuthor(Author);
        NavMan.NavigateTo("Authors");
    }
}

内容的提问来源于stack exchange，提问作者Saucter