How to set up JSON columns with Npgsql?

Question

I'm setting up a mini model and I'm getting this exception when executing the code from the docs website.

Here's my code:

  public class SomeEntity
  {
    public int Id { get; set; }
    [Column(TypeName = "jsonb")]
    public Customer Customer { get; set; }
  }

  public class Customer    // Mapped to a JSON column in the table
  {
    public string Name { get; set; }
    public int Age { get; set; }
    public Order[] Orders { get; set; }
  }

  public class Order       // Part of the JSON column
  {
    public decimal Price { get; set; }
    public string ShippingAddress { get; set; }
  }

using (var dbContext = services.GetRequiredService<AppDbContext>())
{
  await dbContext.Database.MigrateAsync();

  dbContext.SomeEntities.Add(
    new SomeEntity
    {
      Customer = new Customer
      {
        Name = "Roji",
        Age = 35,
        Orders = new[]
        {
          new Order { Price = 3, ShippingAddress = "Somewhere" },
          new Order { Price = 3, ShippingAddress = "Nowhere" }
        }
      }
    });

  await dbContext.SaveChangesAsync();
}

When I call SaveChanges, I get the following exception:

Npgsql.PostgresException: 42P01: relation \"SomeEntities\" does not exist

Here's a repro project.

Since I believe I followed all the steps in the manual, I've opened an issue here too.

Shay Rojansky · Accepted Answer · 2020-03-27 11:28:59Z

1

You're calling the MigrateAsync method, but your project doesn't have any actual migrations (those can be created with dotnet ef migrations add <name>). If you're just playing around, you likely want to call dbContext.Database.EnsureCreated instead, see this doc page.

edited Mar 27, 2020 at 11:28

answered Mar 26, 2020 at 2:05

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shimmy Weitzhandler Over a year ago

It did answer my question but the error still persisted when trying to access the DB afterwards. The answer for that error is that raw SQL query is different in PG and should be formatted differently. Thanks for your answer. I guess PG isn't 1:1 with SQL Server after all, but I'm still gonna stick to it because it's way more powerful to what I've found so far.

Shimmy Weitzhandler Over a year ago

I can see it when using the Npgsql VSIX, if I query SELECT * FROM SomeEntities, I get that error (screenshot), but if I execute SELECT * FROM public."SomeEntities", I get this error, but I do get the results thereafter (screenshot).

Shay Rojansky Over a year ago

Be mindful that PostgreSQL folds unquoted identifiers to lowercase, so SomeEntities becomes someentities (and probably triggers an error). Quotes make sure that case is preserved.

Collectives™ on Stack Overflow

How to set up JSON columns with Npgsql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related