C#•3y ago

Structuring SQL database to accommodate models

I have the following models (simplified for demo):

public class Event
{
  public int Id { get; set;}
  public string Name { get; set;}
  public IEnumerable<Fight> Fights { get; set;}
}

public class Fight
{
  public int Id { get; set;}
  public int EventId { get; set;}
  // other unimportant properties
}

public class Event
{
  public int Id { get; set;}
  public string Name { get; set;}
  public IEnumerable<Fight> Fights { get; set;}
}

public class Fight
{
  public int Id { get; set;}
  public int EventId { get; set;}
  // other unimportant properties
}

I have set up two SQL tables: - Events (columns: Id (PK), Name) - Fights (columns: Id (PK), EventId(FK)) I have set the primary keys to be Identity in SQL, such that I leave the responsibility of creating the Ids to the database. I read Event and Fight data from an external API. So when I have these objects in memory (after API call), the Ids for each of the objects are null (0). Currently, I have to write the Events to the database and return the objects that are written, so I can fetch the respective EventId properties to then use LINQ to assign to the relevant Fight model. Then I can write the Fight model to the database. If I complete these database calls out of order (i.e. write Fight model to DB first), the DB write fails because the Fight model EventId isn't set. This feels to me like I've implemented a bad design approach, as this may not be obvious to other coders that the call order to the DB write is important (note this is only a personal project, so in reality it doesn't matter but I would like to understand a better approach). I've tried to search this on google, but I'm finding it hard to reduce my situation into a query that gets valuable information.

3 Replies

Cisien•3y ago

Make your navigation properties icollection or list, you cant add to ienumeral. This will let you add the relationship without having to save and fetch the id Add an event navigation property to flight along with the id, ef will populate this property with an instance of the event when queries, or you can set a new event instance, and it will be added and the id set properly Id say in general the gap you have is around navigation properties

sonodan.OP•3y ago

I'm using Dapper on this project, though I am contemplating a switch to EF as since then I've read this is general used more widely. In the interim, I'm looking up an equivalent to navigation properties in Dapper. Do you have any recommended reading resources on the approach you mentioned?

Cisien•3y ago

I think the way to do this with dapper is stored procedures

Gaming

Programming

Structuring SQL database to accommodate models