[Solved] From existing database, mapping one column to multiple properties based on second column

[Musashi]

In a few tables in an existing database, I have a foreign key column and a column that tells me what table that foreign key points to.

Ex. Table: Address.
Columns: PRIMARYKEY int pk, NAME nvarchar null, LINKFK int null, LINKTABLE nvarchar null

Table: Company
Columns: PRIMARYKEY int pk, NAME nvarchar null

Table: Person
Columns: PRIMARYKEY int pk, NAME nvarchar null

Address.LINKFK can be either a Company.PRIMARYKEY or Person.PRIMARYKEY, depending on what Address.LINKTABLE is ("Company" or "Person").

public class Address
{
    public int PRIMARYKEY { get; set; }
    public string NAME { get; set; }
    public int? LINKFK { get; set; }
    public string LINKTABLE { get; set; }

    public Company Company { get; set; }
    public Person Person { get; set; }
}

public class Company
{
    public int PRIMARYKEY { get; set; }
    public string NAME { get; set; }
}

public class Person
{
    public int PRIMARYKEY { get; set; }
    public string NAME { get; set; }
}

Currently I map LINKFK as an association to both Company and Person like this:

MappingConfiguration<Address> config;
config.HasAssociation(x => x.Company).ToColumn(x => x.LINKFK);
config.HasAssociation(x => x.Person).ToColumn(x => x.LINKFK);

Since PRIMARYKEY on any given table *should* be unique (though not guaranteed), I'm not currently checking LINKTABLE. (LINKTABLE == "Company" or LINKTABLE == "Person")

But the SQL produced looks like this:

SELECT *
FROM Address
JOIN Company ON Address.LINKFK = Company.PRIMARYKEY
JOIN Person ON Address.LINKFK = Person.PRIMARYKEY

Being an INNER JOIN, I get nothing.

What I'd rather see is this:

SELECT *
FROM Address
LEFT JOIN Company ON Address.LINKFK = Company.PRIMARYKEY
LEFT JOIN Person ON Address.LINKFK = Person.PRIMARYKEY

Currently, my queries look kind of like this

var query =
    from a in Context.Address
    where (a.Company == null ? a.Person.Name : a.Company.Name).Contains("cheese")
    select a;

Is there a way to force it to do a LEFT JOIN instead of a JOIN?
Or is there a different way to set up the association to achieve what I need?

[Doroteya]

Hi Musashi,

You could paraphrase the query like this:

var query = from a in dbContext.Addresses
    join c in dbContext.Companies on a.LINKFK equals c.PRIMARYKEY into ca
    from b in ca.DefaultIfEmpty()
    join p in dbContext.People on a.LINKFK equals p.PRIMARYKEY into cpa
    from z in cpa.DefaultIfEmpty()
    where (a.Company == null ? b.NAME : z.NAME).Contains("cheese")
    select a;

Additionally, you can review the approach (and the sample) suggested in this forum post.

I hope this helps.

Regards,
Doroteya
Telerik