ASP.NET Core中如何通过卖家主键ID查询其创建的车辆列表

一、现有代码核心问题 #

• 外键关系定义错误：当前Seller表存储单条VehicleId外键，仅支持「一个卖家对应一辆车」的关联，完全不符合「一个卖家名下有多辆车」的业务逻辑，外键应该放在Vehicle表中。
• Vehicle类字段类型错误：SellerEmail、SellerPhone两个字段错误定义为Seller实体类型，实际应为字符串/数值类型。

二、步骤1：修正实体类定义 #

修正Seller类 #

删除单车辆关联，增加多车辆集合属性：

namespace Models
{
    public class Seller
    {
        public int Id { get; set; }
        // 删除原来的VehicleId、单Vehicle关联属性
        // 新增多车辆集合关联
        public ICollection<Vehicle> Vehicles { get; set; } = new List<Vehicle>();
        
        // 其他原有字段保持不变
        public string Name { get; set; }
        public string Title { get; set; }
        [EmailAddress(ErrorMessage = "Invalid Email ID")]
        [StringLength(50)]
        public string SellerEmail { get; set; }
        public int Age { get; set; }
        public string Sex { get; set; }
        public string Company { get; set; }
        public string Address { get; set; }
        public int SellerPhone { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public int PhoneNo2 { get; set; }
        public string PostalCode { get; set; }
        [Display(Name = "Profile Image")]
        [StringLength(100)]
        public string ProfileImagePath { get; set; }
    }
}

修正Vehicle类 #

增加关联Seller的外键，修正错误字段类型：

namespace Models
{
    public class Vehicle
    {
        public int Id { get; set; }
        // 新增关联卖家的外键
        public int SellerId { get; set; }
        [ForeignKey("SellerId")]
        public Seller Seller { get; set; }
        
        // 其他原有属性不变，仅修正两个字段的错误类型
        public Make Make { get; set; }
        [RegularExpression("^[1-9]*$", ErrorMessage = "Select Make")]
        public int MakeID { get; set; }
        public Model Model { get; set; }
        [RegularExpression("^[1-9]*$", ErrorMessage = "Select Model")]
        public int ModelID { get; set; }
        [Required(ErrorMessage = "Provide Year")]        
        [YearRangeTillDate(2000,ErrorMessage = "Not with in the valid Year range")]
        public int Year { get; set; }
        [Required(ErrorMessage = "Enter Mileage")]
        [Range(1,int.MaxValue,ErrorMessage ="Not with in the valid mileage range")]
        public int Mileage { get; set; }
        public string Features { get; set; }
        [Required(ErrorMessage ="Provide Seller Name")]
        [StringLength(50)]
        public string SellerName { get; set; }
        // 修正类型为string，原来错误定义为Seller
        [EmailAddress(ErrorMessage ="Invalid Email ID")]
        [StringLength(50)]
        public string SellerEmail { get; set; }
        // 修正类型为string，原来错误定义为Seller
        [Required(ErrorMessage = "Provide Phone No.")]
        [Phone]
        [StringLength(15)]
        public string SellerPhone { get; set; }
        [Required(ErrorMessage = "Provide Price")]
        [Range(1, 999999999, ErrorMessage = "Not with in the valid price range")]
        public int Price { get; set; }
        [Required]
        [StringLength(10)]
        [RegularExpression("^[a-zA-Z]*$", ErrorMessage ="Select Currency")]       
        public string Currency { get; set; }
        [Display(Name ="Image File")]
        [StringLength(100)]
        public string ImagePath { get; set; }
    }
}

三、步骤2：新增查询接口与实现 #

修改ISellerRepository接口 #

添加查询方法定义：

namespace Persistence.Repository.IRepository
{
    public interface ISellerRepository : IRepository<Seller>
    {
        IEnumerable<SelectListItem> GetModelListForDropDown();
        Task<bool> SellerOwnsVehicleAsync(int sellerId, int vehicleId);
        // 新增根据卖家ID查所有车辆的方法
        Task<IEnumerable<Vehicle>> GetVehiclesBySellerIdAsync(int sellerId);
    }
}

修改SellerRepository实现类 #

添加方法实现：

namespace Persistence.Repository
{
    public class SellerRepository : Repository<Seller>, ISellerRepository
    {
        private readonly AutoServDbContext _db;
        public SellerRepository(AutoServDbContext db) : base (db)
        {
            _db = db;
        }
        // 原有方法保持不变
        public IEnumerable<SelectListItem> GetModelListForDropDown()
        {
            return _db.Sellers.Select(i => new SelectListItem()
            {
                Text = i.Name,
                Value = i.Id.ToString()
            });
        }

        public async Task<bool> SellerOwnsVehicleAsync(int sellerId, int vehicleId)
        {
            return await _db.Vehicles.AsNoTracking()
                .AnyAsync(v => v.Id == vehicleId && v.SellerId == sellerId);
        }
        
        // 新增方法实现
        public async Task<IEnumerable<Vehicle>> GetVehiclesBySellerIdAsync(int sellerId)
        {
            // 校验卖家是否存在
            if (!await _db.Sellers.AnyAsync(s => s.Id == sellerId))
            {
                return Enumerable.Empty<Vehicle>();
            }
            // 返回该卖家关联的所有车辆
            return await _db.Vehicles
                .AsNoTracking()
                .Where(v => v.SellerId == sellerId)
                .ToListAsync();
        }
    }
}

修改完成后迁移数据库更新表结构，调用GetVehiclesBySellerIdAsync方法传入卖家ID即可拿到对应车辆列表。

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