C#后端GET请求抛出System.NotSupportedException错误求助

C#新手在对接本地SQL Server实现GET/POST操作时，执行GET请求获取数据时抛出序列化错误：

System.NotSupportedException: 不支持'System.Type'实例的序列化和反序列化。路径: $.Columns.DataType.
---> System.NotSupportedException: 不支持'System.Type'实例的序列化和反序列化。
at System.Text.Json.Serialization.Converters.UnsupportedTypeConverter1.Write(Utf8JsonWriter writer, T value, JsonSerializerOptions options) at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
...（省略后续栈信息）

请求头信息：

Accept: /
Connection: keep-alive
Host: localhost:5291
User-Agent: PostmanRuntime/7.29.2
Accept-Encoding: gzip, deflate, br
Postman-Token: ad516da9-02bb-424f-8979-64365d710b2c

控制器代码 #

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using webApi.Models;
using Microsoft.AspNetCore.Hosting;
using System.IO;

namespace webApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ContactController : ControllerBase
    {
        private readonly IConfiguration _configuration;
        private readonly IWebHostEnvironment _env;
        public ContactController(IConfiguration configuration, IWebHostEnvironment env)
        {
            _configuration = configuration;
            _env = env;
        }

        [HttpGet]
        public JsonResult Get()
        {
            string query = @"select ContactName from dbo.Contact";

            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("ContactAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);
                    myReader.Close();
                    myCon.Close();
                }
            }

            return new JsonResult(table);
        }
    }
}

模型代码 #

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

namespace webApi.Models
{
    public class Contact
    {
        public int ContactID { get; set; }

        public string ContactName { get; set; }

        public string ContactEmail { get; set; }

        public string ContactSubject { get; set; }

        public string ContactMessage { get; set; }
    }
}

Startup.cs代码 #

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Newtonsoft.Json.Serialization;
using Microsoft.Extensions.FileProviders;
using System.IO;

namespace WebApplication1
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            //Enable CORS
            services.AddCors(c =>
            {
                c.AddPolicy("AllowOrigin", options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
            });

            //JSON Serializer
            _ = services.AddControllersWithViews().AddNewtonsoftJson(options =>
            options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore)
                .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver
                = new DefaultContractResolver());

            services.AddControllers();
        }

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            //Enable CORS
            app.UseCors(options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });

            app.UseStaticFiles(new StaticFileOptions
            {
                FileProvider = new PhysicalFileProvider(
                   Path.Combine(Directory.GetCurrentDirectory(), "Photos")),
                RequestPath = "/Photos"
            });
        }
    }
}

错误原因 #

1. 序列化类型不支持：直接返回DataTable时，其内部Columns集合的DataType属性为System.Type类型，ASP.NET Core默认的System.Text.Json序列化器不支持序列化该类型实例，触发错误。
2. 序列化配置无效：Startup中虽配置了AddControllersWithViews().AddNewtonsoftJson()，但后续调用的services.AddControllers()会覆盖之前的配置，最终仍使用默认的System.Text.Json进行序列化。

解决方法 #

方法一：修正序列化配置，使用Newtonsoft.Json #

修改Startup.cs的ConfigureServices方法，确保控制器统一使用NewtonsoftJson：

public void ConfigureServices(IServiceCollection services)
{
    //Enable CORS
    services.AddCors(c =>
    {
        c.AddPolicy("AllowOrigin", options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
    });

    // 统一配置控制器使用NewtonsoftJson
    services.AddControllers()
        .AddNewtonsoftJson(options =>
        {
            options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
            options.SerializerSettings.ContractResolver = new DefaultContractResolver();
        });
}

方法二：返回强类型模型（推荐） #

直接返回DataTable不符合RESTful API规范，建议将查询结果映射到Contact模型，返回强类型集合：
修改控制器的Get方法：

[HttpGet]
public IEnumerable<Contact> Get()
{
    List<Contact> contacts = new List<Contact>();
    string query = @"select ContactName from dbo.Contact";
    string sqlDataSource = _configuration.GetConnectionString("ContactAppCon");
    
    using (SqlConnection myCon = new SqlConnection(sqlDataSource))
    {
        myCon.Open();
        using (SqlCommand myCommand = new SqlCommand(query, myCon))
        {
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                while (myReader.Read())
                {
                    contacts.Add(new Contact
                    {
                        ContactName = myReader.GetString(myReader.GetOrdinal("ContactName"))
                    });
                }
            }
        }
    }

    return contacts;
}

这种方式既避免了序列化问题，又让API返回的数据结构更清晰，符合面向对象编程思想。

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