Save enum as integer value within .NET

monkeysamok · March 1, 2016, 7:21pm

Is there an annotation or configuration to write/read enum values as an integer to/from PostgreSQL via .NET? It seems there is a Java implementation of this functionality but I haven’t found a way to do it within .NET. Thank you.

mythz · March 1, 2016, 7:29pm

You can change Enums to use int’s in OrmLite, by either setting it once globally:

JsConfig.TreatEnumAsInteger = true;

Annotating enums with [Flags] attribute:

[Flags]
enum MyEnum { ... }

Or using the new [EnumAsInt] attribute just added in v4.0.54 which will store it as an integer in OrmLite but still serialize it as a string if returned in a Response DTO.

monkeysamok · March 1, 2016, 7:42pm

Thank you for your prompt response. I tried your suggestions and am still getting an exception. It doesn’t seem that the ORM layer is converting the enum text value (in this case, NotDeleted) to the underlying integer value hence Npgsql is throwing an exception because of mismatched data types.

[NpgsqlException (0x80004005): 22P02: invalid input syntax for integer: “NotDeleted”]
Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage) +320
Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) +200
Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior) +197
Npgsql.NpgsqlCommand.ExecuteNonQueryInternal() +125
Npgsql.NpgsqlCommand.ExecuteNonQuery() +4
NServiceKit.OrmLite.OrmLiteWriteExtensions.Insert(IDbCommand dbCmd, T[] objs) +63
NServiceKit.OrmLite.<>c__DisplayClass481.<Insert>b__47(IDbCommand dbCmd) +44 NServiceKit.OrmLite.ReadConnectionExtensions.Exec(IDbConnection dbConn, Action1 filter) +147
NServiceKit.OrmLite.OrmLiteWriteConnectionExtensions.Insert(IDbConnection dbConn, T[] objs) +94
Parrotly.Data.Entities.Security.UserInfo.Create(UserInfo userInfo, Guid currentUserId) in D:\Visual Studio Projects\Parrotly\Parrotly\Parrotly\Data\Entities\Security\UserInfo.cs:43
Parrotly.Controllers.HomeController.Index(UserInfo userInfo) in D:\Visual Studio Projects\Parrotly\Parrotly\Parrotly.Web.UI\Controllers\HomeController.cs:34
lambda_method(Closure , ControllerBase , Object[] ) +104
System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +157 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +27
System.Web.Mvc.Async.AsyncControllerActionInvoker.b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +22
System.Web.Mvc.Async.WrappedAsyncResult2.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32
System.Web.Mvc.Async.AsyncInvocationWithFilters.b__3d() +50
System.Web.Mvc.Async.<>c__DisplayClass46.b__3f() +225
System.Web.Mvc.Async.<>c__DisplayClass46.b__3f() +225
System.Web.Mvc.Async.<>c__DisplayClass33.b__32(IAsyncResult asyncResult) +10
System.Web.Mvc.Async.WrappedAsyncResult1.CallEndDelegate(IAsyncResult asyncResult) +10 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34
System.Web.Mvc.Async.<>c__DisplayClass2b.b__1c() +26
System.Web.Mvc.Async.<>c__DisplayClass21.b__1e(IAsyncResult asyncResult) +100
System.Web.Mvc.Async.WrappedAsyncResult1.CallEndDelegate(IAsyncResult asyncResult) +10 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
System.Web.Mvc.Controller.b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +13
System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +36
System.Web.Mvc.Controller.b__15(IAsyncResult asyncResult, Controller controller) +12
System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +22 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +26
System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
System.Web.Mvc.MvcHandler.b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +21
System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +28
System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9742689
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

mythz · March 1, 2016, 7:44pm

How are you configuring the Enum to be treated as an int? Can you provide a stand-alone failing test?

monkeysamok · March 1, 2016, 7:57pm

I don’t have a formal test to copy but this is the gist of it…

[Flags] // tried this
public enum SomeEnum
{
Value1,
Value2
}

public class SomeTable
{
public Guid TableId { get; set; }

    public SomeEnum SomeEnumProp { get; set; }

public static Guid Create(SomeTable someRecord)
{
JsConfig.TreatEnumAsInteger = true; // tried this

        var dbFactory = new OrmLiteConnectionFactory(connectionString, PostgreSqlDialect.Provider);

        using (var db = dbFactory.Open())
        {
            db.Insert(someRecord);
        }

        return someTable.Id;
    }

}

mythz · March 1, 2016, 8:42pm

Note: the JsConfig.TreatEnumAsInteger = true should only be set once, but needs to be configured on AppStart before you use OrmLite.

We’ve got a number of tests showing Flags working as intended. Did you create the table with OrmLite?

monkeysamok · March 2, 2016, 4:20pm

I used a table-first approach.

mythz · March 2, 2016, 5:47pm

Then I guess the existing table definition must be incompatible with what OrmLite expects.
You could use OrmLite to create a table with a copy of the POCO then compare the generated schema with the existing table schema.

monkeysamok · March 2, 2016, 7:01pm

That’s a great idea. Thanks for your help.

daleholborow · February 6, 2018, 3:40pm

