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?