How to define a model for a join table ?

[ajonno]

Having a lot of trouble (errors) trying to insert data into a many to many join table, which I think is because perhaps ive not defined correctly?

ERROR: "null value in column "movie_id" of relation "movie_cast" violates not-null constraint"

Thing is, the value being used in the movie_id field is clearly, not null!

This is the join table:

    [Postgrest.Attributes.Table("movie_cast")]
    public class MovieCast : SupabaseModel
    {
        [ForeignKey("movie_id")]
        public string MovieId { get; set; }

        [ForeignKey("person_id")]
        public string PersonId { get; set; }

        [Postgrest.Attributes.Column("character_name")]
        public string? CharacterName { get; set; }

        [Postgrest.Attributes.Column("created_at")]
        public DateTime CreatedAt { get; set; }
    }

Here is attempt to insert a row which keeps failing:

        var movieCastMember = new PostgresModels.MovieCast
        {
            MovieId = "a275c2bc-017f-4423-bf88-56e2bfc0f372", 
            PersonId = "f7eb55d5-4839-4608-91a4-25ac641a3c5b",  
            CharacterName = "xxxxxx", 
            CreatedAt = DateTime.Now
        };

        var movieCastHandle = supabase?.From<PostgresModels.MovieCast>();

        try
        {
            await movieCastHandle!.Insert(movieCastMember);
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

Just to note, I can successfully insert a row to this table when using SQL.

insert into movie_cast (movie_id, person_id, created_at, character_name)
values ('eb55349a-24fc-47de-8582-56a1c70cedb0', '2e246db7-bd4b-46ff-9e1b-bae6576cc298', '2022-07-16 12:56:32+00', 'ffff');

[acupofjose]

Thanks for making such a detailed issue! I believe your problems is because you need to add a Column attribute! (I’m not actually sure what The ForeignKey attribute is referencing, that’s not an attribute included with postgrest-csharp)

Could you try:

  [Column("movie_id")]
  [ForeignKey(“movie_id”]
   public string MovieId { get; set; }

[ajonno]

my pleasure, its a great api by the way for supabase, we're using it in a Blazer web app portion of the solution we're building. yep that was it ! thanks for quick reply.