Entity Splitting and How to Configure the Join

Question

I have two tables.

CREATE TABLE [dbo].[BusinessUnit](
    [BusinessUnitId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [CurrencyId] [int] NOT NULL);

CREATE TABLE [dbo].[Currency](
    [CurrencyId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](35) NOT NULL,
    [Country] [nvarchar](50) NOT NULL,
    [Code] [nvarchar](50) NOT NULL);

I have an Entity

public class BusinessUnit
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CurrencyId { get; set; }
    public string CurrencyCode { get; set; }
}

I want to use Entity Splitting to pull in the dbo.Currency.Code field. My mapping isn't working. Here is the mapping.

class BusinessUnitMap : EntityTypeConfiguration<BusinessUnit>
{
    public BusinessUnitMap()
    {
        Property(bu => bu.Id).HasColumnName("BusinessUnitId");       
        ToTable("BusinessUnit");
        Map(map =>
        {
            map.Property(bu => bu.CurrencyId);
            map.Property(bu => bu.CurrencyCode).HasColumnName("Code");
            map.ToTable("Currency");
        });
    }
}

I have tried a few other variations of this. I can't figure out how to get the mapping to work. It keeps producing SQL joining on the BusinessUnitId. I need the join to occur on CurrencyId.

Is the relationship between `BusinessUnit` and `Currency` many-to-one? As in, can multiple `BusinessUnit`s have the same `Currency`? — jjj, May 06 '15 at 00:21
@jjj Yes. The BusinessUnit's CurrencyId field can be set to any value independent of the other BusinessUnit records. — Price Jones, May 06 '15 at 11:51
I don't know if entity splitting would make sense, then. If you change `CurrencyCode` on a `BusinessUnit`, what would that mean for other `BusinessUnit`s with the same `Currency`? — jjj, May 06 '15 at 17:28
Maybe look at http://stackoverflow.com/questions/24775446/entity-splitting-for-one-to-many-table-relationships? — jjj, May 06 '15 at 17:28

score 1 · Accepted Answer · answered May 06 '15 at 17:41

For your case, I don't think you can use entity splitting since you have a many-to-one relationship. You might be able to do something like this:

public class BusinessUnit
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual Currency Currency { get; set; }
    public string CurrencyCode { get { return Currency.Code; } }
}

public class Currency
{
    public string Code { get; set; }
}

or with the same set-up above, a DTO:

public class BusinessUnitDTO
{
    public string Name { get; set; }
    public string CurrencyCode { get; set; }
}

and an extension method:

public static IQueryable<BusinessUnitDTO> GetBusinessUnitDTO(this IQueryable<BusinessUnit> q)
{
    return q.Select(b => new BusinessUnitDTO
    {
        Name = b.Name,
        CurrencyCode = b.Currency.Code,
    };
}

or something to that effect if you want a single object with that set of data.

Entity Splitting and How to Configure the Join

1 Answers1