Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ServiceStack 4.5.6 & Npgsql 3 & Array fields
using System;
using NUnit.Framework;
using ServiceStack;
using ServiceStack.Configuration;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;
namespace MyCompany.Tests
{
public class OrmLiteModelArrayTests
{
[Alias("color")]
public class ColorModel
{
public string Color { get; set; }
public string Value { get; set; }
}
public class ColorJsonModel
{
public int Id { get; set; }
public string ColorJson { get; set; }
}
[Test]
public void test_model_with_array_to_json()
{
OrmLiteConfig.DialectProvider = PostgreSqlDialect.Provider;
var testingConn = ConfigUtils.GetConnectionString("testing");
using (var db = testingConn.OpenDbConnection())
{
db.DropAndCreateTable<OrmLiteModelTests.ColorModel>();
db.Insert(new ColorModel { Color = "red", Value = "#f00" });
db.Insert(new ColorModel { Color = "green", Value = "#0f0" });
db.Insert(new ColorModel { Color = "blue", Value = "#00f" });
db.Insert(new ColorModel { Color = "cyan", Value = "#0ff" });
db.Insert(new ColorModel { Color = "magenta", Value = "#f0f" });
db.Insert(new ColorModel { Color = "yellow", Value = "#ff0" });
db.Insert(new ColorModel { Color = "black", Value = "#000" });
const string sql = @"SELECT 1::integer AS id
, json_agg(color.*) AS color_json
FROM color;";
var results = db.Select<ColorJsonModel>(sql);
Assert.That(results.Count, Is.EqualTo(1));
foreach (var result in results)
{
Console.WriteLine("{0}".Fmt(result.ColorJson));
Assert.That(result.Id, Is.EqualTo(1));
Assert.That(result.ColorJson, Is.Not.Null);
}
}
}
[Test]
public void test_model_with_array_and_json()
{
OrmLiteConfig.DialectProvider = PostgreSqlDialect.Provider;
var testingConn = ConfigUtils.GetConnectionString("testing");
using (var db = testingConn.OpenDbConnection())
{
db.DropAndCreateTable<OrmLiteModelTests.ColorModel>();
db.Insert(new ColorModel { Color = "red", Value = "#f00" });
db.Insert(new ColorModel { Color = "green", Value = "#0f0" });
db.Insert(new ColorModel { Color = "blue", Value = "#00f" });
db.Insert(new ColorModel { Color = "cyan", Value = "#0ff" });
db.Insert(new ColorModel { Color = "magenta", Value = "#f0f" });
db.Insert(new ColorModel { Color = "yellow", Value = "#ff0" });
db.Insert(new ColorModel { Color = "black", Value = "#000" });
// SQL contains array and json aggs.
// We usually have ARRAY fields defined in the db, but when
// retrieved we json-ize them. In otherwords the array exists in the tables/views.
// We use SELECT.* which would contain the ARRAY field.
// Array fields are not used in any of our models and should not cause the other
// fields in the model to not be populated.
const string sql = @"SELECT 1::integer AS id
, json_agg(color.*) AS color_json
, array_agg(color.*) AS color_array
FROM color;";
var results = db.Select<ColorJsonModel>(sql);
Assert.That(results.Count, Is.EqualTo(1));
foreach (var result in results)
{
Console.WriteLine("{0}".Fmt(result.ColorJson));
Assert.That(result.Id, Is.EqualTo(1));
Assert.That(result.ColorJson, Is.Not.Null);
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.