bugfix> c# > 投稿

私は次の複数サイトのチュートリアルを進めています(https://app.pluralsight.com/library/courses/asp-dot-net-core-restful-api-building/table-of-contents) そして、ベースアプリケーションの作成と同時に、私が持っている既存のデータベースに知識を適用しようとしています。

1)AssetRepositoryのAddAssetの先頭にブレークポイントを配置します。

2)次のPOSTリクエストをAPIに送信します。

POST http://localhost:6059/api/assets
{
    "bay": "X",
    "tile":  "1",
    "Serialnumber": "SERIAL",
    "devicename": "DEVICENAME",
    "assetspecs" : [
            {
                "assetattrid": "SIZE",
                "numvalue":12
            },
            {
                "assetattrid": "POWER",
                "numvalue":24
            }
        ]
}  

3)CreateAssetはAssetControllerから実行され、AddAssetはリポジトリーからコントローラーを介して実行されます

4)デバッガーは_assetRepository.AddAsset(assetEntity);という行に遭遇します。リポジトリからAddAssetを実行します

5)AssetRepository.AddAssetの最後の行が実行される直前、(_ context.Asset.Add(asset);)デバッガーは、行に入る前に一意のIDを持つ適切に形成されたレコードのセットがあることを示します-すべてが正常に見えますそして、データベースに追加する必要があるように。

6)f11を押すと、デバッガーがコンテキストファイルにジャンプし、OnConfiguringとOnModelCreatingを問題なく実行してから、CreateAssetに戻ります。 中断した場所でのコントローラーアクション。

7)f11 AGAINを押すと、if(!_assetRepository.Save())条件がチェックされ、無効な列名Assetuidがあることを通知する承認がスローされます。アプリケーション洞察のデバッグ出力では、列は大文字で表記され、他のすべての列は奇妙に見える小文字で始まります。

私はこれに半日を費やしましたが、だれかが助けてくれれば迷います。関係していない可能性のあるファイルがいくつかありますが、さらに表示する必要がある場合はお知らせください!!!!

aContext.cs

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.Extensions.Configuration;
namespace AssetAPI.Entities
{
    public partial class aContext : DbContext
    {
        public aContext(DbContextOptions<aContext> options) : base(options)
        {
        }
        public static IConfiguration Configuration;
        public virtual DbSet<Asset> Asset { get; set; }
        public virtual DbSet<Assetspec> Assetspec { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(Configuration["connectionStrings:AssetDBConnectionString"])
                    .EnableSensitiveDataLogging();
            }
            else
            {
                optionsBuilder.EnableSensitiveDataLogging();
            }
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Asset>(entity =>
            {
                entity.HasKey(e => e.Assetuid)
                    .ForSqlServerIsClustered(false);
                     entity.ToTable("asset");
                entity.HasIndex(e => e.Assetid)
                    .HasName("asset_indx7");
                entity.HasIndex(e => e.Assetuid)
                    .HasName("asset_ndx")
                    .IsUnique();
                entity.HasIndex(e => e.Barcode)
                    .HasName("asset_barcode");
                entity.HasIndex(e => e.Pluscphyloc)
                    .HasName("asset_ndx16");
                entity.HasIndex(e => e.Status)
                    .HasName("asset_ndx15");
                entity.HasIndex(e => new { e.Assetnum, e.Assetuid })
                    .HasName("asset_ndx17");
                entity.HasIndex(e => new { e.Assetnum, e.Siteid })
                    .HasName("asset_ndx11")
                    .IsUnique();
                entity.HasIndex(e => new { e.Assetnum, e.Status })
                    .HasName("asset_ndx8");
                entity.HasIndex(e => new { e.Orgid, e.Status })
                    .HasName("asset_ndx13");
                entity.HasIndex(e => new { e.Siteid, e.Ancestor })
                    .HasName("asset_ndx7");
                entity.HasIndex(e => new { e.Siteid, e.Assetnum })
                    .HasName("asset_ndx1")
                    .IsUnique()
                    .ForSqlServerIsClustered();
                entity.HasIndex(e => new { e.Siteid, e.Calnum })
                    .HasName("asset_ndx4");
                entity.HasIndex(e => new { e.Siteid, e.Location })
                    .HasName("asset_ndx6");
                entity.HasIndex(e => new { e.Siteid, e.Parent })
                    .HasName("asset_ndx2");
                entity.HasIndex(e => new { e.Siteid, e.Status })
                    .HasName("asset_ndx10");
                entity.HasIndex(e => new { e.Siteid, e.Vendor })
                    .HasName("asset_ndx3");
                entity.HasIndex(e => new { e.Assetid, e.Siteid, e.Moved })
                    .HasName("asset_ndx14")
                    .IsUnique();
                entity.HasIndex(e => new { e.Assetnum, e.Location, e.Siteid })
                    .HasName("asset_ndx12");
                entity.HasIndex(e => new { e.Itemnum, e.Siteid, e.Itemsetid })
                    .HasName("asset_ndx5");
                entity.HasIndex(e => new { e.Siteid, e.Location, e.Parent })
                    .HasName("asset_ndx9");
                entity.Property(e => e.Assetuid)
                    .HasColumnName("assetuid");
                    .ValueGeneratedNever();
              ..... Properties hidden, lots of them, came from scaffolding existing table
            });
            modelBuilder.Entity<Assetspec>(entity =>
            {
                entity.ToTable("assetspec");
                entity.HasIndex(e => e.Assetspecid)
                    .HasName("assetspec_ndx")
                    .IsUnique();
                entity.HasIndex(e => new { e.Assetattrid, e.Section })
                    .HasName("assetspec_ndx3");
                entity.HasIndex(e => new { e.Assetnum, e.Siteid })
                    .HasName("assetspec_ndx4");
                entity.HasIndex(e => new { e.Classstructureid, e.Assetattrid, e.Section })
                    .HasName("assetspec_ndx2");
                entity.HasIndex(e => new { e.Assetattrid, e.Assetnum, e.Section, e.Siteid, e.Linearassetspecid })
                    .HasName("assetspec_ndx1")
                    .IsUnique();
                entity.Property(e => e.Assetspecid)
                    .HasColumnName("assetspecid")
                    .ValueGeneratedNever();
                ...   ..... Properties hidden, lots of them, came from scaffolding existing table
            });
        }
    }
}

AssetRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using AssetAPI.Entities;
namespace AssetAPI.Services
{
    public class AssetRepository : IAssetRepository
    {
        private aContext _context;
        public AssetRepository(aContext context)
        {
            _context = context;
        }
        public void AddAsset(Entities.Asset asset)
        {
            Random rnd = new Random();
            asset.Assetid = rnd.Next(20000000, 30000000);
            asset.Assetuid = rnd.Next(20000000, 30000000);
            asset.Assetnum = rnd.Next(800000, 900000).ToString();
            asset.Barcode = asset.Assetnum;
            asset.Changedate = DateTime.Now;
            asset.Changeby = "123";
            asset.Langcode = "EN";
            asset.Siteid = "SITE";
            asset.Orgid = "ORG";

            // the repository fills the id (instead of using identity columns)
            if (asset.Assetspecs.Any())
            {
                foreach (var assetspec in asset.Assetspecs)
                {
                    Random rnd2 = new Random();
                    assetspec.Assetspecid = rnd2.Next(20000000, 30000000);
                    assetspec.Changedate = DateTime.Today;
                    assetspec.Assetnum = asset.Assetnum;
                    assetspec.Classstructureid = "1140";
                    assetspec.Orgid = "ORG";
                    assetspec.Siteid = "SITE";
                    assetspec.Continuous = 0;
                    assetspec.Displaysequence = 0;
                    assetspec.Inheritedfromitem = 0;
                    assetspec.Itemspecvalchanged = 0;
                    assetspec.Mandatory = 0;
                 }
            }

            _context.Asset.Add(asset);

        }
        public void AddAssetspecForAsset(string assetnum, Assetspec assetspec)
        {
            var asset = GetAsset(assetnum);
            if (asset != null)
            {
                assetspec.Assetnum = assetnum;
                assetspec.Changeby = "123";
                assetspec.Changedate = DateTime.Now;
                assetspec.Classstructureid = "1140";
                assetspec.Orgid = "ORG";
                assetspec.Siteid = "SITE";
                // if there isn't an id filled out (ie: we're not upserting),
                // we should generate one
                if (assetspec == null)
                {
                    Random rnd2 = new Random();
                    assetspec.Assetspecid = rnd2.Next(20000000, 30000000);
                    assetspec.Assetattrid = "RUSZ";
                    assetspec.Numvalue = 12;
                }
                asset.Assetspecs.Add(assetspec);
            }
        }
        public bool AssetExists(string assetnum)
        {
            return _context.Asset.Any(a => a.Assetnum == assetnum);
        }

        public Asset GetAsset(string assetnum)
        {
            return _context.Asset.FirstOrDefault(a => a.Assetnum == assetnum);
        }
        public IEnumerable<Entities.Asset> GetAssets()
        {
            // return _context.Asset.OrderBy(a => a.Barcode).ThenBy(a => a.Bay).ThenBy(a => a.Tile).Take(10).ToList();
            return _context.Asset.OrderByDescending(a => a.Bay).ThenBy(a => a.Tile).Take(10).ToList();
        }
        public IEnumerable<Entities.Asset> GetAssets(IEnumerable<string> Assetnums)
        {
            return _context.Asset.Where(a => Assetnums.Contains(a.Assetnum))
                .OrderBy(a => a.Barcode)
                .OrderBy(a => a.Bay)
                .OrderBy(a => a.Tile)
                .ToList();
        }
        public IEnumerable<Assetspec> GetAssetspecsForAsset(string assetnum)
        {
            return _context.Assetspec
                        .Where(b => b.Assetnum == assetnum && b.Assetattrid == "RUSZ").OrderBy(b => b.Assetnum).ToList();
        }

