asp.net restful ef core sqlite 自定义包的位置

MagicVilla_VillaAPI/MagicVilla_VillaAPI.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net7.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <RestorePackagesPath>..\packages</RestorePackagesPath>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.JsonPatch" Version="7.0.0-preview.5.22303.8" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc.NewtonsoftJson" Version="7.0.0-preview.5.22303.8" />
    <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="7.0.0-preview.5.22303.8" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.10" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.10">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.10" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.0-preview.5.22302.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="7.0.10">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="SQLite.CodeFirst" Version="1.7.0.34" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />
  </ItemGroup>

</Project>

MagicVilla_VillaAPI/appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultSQLConnection": "Data Source=MagicVilla.db"
  }
}

MagicVilla_VillaAPI/appsettings.Development.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  }
}

MagicVilla_VillaAPI/Program.cs

using MagicVilla_VillaAPI.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);


// Add services to the container.
builder.Services.AddDbContext<ApplicationDbContext>(option =>
{
    // sqlite数据库的使用步骤
    // 0. 安装ef core中关于sqlite的依赖包  dotnet add package Microsoft.EntityFrameworkCore.Sqlite
    // 1. 需要删掉Migrations文件夹下所有的迁移文件 
    // 2. 然后重新生成迁移文件  dotnet ef migrations add <自定义的名称>
    //      出现错误: Method 'Fragment' in type 'Microsoft.EntityFrameworkCore.Design.Internal.CSharpHelper' from assembly 'Microsoft.EntityFrameworkCore.Design, Version=7.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60' does not have an implementation.
    //      这个错误的原因是你的 `Microsoft.EntityFrameworkCore.Design` 包的版本和你的 `Microsoft.EntityFrameworkCore` 包的版本不一致，导致了类型加载异常¹。你需要将这两个包的版本统一，最好都升级到最新的版本²。你可以使用以下命令来更新这两个包：
    //      也可能是没有安装
    //      ```bash
    //      dotnet add package Microsoft.EntityFrameworkCore --version 7.0.10
    //      dotnet add package Microsoft.EntityFrameworkCore.Design --version 7.0.10
    //      ```
    // 3. 进而重新创建数据表    dotnet ef database update
    option.UseSqlite(builder.Configuration.GetConnectionString("DefaultSQLConnection"));
});


builder.Services.AddControllers(option =>
{
    //option.ReturnHttpNotAcceptable=true;
}).AddNewtonsoftJson();

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

MagicVilla_VillaAPI/Models/Villa.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using SQLite.CodeFirst;

namespace MagicVilla_VillaAPI.Models
{
    public class Villa
    {
        // 主键,即使不加上[Key],默认也是主键
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Details { get; set; }
        public double Rate { get; set; }
        public int Sqft { get; set; }
        public int Occupancy { get; set; }
        public string ImageUrl { get; set; }
        public string Amenity { get; set; }

        public DateTime CreatedDate { get; set; }

        public DateTime UpdatedDate { get; set; }


    }
}

MagicVilla_VillaAPI/Models/Dto/VillaDTO.cs

using System.ComponentModel.DataAnnotations;

namespace MagicVilla_VillaAPI.Models.Dto
{
    public class VillaDTO
    {
        public int Id { get; set; }
        [Required]
        [MaxLength(30)]
        public string Name { get; set; }
        public string Details { get; set; }
        [Required]
        public double Rate { get; set; }
        public int Occupancy { get; set; }
        public int Sqft { get; set; }
        public string ImageUrl { get; set; }
        public string Amenity { get; set; }
    }
}

MagicVilla_VillaAPI/Models/Dto/VillaCreateDTO.cs

using System.ComponentModel.DataAnnotations;

namespace MagicVilla_VillaAPI.Models.Dto
{
    public class VillaCreateDTO
    {
        [Required]
        [MaxLength(30)]
        public string Name { get; set; }
        public string Details { get; set; }
        [Required]
        public double Rate { get; set; }
        public int Occupancy { get; set; }
        public int Sqft { get; set; }
        public string ImageUrl { get; set; }
        public string Amenity { get; set; }
    }
}

MagicVilla_VillaAPI/Models/Dto/VillaUpdateDTO.cs

