CODEWITHSUNDEEP
servicestackforeign-key-relationshipormlite-servicestack
Dale Holborow
Dale Holborow

Reputation: 580

ServiceStack Ormlite: Circular reference between parent and child tables prevents foreign key creation

We are experimenting with a shift from EF6 to ServiceStack.OrmLite and are having trouble during table creation in the situation where a Parent table contains a reference to a (default/active) Child table. We have a 1:many Parent:Child relationship, where Parent has an additional optional reference to a particular child. The database tables produced would each contain foreign keys to eachother, e.g.

Parent table:
Id  (int)   | Name (varchar)    | ActiveChildId (int null FK)

Child table:
Id  (int)   | Description (varchar) | ParentId (int FK)

We are trying to mimic the navigation property functionality that EF provides, but not sure if this is possible?

For example, if we declare the following classes, where

public class Parent
{
    public int Id {get;set;}
    public string Name {get;set;}

    // the item currently published, modelled like an EF navigation property
    [Reference]
    public Child ActiveChild {get;set;}     

    [References(typeof(Child))]
    public int? ActiveChildId { get; set; }

    // all items mapped to this Parent
    [Reference]
    public List<Child> AllChildren {get;set;}   
}

public class Child
{
    public int Id {get;set;}
    public string Description {get;set;}

    [References(typeof(Parent))]
    public int ParentId {get;set;}

    [Reference]
    public Parent MyParent {get;set;}
}

Now, when we use the Ormlite table creation, such as:

using (var db = DbFactory.Open())
{
    db.CreateTableIfNotExists<Parent>();
    db.CreateTableIfNotExists<Child>();
}

we get an error like:

System.Data.SqlClient.SqlException (0x80131904): Foreign key 'FK_Parent_Child_ActiveChildId' references invalid table 'Child'.
Could not create constraint or index. See previous errors.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
[etc etc]

Basically, it seems that creating the Parent table fails because the Child table doesnt yet exist and so the foreign key cannot be created, and similarly reversing the table creation order fails because Child cannot be created without Parent?

Are we able to model our classes this way in OrmLite, or if not, what would be the suggested/recommended structure to capture this type of relationship?

Thanks

Upvotes: 2

Views: 884

Answers (1)

mythz
mythz

Reputation: 143284

OrmLite is a code-first ORM where it's POCO's are a 1:1 mapping of the underlying table. You can't define cyclical relationships like this in OrmLite as you can't define cyclical relationships like this in SQL without resorting to a multi-step table creation where foreign key constraints are added after all tables are created. I'd recommend against defining your tables with cyclical foreign keys, you can always add them later after the tables are created.

Using the [References(Type)] attribute creates a foreign key, but this isn't required for OrmLite POCO References which can use implicit naming convention for defining implied relationships, e.g:

public class Parent
{
    [AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; }

    [Reference]
    public Child ActiveChild { get; set; }

    public int? ActiveChildId { get; set; }

    [Reference]
    public List<Child> AllChildren { get; set; }
}

public class Child
{
    [AutoIncrement]
    public int Id { get; set; }

    public int ParentId { get; set; }

    public string Description { get; set; }
}

Which you can then create and use as normal:

db.DropAndCreateTable<Parent>();
db.DropAndCreateTable<Child>();

var parent = new Parent
{
    Name = "Parent",
    ActiveChild = new Child {  Description = "Active" },
    AllChildren = new List<Child>
    {
        new Child { Description = "Child 1" },
        new Child { Description = "Child 2" },
    }
};

db.Save(parent, references:true);

var dbParent = db.LoadSelect<Parent>();
dbParent.PrintDump();

Upvotes: 4

Related Questions