Web API .net and MySQL: Cannot test the post and get method that I just implemented-CodePudding

As a beginner in C# and Web API, I'm trying to develop a web API linked to a MySQL server.

As my whole database contains 10 tables, I have created for my solutions only two controllers but I cannot post or even get any entity.

These 10 tables are binded thanks to Foreign Keys: does that mean I have to code the controllers for every table to be able to test my web api ?

For example, among the two tables I tried to implement to the API, there is the "professionfield" table.

This one is binded to two other tables: "user" and "profession".

Here is the DTO for ProfessionField:

using SIMS_SQL.DataObjects;

namespace SIMS_SQL.Models
{
    public class ProfessionFieldDTO
    {
        public int ProfessionFieldId { get; set; }
        public string? ProfessionFieldName { get; set; }
        public int ProfessionId { get; set; }

        public virtual Profession? Profession { get; set; }
        public virtual ICollection<User>? Users { get; set; }
    }
}

And here is the Post method from the ProfessionFieldControl

//POST ProfessionField
[HttpPost("InsertProfessionField")]
public async Task<HttpStatusCode> InsertProfessionField(ProfessionFieldDTO ProfessionField)
    {
        var entity = new ProfessionField()
        {
            ProfessionFieldName = ProfessionField.ProfessionFieldName,
            ProfessionId = ProfessionField.ProfessionId,
            Profession = ProfessionField.Profession,
            Users = ProfessionField.Users
        };

    open_Data_Usage_In_SwedenContext.ProfessionFields.Add(entity);
    await open_Data_Usage_In_SwedenContext.SaveChangesAsync();
    return HttpStatusCode.Created;
}

The thing is swagger asks me for posting data from the whole database:

{
  "professionFieldId": 0,
  "professionFieldName": "string",
  "professionId": 0,
  "profession": {
    "idProfession": 0,
    "professionName": "string",
    "professionFields": [
      {
        "idProfessionField": 0,
        "professionFieldName": "string",
        "professionId": 0,
        "profession": "string",
        "users": [
          "string"
        ]
      }
    ],
    "users": [
      {
        "idUser": 0,
        "userProfessionId": 0,
        "userProfessionFieldId": 0,
        "userName": "string",
        "userMail": "string",
        "userCompany": "string",
        "userPicture": "string",
        "userProfession": "string",
        "userProfessionField": {
          "idProfessionField": 0,
          "professionFieldName": "string",
          "professionId": 0,
          "profession": "string",
          "users": [
            "string"
          ]
        },
        "dataUsages": [
          {
            "idDataUsage": 0,
            "openDataId": 0,
            "dataFormatId": 0,
            "languageId": 0,
            "isDownloaded": 0,
            "usedBy": 0,
            "dataFormat": {
              "idDataFormat": 0,
              "dataFormatName": "string",
              "dataUsages": [
                "string"
              ]
            },
            "language": {
              "idDataLanguage": 0,
              "dataLanguageName": "string",
              "dataUsages": [
                "string"
              ]
            },
            "openData": {
              "idData": 0,
              "dataUrl": "string",
              "dataOpenLicense": 0,
              "dataOwnerId": 0,
              "updateFrequencyId": 0,
              "dataThemeId": 0,
              "dataOwner": {
                "idDataOwner": 0,
                "dataOwnerName": "string",
                "openData": [
                  "string"
                ]
              },
              "dataTheme": {
                "idDataTheme": 0,
                "dataThemeName": "string",
                "openData": [
                  "string"
                ]
              },
              "updateFrequency": {
                "idUpdateFrequency": 0,
                "updateFrequencyName": "string",
                "openData": [
                  "string"
                ]
              },
              "dataUsages": [
                "string"
              ]
            },
            "usedByNavigation": "string"
          }
        ]
      }
    ]
  },
  "users": [
    {
      "idUser": 0,
      "userProfessionId": 0,
      "userProfessionFieldId": 0,
      "userName": "string",
      "userMail": "string",
      "userCompany": "string",
      "userPicture": "string",
      "userProfession": "string",
      "userProfessionField": {
        "idProfessionField": 0,
        "professionFieldName": "string",
        "professionId": 0,
        "profession": "string",
        "users": [
          "string"
        ]
      },
      "dataUsages": [
        {
          "idDataUsage": 0,
          "openDataId": 0,
          "dataFormatId": 0,
          "languageId": 0,
          "isDownloaded": 0,
          "usedBy": 0,
          "dataFormat": {
            "idDataFormat": 0,
            "dataFormatName": "string",
            "dataUsages": [
              "string"
            ]
          },
          "language": {
            "idDataLanguage": 0,
            "dataLanguageName": "string",
            "dataUsages": [
              "string"
            ]
          },
          "openData": {
            "idData": 0,
            "dataUrl": "string",
            "dataOpenLicense": 0,
            "dataOwnerId": 0,
            "updateFrequencyId": 0,
            "dataThemeId": 0,
            "dataOwner": {
              "idDataOwner": 0,
              "dataOwnerName": "string",
              "openData": [
                "string"
              ]
            },
            "dataTheme": {
              "idDataTheme": 0,
              "dataThemeName": "string",
              "openData": [
                "string"
              ]
            },
            "updateFrequency": {
              "idUpdateFrequency": 0,
              "updateFrequencyName": "string",
              "openData": [
                "string"
              ]
            },
            "dataUsages": [
              "string"
            ]
          },
          "usedByNavigation": "string"
        }
      ]
    }
  ]
}

If I try to post, I get an error 400 'The ProfessionField field is required' and if I try to set the attributes 'user' and 'profession' as null, I got an error 500 due to foreign key constraint failure.

Even the get method doesn't work and return an error 500 as 'System.InvalidOperationException: Sequence contains more than one matching element'

Should I get rid of the foreign keys or shall I wait for when I added a controller and DTO for every table of the database ?

If you have any alternative, I will happily take them into account :D

Thanks for reading !

CodePudding user response：

The issue is likely with how your models are adding the foreign keys and objects.

using SIMS_SQL.DataObjects;

namespace SIMS_SQL.Models
{
    public class ProfessionFieldDTO
    {
        public int ProfessionFieldId { get; set; }
        public string? ProfessionFieldName { get; set; }
        public int ProfessionId { get; set; }

        // below references bind the objects to your DTO model
        public virtual Profession? Profession { get; set; }
        public virtual ICollection<User>? Users { get; set; }
    }
}

Referencing public virtual fields in your model tells entity framework that you are wanting to bind to those and any foreign key objects those bind to.

Looking at the json from your swagger you'll see a circular dependency between ProfessionField and Profession.

{
  //
  // professionalField appears here
  // 
  "professionFieldId": 0,
  "professionFieldName": "string",
  "professionId": 0,
  "profession": {
    "idProfession": 0,
    "professionName": "string",
    //
    // and also here!
    //
    "professionFields": [
      {
        "idProfessionField": 0,
        "professionFieldName": "string",
        "professionId": 0,
        "profession": "string",
        "users": [
          "string"
        ]
      }
    ],
}

As you have a many to one relationship your ProfessionField and Profession models those should look like:

public class Profession
{
   public int ProfessionId{ get; set; }
   public string ProfessionName { get; set; }
   public virtual ICollection<ProfessionField> ProfessionFields {get; set; }

}

// and

public class ProfessionField
{
   public int ProfessionFieldId { get; set; }
   public string ProfessionFieldName  { get; set; }
}

Next you'll just need to update your POST request to add a new ProfessionField to your database:

var entity = new ProfessionField()
{
    ProfessionFieldName = ProfessionField.ProfessionFieldName
};