How do i reference objects directly in the classes using ORMlite?

Question

I am trying to map the attributes of a class directly to the corresponding object but I don't know how.

This is the database definition:

CREATE TABLE "Projects" (
    "project_id"    INTEGER NOT NULL UNIQUE,
    "project_description"   VARCHAR(255),
    PRIMARY KEY("project_id" AUTOINCREMENT)
);

CREATE TABLE "Employee" (
    "employee_id"   INTEGER NOT NULL UNIQUE,
    "employee_name" VARCHAR(255),
    "employee_assignedProject"  INT,
    FOREIGN KEY("employee_assignedProject") REFERENCES "Projects"("project_id"),
    PRIMARY KEY("employee_id" AUTOINCREMENT)
);

This are the corresponding c# classes:

[Table("Projects")]
public class Projects
{
    [PrimaryKey]
    [AutoIncrement]
    [Alias("project_id")]
    public int ProjectId { get; set; }

    [Alias("project_description")]
    public string ProjectDescription { get; set; }
}

[Table("Employee")]
public class Employee
{
    [PrimaryKey]
    [AutoIncrement]
    [Alias("employee_id")]
    public int EmployeeId { get; set; }

    [Alias("employee_name")]
    public string EmployeeName { get; set; }

    [Reference]
    [Alias("employee_assignedProject")]
    [ServiceStack.DataAnnotations.ForeignKey(typeof(Projects))]
    public Projects EmployeeAssignedProject { get; set; }
}

As you can see I am trying to get the Project Object in the Employee class as an attribute in order to be able to use it for processing later on.

The problem however is that when I use this code I always get a null reference for the EmployeeAssignedProject attribute:

                // Query employees
                List<Employee> employees1 = dbconn.Select<Employee>();

I have also found that the generated Project object has always zero as the ID:

            var newProject = new Projects
            {
                ProjectDescription = "New Project!"
            };

            // Insert the new project into the database
            dbconn.Insert(newProject);

            // The newProject object has always "0" as an ID even though the ID in the database is different
            int newProjectId = newProject.ProjectId;

I am new to ORMlite and have no diea what the problem could be.

Any help is appreciated.

Answer 1

You'll want to read OrmLite Documentation as there's a lot of things wrong with your code example.

• [Table] is not a OrmLite attribute and has no effect
• [Alias] is what's used to specify a different RDBMS table name when needed
• [ForeignKey] is for defining the foreign key reference id, not the complex type table it references
• [Alias] attribute is only used on persisted properties e.g. the int Foreign Key not the complex type [Reference] property
• Read Insert records with AutoIncrement Ids docs to find out how to access auto incremented ids

Have a look at the [ForeignKey] attributes in OrmLite's Getting Started docs for examples.

Please refer to Reference Support docs for how to define complex type references.

These references are only loaded with Load* APIs, e.g:

var employees = db.LoadSelect<Employee>();

Basically, you should go through and read all OrmLite docs (https://docs.servicestack.net/ormlite/) as your code examples show a lot of incorrect usage and assumptions.