I am using Entity Framework Core with npgsql postgresql for Entity Framework Core.
My question is, using migrations, how do I mark a class property to generate a JSONB column type?
For example:
public class MyTableClass
{
public int Id { get; set; }
// My JSONB column
public string Data { get; set; }
}
Thanks in advance.
Based on H. Herzl comment:
My final solution was something like this:
public class MyTableClass
{
public int Id { get; set; }
[Column(TypeName = "jsonb")]
public string Data { get; set; }
}
Migrations generated this:
Data = table.Column<string>(type: "jsonb", nullable: true),
When updated the database with migrations, the Data column was created correctly with jsonb type.
using string as was suggested by @bruno.almeida is a nice solution but couldn't be queried.
additional approaches are to use:
JsonDocument or JsonElement)JsonDocument being my favorite since it could be queried, is flexible and fast to setup, e.g.:
public JsonDocument Customer { get; set; }
more details at: https://www.npgsql.org/efcore/mapping/json.html
In addition to the response by @bruno.almeida, it is worth noting that it is possible to add a type to the JSONB column. For example, I have recently been creating a column to hold various file upload errors:
I have a RecordErrors class that I think is generic enough to be used in many of my error storage situations:
public class RecordErrors
{
public IEnumerable<ResultError> Errors { get; set; } = Enumerable.Empty<ResultError>();
}
The ResultError class just has additional properties such as ErrorCode and Message but could just as easily be replaced with String if you wanted simple string error values.
My Entity then looks like this:
public class MyFileUpload
{
public int Id { get; set; }
[StringLength(800)]
public string Name { get; set; } = string.Empty;
...
[Column(TypeName = "jsonb")]
public RecordErrors? Errors { get; set; } = null;
}
Running migrations created the column with the jsonb type.
You can then save data to the database in the usual way using:
_databaseContext.SaveChangesAsync(cancellationToken);
The result in the database in my instance is JOSN that looks something like:
{
"Errors": [
{
"Code": "Columns.Unsupported",
"Message": "The upload contains the following unsupported column headings: Column 1, Column 2, Column 3, Column 4."
}
]
}