        public bool Save()
        {
            return (_context.SaveChanges() >= 0);
        }
        public Assetspec GetAssetspecsForAsset(string assetnum, long assetspecId)
        {
            return _context.Assetspec
                          .Where(b => b.Assetnum == assetnum && b.Assetspecid == assetspecId).FirstOrDefault();
        }
    }
}

Asset.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace AssetAPI.Entities
{
    public partial class Asset
    {

        [Key]
        public long Assetuid { get; set; }
        public string Bay { get; set; }
        public string Tile { get; set; }
        public string TileLocation { get; set; }
        public string Serialnumber { get; set; }
        public string Devicename { get; set; }
           ....scaffolded properties hidden
        public  virtual ICollection<Assetspec> Assetspecs { get; set; } = new List<Assetspec>();
    }
}

AssetsController.cs

using AssetAPI.Models;
using AssetAPI.Services;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AutoMapper;
using AssetAPI.Entities;
namespace AssetAPI.Controllers
{
    [Route("api/assets")]
    public class AssetsController : Controller
        {
            private IAssetRepository _assetRepository;
        public AssetsController(IAssetRepository assetRepository)
        {
            _assetRepository = assetRepository;
        }
        public IActionResult GetAssets()
        {
            var assetsFromRepo = _assetRepository.GetAssets();
            var assets = Mapper.Map<IEnumerable<AssetDTO>>(assetsFromRepo);
            return Ok(assets);
        }
        [HttpGet("{assetnum}", Name = "GetAsset")]
        public IActionResult GetAsset(string assetnum)
        {
            var assetFromRepo = _assetRepository.GetAsset(assetnum); //Run GetAsset method for the passed in Assetid from AssetRepository
            if (assetFromRepo == null)
            {
                return NotFound();
            }
            var asset = Mapper.Map<AssetDTO>(assetFromRepo); //Map the entity to the DTO
            return Ok(asset); //Return the result in JSON format
        }

        //[FromBody] attribute signifies that parameter should be serialized from the request body into AssetForCreationDTO
        [HttpPost]
        public IActionResult CreateAsset([FromBody] AssetForCreationDTO asset)
        {
            //If the input provider in the request body was correctly serialized to an AssetForCreationDTO
            if (asset == null)
            {
                return BadRequest();
            }
            var assetEntity = Mapper.Map<Asset>(asset); //Map the entity to the DTO
            _assetRepository.AddAsset(assetEntity); //Add the entity to the DBContext
            if (!_assetRepository.Save()) //If the new resource cannot be saved
            {
                throw new Exception("Creating asset failed upon save");
                return StatusCode(500, "A problem occured.");
            }
            //Map the results again to learn the ID of the newly created resource
            var assetToReturn = Mapper.Map<AssetDTO>(assetEntity);
            //In case of a successful post, return 201 created response with location header
            //First param = name of route
            //Second = anon type containing new resource id
            //Third = Response body of newly created resource
            return CreatedAtRoute("GetAsset", new { assetnum =  assetToReturn.assetnum }, assetToReturn);
        }

    }
}

AssetDTO.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace AssetAPI.Models
{
    public class AssetDTO
    {
        public string assetnum { get; set; }
        public string Barcode { get; set; }
        public string Bay { get; set; }
        public string Tile { get; set; }
        public string TileLocation { get; set; }
        public string Serialnumber { get; set; }
        public string Devicename { get; set; }
    }
}

AssetForCreationDTO:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace AssetAPI.Models
{
    public class AssetForCreationDTO
    {
        public string Bay { get; set; }
        public string Tile { get; set; }
        public string TileLocation { get; set; }
        public string Serialnumber { get; set; }
        public string Devicename { get; set; }
        public ICollection<AssetspecForCreationDTO> Assetspecs { get; set; } = new List<AssetspecForCreationDTO>();
    }
}

回答 1 件
  • 私の問題を修正しました。

    このコードの背後にあるアプリケーションがテーブルを使用する方法のために、EFcoreが望むように関係を強制しませんでした。また、キーフィールドとしてASSETUIDを誤って設定しました。

    assetspecエンティティにはASSETUID列がないため、私が行った調査によると、ナビゲーションプロパティに問題が発生します。

    PKをASSETNUMに変更しました。両方のエンティティがプロパティとしてASSETNUMを持っているので、今はすべて良いです!

あなたの答え