C# ASP.Net - Error when trying to delete data from table

Question

I have table Player and table Statistic and other tables that are not important in this question. Table Player has PK Player_ID and it is FK in table Statistic. The relationship between these tables is one-to-many (one player can have more statistics).

Here is the code: GenericRepository(I have created it to have unique class for CRUD methods)

public async Task<int> Delete<T>(Guid id) where T : class
{
        try
        {
            T entity = await Get<T>(id);
            Context.Set<T>().Remove(entity);
            return await Context.SaveChangesAsync();
        }
        catch (Exception ex)
        {
            throw ex;
        }
}

PlayerRepository(for managing operations on Player table)

public async Task<int> Delete(Guid id)
{
        try
        {
            var player = await GenRepository.Get<Player>(id);
            if (player == null)
            {
                return 404;
            }
            else
            {
                return await GenRepository.Delete(player);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
}

PlayerService(connection between repository and controller in WebAPI)

public async Task<int> Delete(Guid id)
{
        try
        {
            return await PlayerRepository.Delete(id);
        }
        catch (Exception ex)
        {
            throw ex;
        }
}

PlayerController

[HttpDelete]
    [Route("deleteplayer")]
    public async Task<HttpResponseMessage> Delete(Guid id)
    {
        try
        {
            var Finder = Mapper.Map<PlayerView>(await PlayerService.Get(id));
            if(Finder == null)
            {
                return Request.CreateErrorResponse(HttpStatusCode.BadRequest, "Player doesn't exist in database.");
            }

            var Response = await PlayerService.Delete(id);
            var profile = "../../../app/pictures/FootballFanAppPictures/" + Finder.Club_ID.ToString().ToUpper() + "/profiles/" + id.ToString().ToUpper() + ".png";
            var details = "../../../app/pictures/FootballFanAppPictures/" + Finder.Club_ID.ToString().ToUpper() + "/" + id.ToString().ToUpper() + ".png";
            if (System.IO.File.Exists(profile))
            {
                System.IO.File.Delete(profile);
            }
            if (System.IO.File.Exists(details))
            {
                System.IO.File.Delete(details);
            }
            return Request.CreateResponse(HttpStatusCode.OK, Response);
        }
        catch(Exception ex)
        {
            return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex);
        }
    }

Entity models:

-database models

 public partial class Player
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Player()
    {
        this.Statistic = new HashSet<Statistic>();
    }

    public System.Guid Player_ID { get; set; }
    public System.Guid Club_ID { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public double Height { get; set; }
    public int Weight { get; set; }
    public System.DateTime BirthDate { get; set; }
    public string Nationality { get; set; }
    public string Position { get; set; }
    public int Shirtnmbr { get; set; }

    public virtual Club Club { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Statistic> Statistic { get; set; }
}
using System;
using System.Collections.Generic;

public partial class Statistic
{
    public System.Guid Statistic_ID { get; set; }
    public System.Guid Player_ID { get; set; }
    public int Goals { get; set; }
    public int Assists { get; set; }
    public int FoulsFor { get; set; }
    public int FoulsAgainst { get; set; }
    public int ShotsTotal { get; set; }
    public int ShotsGoal { get; set; }

    public virtual Player Player { get; set; }
}

-domain models (used in repository)

public class PlayerDomain : IPlayerDomain
{
    public Guid Player_ID { get; set; }
    public Guid Club_ID { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public double Height { get; set; }
    public int Weight { get; set; }
    public DateTime BirthDate { get; set; }
    public string Nationality { get; set; }
    public string Position { get; set; }
    public int Shirtnmbr { get; set; }
    public virtual ICollection<IStatisticDomain> Statistic { get; set; }
}
public class StatisticDomain: IStatisticDomain
{
    public Guid Statistic_ID { get; set; }
    public Guid Player_ID { get; set; }
    public int Goals { get; set; }
    public int Assists { get; set; }
    public int FoulsFor { get; set; }
    public int FoulsAgainst { get; set; }
    public int ShotsTotal { get; set; }
    public int ShotsGoal { get; set; }
}

-view models (used in controller)

public class PlayerView
{
    public Guid Player_ID { get; set; }
    public Guid Club_ID { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public double Height { get; set; }
    public int Weight { get; set; }
    public DateTime BirthDate { get; set; }
    public string Nationality { get; set; }
    public string Position { get; set; }
    public int Shirtnmbr { get; set; }
    public virtual ICollection<StatisticView> Statistic { get; set; }
}
public class StatisticView
{
    public Guid Statistic_ID { get; set; }
    public Guid Player_ID { get; set; }
    public int Goals { get; set; }
    public int Assists { get; set; }
    public int FoulsFor { get; set; }
    public int FoulsAgainst { get; set; }
    public int ShotsTotal { get; set; }
    public int ShotsGoal { get; set; }
}

Every class is in a separate file. I use database first approach so i got .edmx file along with database models. Database is created in SQL Server Management Studio.

I can update Player but when I try to delete it i get this error:

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

I have searched various answers on google and stackoverflow but I couldn't find an answer that solves my problem

Answer 1

I don't see Statistic deletion on Player delete in your code. Before you can delete a Player, you need to delete all Player related Statistics first. If there is any left, a player will fail on delete operation.

Answer 2

Before you call this line:

var Response = await PlayerService.Delete(id);

You are going to need to retrieve a list of your statistics that are assigned to the player you are trying to delete.

Then loop trough each of the statistics and delete those from your database first.

var stats = Finder.Statistic.ToList();

if(stats != null && stats.Any())
{
   foreach(var stat in stats)
   { 
      //retrieve your stat record here from the database
      //check that the stat record is not null
      //delete your stat record here
   }
}

Then you should be able to delete your player record as there will no longer be references to it.

OR

You could just set ON DELETE CASCADE to true, but you need to be careful that you fully understand what all will be deleted on player deletion.

Answer 3

Set PrimaryKey and Foreignkey Relationship :according to @Bad Dub suggestion.