This is a migrated thread and some comments may be shown as answers.

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

1 Answer 54 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Musashi
Top achievements
Rank 1
Musashi asked on 08 Jun 2016, 07:10 PM

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?

1 Answer, 1 is accepted

Sort by
0
Doroteya
Telerik team
answered on 14 Jun 2016, 02:33 PM
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
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Tags
Development (API, general questions)
Asked by
Musashi
Top achievements
Rank 1
Answers by
Doroteya
Telerik team
Share this question
or