Table with PK as FK

Topics: User Forum
Nov 6, 2007 at 12:49 PM
Hi folks,

I'm trying to figure out how to have an PK which is also an FK. In the Relations sample the PK is an Identity (Id) and the FK is a second column holding the FK. The HasOne works ok if the that has being previsouly inserted, but I cannot save a new record in this case.

Thanks for any help. Below is the sample I've tried.


Tables:
Person has two columns, Id and Name. Id is PK and set as Identiy.
PCs has also two columns, Id an Name, ID is PK but is also an FK from Person.Id.

 class Program
    {
        static void Main(string[] args)
        {
            try
            {
                Person p = Person.New();
                p.Name = "Eric";
                p.Save();
                PersonalComputer pc = PersonalComputer.New();
                pc.Id = p.Id;
                pc.Name = "HP";
                pc.Save();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
    }
 
    [DbTable("Person")]
    public abstract class Person : DbObjectModel<Person>
    {
        [Length(50)]
        public abstract string Name { get; set; }
 
        [HasOne]
        public abstract PersonalComputer PC { get; set; }
 
        public Person() { }
        public Person(string Name) { this.Name = Name; }
    }
 
    [DbTable("PCs")]
    public abstract class PersonalComputer : DbObjectModel<PersonalComputer>
    {
        [Length(50)]
        public abstract string Name { get; set; }
 
        [BelongsTo, DbColumn("Id")]
        public abstract Person Owner { get; set; }
 
        public PersonalComputer() { }
        public PersonalComputer(string Name) { this.Name = Name; }
    }
Coordinator
Nov 6, 2007 at 1:48 PM
Edited Nov 6, 2007 at 1:51 PM
To do that, the table should be:
  • Person has two columns, Id and Name. Id is PK and set as Identity.
  • PCs has three columns, Id, Name and Person_Id, ID is PK and set as Identity, Person_Id is FK from Person.Id.
And, don't set Id field by yourself!

To insert the new object, it wiil be:

class Program
{
    static void Main(string[] args)
    {
        try
        {
            Person p = Person.New();
            p.Name = "Eric";
            PersonalComputer pc = PersonalComputer.New();
            pc.Name = "HP";
            p.PC = pc;
            p.Save(); // save p and pc
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }
    }
}
 
public abstract class Person : DbObjectModel<Person>
{
    [Length(50)] public abstract string Name { get; set; }
    [HasOne] public abstract PersonalComputer PC { get; set; }
}
 
[DbTable("PCs")]
public abstract class PersonalComputer : DbObjectModel<PersonalComputer>
{
    [Length(50)] public abstract string Name { get; set; }
    [BelongsTo] public abstract Person Owner { get; set; }
}
Coordinator
Nov 7, 2007 at 1:17 AM
If you real like "an PK which is also an FK", the following code can do that, and the tables are the same you mentioned:

class Program
{
    static void Main(string[] args)
    {
        try
        {
            DbEntry.ExecuteTransaction(delegete() // start a transaction
            {
                Person p = DynamicObject.NewObject<Person>();
                p.Name = "Eric";
                DbEntry.Save(p); // with an identity, the save function works.
                PersonalComputer pc = DynamicObject.NewObject<PersonalComputer>();
                pc.Id = p.Id;
                pc.Name = "HP";
                DbEntry.Context.Insert(pc); // without an identity, Insert or Update must be called by ourself.
            });
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }
    }
}
 
public abstract class Person : IDbObject
{
    [DbKey] public abstract long Id { get; set; }
    [Length(50)] public abstract string Name { get; set; }
}
 
[DbTable("PCs")]
public abstract class PersonalComputer : IDbObject
{
    [DbKey(IsDbGenerate = false)] public abstract long Id { get; set; }
    [Length(50)] public abstract string Name { get; set; }
}
It works, but It is not recommanded.