CODEWITHSUNDEEP
sqlitesingle-table-inheritancesqlite-netsqlite-net-extensions
asp_net
asp_net

Reputation: 3597

Joining two records with the same key

Let's say I have these two objects:

public class Employee : Person
{
    public string Something { get; set; }
}

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Now I have to deal with a given database schema, that looks like follows:

CREATE TABLE employee (
    id INTEGER PRIMARY KEY,
    something TEXT
);

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    firstname TEXT,
    lastname TEXT
);

(Both share the same id, so they can be joined together to a single record in a query.)

Question: Do I have a chance to get this resolved by sqlite-net with SQLite-Net Extensions? And if so: how would that work?

Upvotes: 0

Views: 111

Answers (1)

redent84
redent84

Reputation: 19239

SQLite.Net mapping flattens the object hierarchy into a single table, so if you let SQLite.Net create the tables you will obtain these completely unrelated tables:

CREATE TABLE employee (
    id INTEGER PRIMARY KEY,
    firstname TEXT,
    lastname TEXT,
    something TEXT
);

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    firstname TEXT,
    lastname TEXT
);

Changes to the latter won't affect the previous and vice versa. You will be able to use custom queries for reading and writing, but you won't be able to to use any of the utility methods of SQLite.Net with that schema and those classes.

Depending on your needs, you could add intermediate classes that will be used in database operations and then map them to model classes. Something like this:

[Table("employee")]
public class DBEmployee
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Something { get; set; }
}

[Table("person")]
public class DBPerson
{
    [AutoIncrement, PrimaryKey]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class DBService {
    static SQLiteConnection _connection;

    public void SaveEmployee(Employee employee) {
        var dbPerson = new DBPerson { 
            FirstName = employee.FirstName,
            LastName = employee.LastName
        };
        _connection.Insert(dbPerson);
        var dbEmployee = new DBEmployee {
            Id = dbPerson.Id,
            Something = employee.Something
        };
        _connection.Insert(dbEmployee);
    }

    public Employee GetEmployee(int employeeId) {
        var dbEmployee = _connection.Get<DBEmployee>(employeeId);
        var dbPerson = _connection.Get<DBPerson>(employeeId);
        return new Employee {
            Id = employeeId,
            Something = dbEmployee.Something,
            FirstName = dbPerson.FirstName,
            LastName = dbPerson.LastName
        };
    }
}

You could use AutoMapper or equivalent to make mapping code more readable and maintainable.

Upvotes: 1

Related Questions