Can I dynamically populate relational data using one foreign key column that maps to two different tables in ef 4.1?

Question

I have a c# .net 4 wcf ria service exposed to a silverlight 4 client, using linq to ef code first.

DbContext:

public class TableA
{
    public long TableAId {get; set;}
    public string LinkedTableType {get; set;}
    public long LinkedTableId {get; set;}

    public virtual TableB TableB { get; set; }
    public virtual TableC TableC { get; set; }
}

public class TableB
{
    public long TableBId {get; set;}
    public string TableB_1 {get; set;}

    public long ChildTableAId {get; set;}
    [ForeignKey("ChildTableAId")]
    public virtual TableA TableA {get; set;}
}

public class TableC
{
    public long TableCId {get; set;}
    public string TableC_1 {get; set;}
    public string TableC_2 {get; set;}
}

TableA.LinkedTableId is a foregin key that relates to either TableB or TableC by TableBId or TableCId respectively, depending on the value of LinkedTableType. For example, LinkedTableType can be "B" or "C".

I read the question here but I want to prevent an ef circular reference from TableA -> TableB -> TableA -> TableB. Note that this cannot happen with real data; If a row in TableA points to a row in TableB, the row in TableA that TableB points to by ChildTableAId should never point back to TableB, only to TableC.

Domain Service:

[MetadataTypeAttribute(typeof(TableA.TableAMetadata))]
public partial class TableA
{
    internal sealed class TableAMetadata
        {
            private TableAMetadata()
            {
            }

            public long TableAId { get; set; }

            [Include]
            public TableB TableB { get; set; }

            [Include]
            public TableC TableC { get; set; }
        }
}

public partial class TableB
{
    internal sealed class TableBMetadata
    {
        private TableBMetadata()
        {
        }

        public long TableBId { get; set; }

        public string TableB_1 { get; set; }

        public long ChildTableAId { get; set; }

        [Include]
        public TableA TableA { get; set; }
    }
}

[EnableClientAccess]
public partial class Sample1DomainService : LinqToEntitiesDomainService<Sample1Entities>
{
    public IQueryable<TableA> GetTableA()
    {
        return this.ObjectContext.TableA
            .Include("TableB");
            .Include("TableC");
    }
}

I can compile the domain service (Sample1.RIAServices.Web) without errors, but the wcf ria service silverlight library (Sample1.RIAServices) fails with the following compiler error:

The associated metadata type for type 'Sample1.RIAServices.Web.TableA' contains the following unknown properties or fields: TableB, TableC. Please make sure that the names of these members match the names of the properties on the main type.

How do I populate TableB or TableC on the server side so that they are directly available on the wcf ria client? Is this possible by specifying a custom relational mapping in the overriden OnModelCreating method on the DbContext and\or specifying a custom linq query in the wcf ria domain service? If so, how?

For example, this is what I would expect on the client side:

IEnumerable<TableA> tableA = MyDbContext.TableA;

foreach (TableA _tableA in tableA)
{
    if (_tableA.LinkedTableType == "B")
    {
        Assert.IsNotNull(_tableA.TableB);
        Assert.IsNull(_tableA.TableC);
    }
    else if (_tableA.LinkedTableType == "C")
    {
        Assert.IsNotNull(_tableA.TableC);
        Assert.IsNull(_tableA.TableB);
    }
    else
    {
        Assert.IsNull(_tableA.TableB);
        Assert.IsNull(_tableA.TableC);
    }
}

I want to do this on the server side to support paging through records in TableA and not have to load the entire TableB and TableC to ensure that the foreign tables exists.

If there a better database design? Note that in my real database design there are about 10 columns in TableB and 6 columns in TableC. The columns in TableB and TableC are not similar. I'd like to keep the design flexible enough so that I can easily add support for more "linked" tables (ex. TableD, TableE, TableF, etc.)

Thank you.

Answer 1

I actually resolved my problem by doing the following:
1. delete LinkedTableId
2. add nullable foreign keys TableA.TableBId and TableA.TableCId

This post helped me lot. :)