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

2 posts, 0 answers
  1. Musashi
    Musashi avatar
    20 posts
    Member since:
    Jan 2014

    Posted 08 Jun Link to this post

    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?

  2. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 14 Jun Link to this post

    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.
  3. DevCraft banner
Back to Top