I have a long list of crafted items with a reagents dictionary consisting of an int that is the id of a reagent and an int that is the quantity to use.

I have no idea how to store this list in tables.

public class CraftedItem : IEquatable<CraftedItem>
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public Dictionary<int, int> ReagentsAndQuantities { get; set; }
    public int RecipeId { get; set; }

    public bool Equals(CraftedItem other)
    {
        if (other is null)
            return false;

        return Id == other.Id;
    }

    public override bool Equals(object obj) => Equals(obj as Recipe);
    public override int GetHashCode() => Id.GetHashCode();
}

When I google it, I get lots of hits on how to use dictionaries but nothing on how to store a class which has a dictionary as a member. Can anyone point me in the right direction?

CodePudding user response：

I can't speak to EF at all, but if you are just using normal ADO connections, you just need a separate table for your dictionary:

create table crafted_item (
  id int primary key not null,
  name text,
  recipe_id int
);

create table reagents_and_quantities (
  crafted_item_id int not null,
  reagent int not null,
  quantity int,
  constraint reagents_and_quantities_pk primary key (crafted_item_id, reagent)
);

The trick is then your CRUD which you use to populate the data. I would think something as simple as this would work. Assuming your CRUD GetAll method looks something like this:

  List<CraftedItem> results = new List<CraftedItem>();

  using (NpgsqlCommand cmd = new NpgsqlCommand("select * from crafted_item", conn))
  {
      using (NpgSqlDataReader reader = cmd.ExecuteReader())
      {
          while (reader.Read())
          {
              CraftedItem foo = new CraftedItem();
              // populate the properties
              foo.ReagentsAndQuantities = new Dictionary<int, int>();
              results.Add(foo);
          }
          reader.Close();
      }
  }

Then, before you close your connection do another pass and dynamically bind the dictionary entries:

  using (NpgsqlCommand cmd = new NpgsqlCommand("select * from reagents_and_quantities", conn))
  {
      using (NpgSqlDataReader reader = cmd.ExecuteReader())
      {
          while (reader.Read())
          {
              int craftedItem = reader.GetInt32(0);
            
              CraftedItem foo = results.Find(x => x.Id == craftedItem);
              if (foo != null)
              {
                  int reagent = reader.GetInt32(1);
                  int qty = reader.GetInt32(2);
                  foo.ReagentsAndQuantities[reagent] = qty;
              }
          }
          reader.Close();
      }
  }

I got a little lazy, but hopefully you get the idea. Presumably your "GetAll" would have some form of where clause, so you would of course want to apply that to the reagents_and_quantities details to streamline.

Alternatively you could use a JSON datatype, but I wouldn't bother.