I have encountered this situation using MS SQL Server, where the EnumAsInt flag works nicely except when you want to use it as the PrimaryKey - this will crash on both insert and select operations. Our situation is that we are using Enums throughout our application (migrating a legacy system to a new archiving solution) to capture business-rule states and objects etc (e.g.ProjectStatusEnum : LIVE = 0, DISCARDED = 1, etc), and to be able to “disable” these settings as the system evolves, we first store all our enums in a corresponding lookup table, where the PK is the integer value of the enum eg.

classs ProjectStatusTbl { 
    // Stores PK as INT!!
    public ProjectStatusEnum ProjectStatusId {get;set;} 
	
    // Store the human readable name for convenience
    public string ProjectStatusName {get;set;} // ProjectStatusEnum.ToString() 
	
	/// <summary>
	/// Allow this lookup type to be soft deleted in the future, but retaining referential integrity
	/// </summary>
	public bool IsDeleted { get; set; }
}

This legacy tables reference the ProjectStatusTbl.ProjectStatusId PK, so the integer value rather than the nice string name is used as a reference.

The following test case shows though, that when EnumAsInt attribute is used on an enum value that is used as the PrimaryKey, the Insert and Select operations fail to take note of this, and try to query the value as a string instead. Is this a bug, or some behaviour that I still need to override?

My test case is modelled on the EnumTests.cs file found in the Ormlite project:

using System.Linq;
using NUnit.Framework;
using ServiceStack.DataAnnotations;

namespace ServiceStack.OrmLite.SqlServerTests
{
	public class EnumAsIntAsPkTests : OrmLiteTestBase
	{
		[Test]
		public void CanCreateTable()
		{
			OpenDbConnection().CreateTable<LookupTypeWithEnumAsIntAsPk>(true);
		}

		[Test]
		public void CanStoreEnumValue()
		{
			using (var con = OpenDbConnection())
			{
				con.CreateTable<LookupTypeWithEnumAsIntAsPk>(true);
				con.Save(new LookupTypeWithEnumAsIntAsPk { EnumAsIntAsPkId = LookupTypeEnum.Value1, EnumValueThatWouldGoInAsString = SomeEnum.Value1 });
			}
		}

		[Test]
		public void CanGetEnumValue()
		{
			using (var con = OpenDbConnection())
			{
				con.CreateTable<LookupTypeWithEnumAsIntAsPk>(true);
				var obj = new LookupTypeWithEnumAsIntAsPk { EnumAsIntAsPkId = LookupTypeEnum.Value1, EnumValueThatWouldGoInAsString = SomeEnum.Value1 };
				con.Save(obj);
				var target = con.SingleById<LookupTypeWithEnumAsIntAsPk>(obj.EnumAsIntAsPkId);
				Assert.AreEqual(obj.EnumAsIntAsPkId, target.EnumAsIntAsPkId);
				Assert.AreEqual(obj.EnumValueThatWouldGoInAsString, target.EnumValueThatWouldGoInAsString);
			}
		}
	}

	
	/// <summary>
	/// We store all enum values in the db for 
	/// </summary>
	[EnumAsInt]
	public enum LookupTypeEnum
	{
		Value1 = 0,
		Value2 = 1,
		Value3 = 2
	}


	[Alias("LookupTypeWithEnumAsIntAsPk")]
	public class LookupTypeWithEnumAsIntAsPk
	{
		[PrimaryKey]
		public LookupTypeEnum EnumAsIntAsPkId { get; set; }


		public SomeEnum EnumValueThatWouldGoInAsString { get; set; }

		/// <summary>
		/// Allow this lookup type to be soft deleted in the future, but retaining referential integrity
		/// </summary>
		public bool IsDeleted { get; set; }
	}
}

mythz · February 7, 2018, 1:20am

Thanks for the repro, this should now be fixed from this commit.

This fix is now available from v5.0.3 that’s now available on MyGet.

daleholborow · February 8, 2018, 8:24am

Is there any way to see which myget packages have been updated? I have cleared my nuget cache repeatedly over the last two days and each time I try to download 5.0.3, I still get the error.

However, when I downloaded the Ormlite github repo , I build that and AM able to get the tests to work? So, can I confirm that the myget packages are indeed up to date with the GIT repo files to include this change?

*Note: The tests where they are currently placed, in the Ormlite.Test project, actually pick up the :memory: sqlite db, and so they pass even when they shouldn’t I think. I created the same tests in the SQL Server test project, and will submit a pull request, if you like, to be a more secure test for future.

mythz · February 8, 2018, 8:40am

Are you clearing your NuGet packages cache with?

$ nuget locals all -clear

We already have this test case in EnumAsIntAsPkTests which I’ve tested passes in SQLite, SQL Server, MySql and PostgreSQL.

daleholborow · February 8, 2018, 9:04am

I was running it through the Nuget Package Manager window, but now running the cmd you note above, same issue.

daleholborow · February 8, 2018, 11:59am

So… I have no idea why, but I:

shut VS
cleared nugut from cmd prompt (again, both methods), deleted all /bin and /obj folders from my solution
made a new solution, downloaded 5.0.3, added test case and confirmed that EnumAsInt works
returned to my existing code base, upgrade to 5.0.3
enums seem to work now… I just can’t explain why.

Thanks for your help