servicestack ormlite and foreign keys to same table

Question

I have a table of links, and some links will be child links, referencing the parent links ID however i can not get my head around servicestack ormlite and populating a property of children, will all the child links when getting a list of all links.

Here is my model:

public partial class Navigation
{
   [Alias("Id"), AutoIncrement]
   public int Id  { get; set; }

   [Alias("ParentId")]
   [Display( Name = "ParentId")]
   [References(typeof(Navigation))]
   public int? ParentId  { get; set; }

   [Alias("LinkText")]
   [StringLength(50, ErrorMessage = " Must be no more than 50 characters long!")]
   [Display( Name = "LinkText")]
   public string LinkText  { get; set; }

   [Alias("Action")]
   [StringLength(50, ErrorMessage = " Must be no more than 50 characters long!")]
   [Display( Name = "Action")]
   public string Action  { get; set; }

   [Alias("Controller")]
   [StringLength(50, ErrorMessage = " Must be no more than 50 characters long!")]
   [Display( Name = "Controller")]
   public string Controller  { get; set; }

   [Alias("Area")]
   [StringLength(50, ErrorMessage = " Must be no more than 50 characters long!")]
   [Display( Name = "Area")]
   public string Area  { get; set; }

   [Alias("Visible")]
   [Display( Name = "Visible"),Required(ErrorMessage = " is required" )]
   public bool Visible  { get; set; }

   [Alias("Sequence")]
   [Display( Name = "Sequence")]
   public int? Sequence  { get; set; }

   [ForeignKey(typeof(Navigation))]
   public virtual ICollection<Navigation> Children { get; set; }
}

any ideas ?

Moslem Ben Dhaou · Answer 1 · 2013-01-29T23:51:24.567

You can do that using inheritance. The parent class will contain a reference to the child class. I had to use it to get which user have created each user. Here is a sample:

public class UserCommon
{
    [References(typeof(User))] // Self reference workaround for User ;)
    public Guid CreatedBy { get; set; }
}

public class User : UserCommon
{
    public Guid Uid { get; set; }
    public String Username { get; set; }
    public String Password { get; set; }
}

Something you need to pay attention to is to include the Id in the child class not the parent. The table that will be generated is as follow. The foreign key is a self reference

Generated Table

Getting the list of children should be as easy as a simple LINQ query that will fetch all children for a certain parent Guid. CreatedBy is also a property of User becuase of inheritance.

db.Select<User>(q => q.CreatedBy == '734FD814-024D-4795-AFD0-34FECF89A13A');
// Just a sample Guid, you should be able to select
// the Guid you need and insert it here.

why would you bring live a post from last year? not even put a bounty on this, had to drop ormlite and use something else to deal with it, I dont think i would be re-opening a completed project to re-do all the work though to be honest, nice solution just shame its 3 months later :-) — davethecoder, Jan 31 '13 at 15:59
Well, I just started with OrmLite and as you know StackOverflow is a community. My experience could be beneficent for people who looks for the same thing in the future. — Moslem Ben Dhaou, Feb 01 '13 at 00:37

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

Tables in OrmLite are strictly a 1:1 mapping with the underlying db tables.

This means all complex type properties are blobbed into a db text field with the property name, they're never used to auto-map to child relations as you're expecting to do here.

Here's an early answer that shows how you could map many to many relations with OrmLite.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 17 '12 at 17:11

mythz

141,670
29
246
390

is there a quick way I could make this happen, for example, creating my end results model and having that then populated as I would need it, the above is quite a major part of an existing project that uses entity framework at the moment, I'm trying to justify that ormlite will be able to deal with things like this, in the project without much trouble, don't really understand the link you sent me to really, I don't want to insert, just deliver the results. thanks – davethecoder Nov 17 '12 at 17:47
I don't have any time to do any of this myself, but you could do an extension method that behaved similarly to what you want, e.g. `model.Children()` that uses reflection to look at src + child tables and using convention to build the appropriate sql wanted. – mythz Nov 17 '12 at 18:30

servicestack ormlite and foreign keys to same table

2 Answers2