Create Database table from model with enum using Umbraco.Core.Persistence

Question

Want to create this table on application start if it does not exist.

Code:

public class Database : ApplicationEventHandler
{
    protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
    {
        var db = applicationContext.DatabaseContext.Database;

        //Cant add this table due to the ENUM
        if (!db.TableExist("FormData"))
        {
            db.CreateTable<FormData>(false);
        }
    }
}

Model:

[PrimaryKey("Id")]
public class FormData
{
    [PrimaryKeyColumn(AutoIncrement = true, IdentitySeed = 1)]
    public int Id { get; set; }

    [NullSetting(NullSetting = NullSettings.NotNull)]
    public FormType Type { get; set; }

    [NullSetting(NullSetting = NullSettings.NotNull)]
    public string Data { get; set; }

    [NullSetting(NullSetting = NullSettings.NotNull)]
    public DateTime Date { get; set; }
}

Error message:

[InvalidOperationException: Sequence contains no matching element] System.Linq.Enumerable.First(IEnumerable1 source, Func2 predicate) +415 Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase1.FormatType(ColumnDefinition column) +1225 Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase1.Format(ColumnDefinition column) +155 Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase1.Format(IEnumerable1 columns) +144 Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase`1.Format(TableDefinition table) +131 Umbraco.Core.Persistence.PetaPocoExtensions.CreateTable(Database db, Boolean overwrite, Type modelType) +161 Umbraco.Core.Persistence.PetaPocoExtensions.CreateTable(Database db, Boolean overwrite) +121

Looking at the error I dont think there is a solution to this without updating the core but here is to hoping you guys can help

score 9 · Accepted Answer · answered Sep 02 '15 at 13:08

Working on the answer that @Ryios gave, I think that something like this is good:

/// <summary>
/// Don't use this to get or set.
/// This must however be kept as public or db.CreateTable()
/// will not insert this field into the database.
/// </summary>
[NullSetting(NullSetting = NullSettings.NotNull)]
[Column("type")]
public int _type { get; set; }

/// <summary>
/// This field is ignored by db.CreateTable().
/// </summary>
[Ignore]
public FormType Type
{
    get
    {
        return (FormType)_type;
    }
    set
    {
        _type = (int)value;
    }
}

In the code, Type should be used rather than _type so that the enum can be benefitted from. _type is only present as the field that is inserted into the database table.

Yeah, that's a bit cleaner, I like it. However PetaPoco v4.0+ supports private/protected members so you don't need to make _Type public if you are using the newer versions. — Ryan Mann, Sep 02 '15 at 17:42

score 3 · Answer 2 · answered Jul 17 '15 at 18:57

Without modifying PetaPoco or using a different fork, you can use a solution like this,

[Column(type), NullSetting(NullSetting = NullSettings.NotNull)]
public int TypeAsInt { get; set; }

[Ignore]
public FormType TypeAsEnum { get { return (FormType)TypeAsInt; } }

Peta poco will Ignore properties tagged with Ignore, meaning it won't try to use them when creating a table, or selecting a result. Instead TypeAsInt will get created in the table as type of type int.

Then in your code you can use TypeAsEnum any time you want the FormType version which is cast from TypeAsInt.

score 1 · Answer 3 · answered Feb 23 '15 at 11:26

1

I believe the issue is because your class has a non-sql type on it, so it doesn't know what to do with it. In particular you have a property called "FormType" which is of type "FormType". PetaPoco has no idea what that is in terms of SQL Column types. If you want to auto-create the table, you'll need to make sure your class uses only property types that can be mapped to SQL Column types.

You may also run into a couple of issues, for example if you want a column to be nvarchar(max) there's no way to tell the persistence layer to do max, so you have to set it to nvarchar(x) where x is a number, and then run an alter statement to change the type to nvarchar(max) after you've created the table.

answered Feb 23 '15 at 11:26

Tim

4,217
1
15
21

If the field is an Enum, you'll need to store it as it's base type (presumably int?) rather than the Enum. I think there are forks of PetaPoco that will do Enums, but as you say, you'd have to update the core to make it work. – Tim Feb 23 '15 at 12:20
If I create the table manually the code runs and the data is saved as an integer, petapoco does all the mapping as you would hope. Just cant create the table from code. Looks like it's I may have to add another property of int just for column storage if I wish to do this. – Matt Feb 23 '15 at 12:39
Yup, that's correct. Peta Poco will map it OK, it just can't create it, as it doesn't know what to do with the Enum. the table creation features for PetaPoco are very simplistic sadly! – Tim Feb 24 '15 at 10:18

Create Database table from model with enum using Umbraco.Core.Persistence

3 Answers3