using System.ComponentModel.DataAnnotations;

namespace MagicVilla_VillaAPI.Models.Dto
{
    public class VillaUpdateDTO
    {
        [Required]
        public int Id { get; set; }
        [Required]
        [MaxLength(30)]
        public string Name { get; set; }
        public string Details { get; set; }
        [Required]
        public double Rate { get; set; }
        [Required]
        public int Occupancy { get; set; }
        [Required]
        public int Sqft { get; set; }
        [Required]
        public string ImageUrl { get; set; }
        public string Amenity { get; set; }
    }
}

MagicVilla_VillaAPI/Controllers/VillaAPIController.cs

using MagicVilla_VillaAPI.Data;
using MagicVilla_VillaAPI.Models;
using MagicVilla_VillaAPI.Models.Dto;
using Microsoft.AspNetCore.JsonPatch;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace MagicVilla_VillaAPI.Controllers
{
    // 路由的名称回去控制类的前面的名称作为路径,保留VillaAPI,去掉Controller,也就是 api/VillaAPI
    //[Route("api/[controller]")]

    // 当然,也可以写死,写死的好处就是当我修改这个类的名称时候,不用考虑会影响路由路径
    [Route("api/VillaAPI")]
    [ApiController]
    public class VillaAPIController : ControllerBase
    {
        private readonly ApplicationDbContext _db;

        // 依赖注入数据库上下文
        public VillaAPIController(ApplicationDbContext db)
        {
            _db = db;
        }

        [HttpGet]
        [ProducesResponseType(StatusCodes.Status200OK)]
        public ActionResult<IEnumerable<VillaDTO>> GetVillas()
        {
            return Ok(_db.Villas.ToList());
        }

        [HttpGet("{id:int}", Name = "GetVilla")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status404NotFound)]
        public ActionResult<VillaDTO> GetVilla(int id)
        {
            if (id == 0)
            {
                return BadRequest();
            }
            var villa = _db.Villas.FirstOrDefault(u => u.Id == id);
            if (villa == null)
            {
                return NotFound();
            }

            return Ok(villa);
        }

        [HttpPost]
        [ProducesResponseType(StatusCodes.Status201Created)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public ActionResult<VillaDTO> CreateVilla([FromBody] VillaCreateDTO villaDTO)
        {

            if (_db.Villas.FirstOrDefault(u => u.Name.ToLower() == villaDTO.Name.ToLower()) != null)
            {
                ModelState.AddModelError("CustomError", "Villa already Exists!");
                return BadRequest(ModelState);
            }

            if (villaDTO == null)
            {
                return BadRequest(villaDTO);
            }

            Villa model = new()
            {
                Amenity = villaDTO.Amenity,
                Details = villaDTO.Details,
                ImageUrl = villaDTO.ImageUrl,
                Name = villaDTO.Name,
                Occupancy = villaDTO.Occupancy,
                Rate = villaDTO.Rate,
                Sqft = villaDTO.Sqft,
                CreatedDate = DateTime.Now,
                UpdatedDate = DateTime.Now
            };

            _db.Villas.Add(model);
            _db.SaveChanges();

            // 会在response的head中增加一个location地址,指向新添加的地址
            // https://www.cnblogs.com/zhuoss/p/17674336.html
            return CreatedAtRoute("GetVilla", new { id = model.Id }, model);
            // return Ok(villaDTO);
        }

        [ProducesResponseType(StatusCodes.Status204NoContent)]
        [ProducesResponseType(StatusCodes.Status404NotFound)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [HttpDelete("{id:int}", Name = "DeleteVilla")]
        public IActionResult DeleteVilla(int id)
        {
            if (id == 0)
            {
                return BadRequest();
            }
            var villa = _db.Villas.FirstOrDefault(u => u.Id == id);
            if (villa == null)
            {
                return NotFound();
            }
            _db.Villas.Remove(villa);
            _db.SaveChanges();
            return NoContent();

        }

        [HttpPut("{id:int}", Name = "UpdateVilla")]
        [ProducesResponseType(StatusCodes.Status204NoContent)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        public IActionResult UpdateVilla(int id, [FromBody] VillaUpdateDTO villaDTO)
        {
            if (villaDTO == null || id != villaDTO.Id)
            {
                return BadRequest();
            }

            // ef core很智能的是,你不需要先去根据id查找响应的数据,然后在修改,直接赋值,然后updade
            Villa model = new()
            {
                Amenity = villaDTO.Amenity,
                Details = villaDTO.Details,
                Id = villaDTO.Id,
                ImageUrl = villaDTO.ImageUrl,
                Name = villaDTO.Name,
                Occupancy = villaDTO.Occupancy,
                Rate = villaDTO.Rate,
                Sqft = villaDTO.Sqft,
                // 算了,本来想设置UpdatedDate这个属性自动更新的,但是sqlite好像有个时区问题,不好弄,手动更新了,额
                UpdatedDate = DateTime.Now
            };

            // 直接update  
            _db.Villas.Update(model);
            _db.SaveChanges();
            return NoContent();

        }


        // AsNoTracking(),很关键,ef core一次只能跟踪一个模型

        [HttpPatch("{id:int}", Name = "UpdatePartialVilla")]
        [ProducesResponseType(StatusCodes.Status204NoContent)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        public IActionResult UpdatePartialVilla(int id, JsonPatchDocument<VillaUpdateDTO> patchDTO)
        {
            if (patchDTO == null || id == 0)
            {
                return BadRequest();
            }

            // - `AsNoTracking` 是一个扩展方法（Extension Method），表示在查询时不跟踪（Track）实体的状态（State），也就是说，不将实体添加到上下文中的变化跟踪器（Change Tracker）中。这样做的原因是：
            //     - 提高性能：不跟踪实体可以减少内存消耗和查询时间，因为不需要为每个实体创建快照（Snapshot）和检查变化。
            //     - 避免冲突：不跟踪实体可以避免在更新或删除时出现冲突或异常，因为不会有多个实体引用同一个数据库行。下面会更新数据库
            var villa = _db.Villas.AsNoTracking().FirstOrDefault(u => u.Id == id);


            VillaUpdateDTO villaDTO = new()
            {
                Amenity = villa.Amenity,
                Details = villa.Details,
                Id = villa.Id,
                ImageUrl = villa.ImageUrl,
                Name = villa.Name,
                Occupancy = villa.Occupancy,
                Rate = villa.Rate,
                Sqft = villa.Sqft,
            };


            if (villa == null)
            {
                return BadRequest();
            }




            // - `patchDTO` 是一个 `JsonPatchDocument < VillaUpdateDTO >` 类型的参数，表示客户端发送的 JSON Patch 文档，这是一种表示部分更新内容的格式。
            // - `ApplyTo` 是一个扩展方法（Extension Method），表示将 JSON Patch 文档应用到一个对象上，并修改它的属性值。
            // - `villaDTO` 是一个 `VillaUpdateDTO` 类型的对象，表示一个数据传输对象（DTO），用来简化数据交换。这个对象包含了别墅实体的部分属性，比如 `Id`、`Name`、`Details` 等。
            // - `ModelState` 是一个 `ModelStateDictionary` 类型的属性，表示当前请求的模型状态（Model State），用来验证数据是否有效。
            // 将客户端发送的 JSON Patch 文档应用到别墅的数据传输对象上，并修改它的属性值，并传入模型状态参数来验证数据是否有效。
            patchDTO.ApplyTo(villaDTO, ModelState);

            Villa model = new()
            {
                Amenity = villaDTO.Amenity,
                Details = villaDTO.Details,
                Id = villaDTO.Id,
                ImageUrl = villaDTO.ImageUrl,
                Name = villaDTO.Name,
                Occupancy = villaDTO.Occupancy,
                Rate = villaDTO.Rate,
                Sqft = villaDTO.Sqft,
                UpdatedDate = DateTime.Now
            };

            _db.Villas.Update(model);
            _db.SaveChanges();

            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            return NoContent();
        }

    }
}

MagicVilla_VillaAPI/Data/ApplicationDbContext.cs

using MagicVilla_VillaAPI.Models;
using Microsoft.EntityFrameworkCore;

namespace MagicVilla_VillaAPI.Data
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        /// <summary>
        /// - `public` 是一个访问修饰符（Access Modifier），表示这个属性（Property）可以被任何类或对象访问。
        /// - `DbSet<Villa>` 是一个泛型类（Generic Class），表示一个数据库中的一张表，其中的泛型参数 `Villa` 表示表中的每一行对应一个 `Villa` 类型的对象。
        /// - `Villas` 是一个属性的名称，表示这个数据库表的名称，也可以用来查询或操作这个表中的数据。
        /// - `{ get; set; }` 是一个属性的访问器（Accessor），表示这个属性可以被获取或设置，也就是说，可以读取或修改这个表中的数据。
        /// </summary>
        public DbSet<Villa> Villas { get; set; }

        /// <summary>
        /// 重写这个方法,可以在创建表的时候,同时初始化一些数据 
        /// </summary>
        /// <param name="modelBuilder"></param>
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            // getdate()是sqlserver支持的函数,sqlite不支持
            // modelBuilder.Entity<Villa>()
            //     .Property(v => v.UpdatedDate)
            //     .HasDefaultValueSql("getdate()");

            // sqlite支持CURRENT_TIMESTAMP
            modelBuilder.Entity<Villa>()
                .Property(v => v.UpdatedDate)
                .HasDefaultValueSql("CURRENT_TIMESTAMP");


            modelBuilder.Entity<Villa>().HasData(
                    new Villa
                    {
                        Id = 1,
                        Name = "Royal Villa",
                        Details = "Fusce 11 tincidunt maximus leo, sed scelerisque massa auctor sit amet. Donec ex mauris, hendrerit quis nibh ac, efficitur fringilla enim.",
                        ImageUrl = "https://dotnetmasteryimages.blob.core.windows.net/bluevillaimages/villa3.jpg",
                        Occupancy = 4,
                        Rate = 200,
                        Sqft = 550,
                        Amenity = "",
                        CreatedDate = DateTime.Now
                    },
                  new Villa
                  {
                      Id = 2,
                      Name = "Premium Pool Villa",
                      Details = "Fusce 11 tincidunt maximus leo, sed scelerisque massa auctor sit amet. Donec ex mauris, hendrerit quis nibh ac, efficitur fringilla enim.",
                      ImageUrl = "https://dotnetmasteryimages.blob.core.windows.net/bluevillaimages/villa1.jpg",
                      Occupancy = 4,
                      Rate = 300,
                      Sqft = 550,
                      Amenity = "",
                      CreatedDate = DateTime.Now
                  },
                  new Villa
                  {
                      Id = 3,
                      Name = "Luxury Pool Villa",
                      Details = "Fusce 11 tincidunt maximus leo, sed scelerisque massa auctor sit amet. Donec ex mauris, hendrerit quis nibh ac, efficitur fringilla enim.",
                      ImageUrl = "https://dotnetmasteryimages.blob.core.windows.net/bluevillaimages/villa4.jpg",
                      Occupancy = 4,
                      Rate = 400,
                      Sqft = 750,
                      Amenity = "",
                      CreatedDate = DateTime.Now
                  },
                  new Villa
                  {
                      Id = 4,
                      Name = "Diamond Villa",
                      Details = "Fusce 11 tincidunt maximus leo, sed scelerisque massa auctor sit amet. Donec ex mauris, hendrerit quis nibh ac, efficitur fringilla enim.",
                      ImageUrl = "https://dotnetmasteryimages.blob.core.windows.net/bluevillaimages/villa5.jpg",
                      Occupancy = 4,
                      Rate = 550,
                      Sqft = 900,
                      Amenity = "",
                      CreatedDate = DateTime.Now
                  },
                  new Villa
                  {
                      Id = 5,
                      Name = "Diamond Pool Villa",
                      Details = "Fusce 11 tincidunt maximus leo, sed scelerisque massa auctor sit amet. Donec ex mauris, hendrerit quis nibh ac, efficitur fringilla enim.",
                      ImageUrl = "https://dotnetmasteryimages.blob.core.windows.net/bluevillaimages/villa2.jpg",
                      Occupancy = 4,
                      Rate = 600,
                      Sqft = 1100,
                      Amenity = "",
                      CreatedDate = DateTime.Now
                  });
        }
    }
}