CODEWITHSUNDEEP
c#orm
Andy
Andy

Reputation: 1043

One-to-one relation in Shaolinq

I am using Shaolinq library for database usage and I have problem with one-to-one relation. I have following database schema:

create table A (
    Id INT NOT NULL,
    PRIMARY KEY (`Id`)
);
create  table B (
    Id INT NOT NULL,
    PRIMARY KEY (`Id`),
    CONSTRAINT `fk_a_b_id` FOREIGN KEY (`Id`) REFERENCES `A` (`Id`)
);

basically those two tables contains info about same things, just different data. That's how someone has designed it, I cannot change it.

However, in the code I would like to have all those data in one object. I have been trying with BackReference, but no luck:

[DataAccessObject]
public abstract class A : DataAccessObject<uint>
{
    [AutoIncrement]
    [PersistedMember]
    public abstract uint Id { get; set; }

    [RelatedDataAccessObjects]
    public abstract RelatedDataAccessObjects<B> Bs { get; }
}

[DataAccessObject]
public abstract class B : DataAccessObject
{
    [BackReference(Name = "", PrefixName = "", SuffixName = "")]
    public abstract A Id { get; set; }

    [PersistedMember]
    public abstract string MoreData { get; set; }
}

This created somehow similar schema (obviously Id in B is not primary key now), but after I fill data in DB, Bs property in A is not filled with references. Simply puting abstract property of type B in A also doesn't seem to work since same column is foreign key and primary key at once (Id).

Any advice is welcome.

Upvotes: 0

Views: 79

Answers (1)

tumtumtum
tumtumtum

Reputation: 1162

Do you need to access a collection of Bs from As? It seems to me that B is the "super class" that encompasses data from both A and B. If that's the case, then you can just make A the primary key of B.

[DataAccessObject]
public abstract class A : DataAccessObject<uint>
{
    [PersistedMember(Name = "Id")]
    public abstract override uint Id { get; set; }

    [PersistedMember]
    public abstract string Data { get; set; }
}

[DataAccessObject]
public abstract class B : DataAccessObject<A>
{
    [PersistedMember(Name = "Id", PrefixName = "")]
    public abstract override A Id { get; set; }

    [PersistedMember]
    public abstract string MoreData { get; set; }
}

Note that I've overridden the Id properties to get the column names matching the SQL. The defaults have meant column names: AId and BId

Because the primary key is an object it's known in Shaolinq terminology as a "complex primary key". You can use the deflated references feature to retrieve objects just be their primary key without a roundtrip to the database (more here: https://github.com/tumtumtum/Shaolinq/wiki/Deflated-References)

You can query for a specific value of B:

// Need to define primary key using an anonymous object because it's not an uint but rather an A that contains an uint.
model.Bs.GetByPrimaryKey(new { Id = 100 } );

You can also query for a specific value of B using a deflated reference:

model.Bs.GetByPrimaryKey(model.As.GetReference(100));

You can also use references within normal LINQ queries:

model.Bs.Where(c => c.Id == model.As.GetReference(100) || c.Id == model.As.GetReference(101)).ToList();

You can directly access nested/complex primary keys within the query too:

model.Bs.Where(c => c.Id.Id == 100 || c.Id.Id == 101).ToList();

Note that the above three queries don't need to perform an implicit join be cause the complex primary key is flattened out and stored on the table B.

When you get back B, you can access B.Id.Id without a round-trip query but if you access B.Id.Data then that will cause B.Id to automatically inflate with a hidden round-trip to the database. To avoid this, you can use Include to automatically inflate B.Id with the first query.

model.Bs.Include(c => c.Id).Single(c => c.Id == model.As.GetReference(100));

Or more succinctly:

model.Bs.Include(c => c.Id).Single(c => c.Id.Id == 100);

Upvotes: 1

Related Questions