Fluent NHibernate 3 table relation without primary and foreign keys

Question

Background Info

I have the following class that I want to map with NHibernate:

public class Player
{
    public virtual int Id { get; set; }
    public virtual Type Type { get; set; }
    public virtual string ScreenName { get; set; }
    public virtual bool Unsubscribed { get; set; }
}

On the database side, I have the following tables:

-- New table
Player (
    int Id
    int TypeId (not null) -- foreign-key to Type table
    string ScreenName (not null) -- can be an EmailAddress, but not necessarily
)
Type (
    int Id
    string Name -- "Email", "Facebook", etc
)

The Player's ScreenName can be an email address ("[email protected]"), a Twitter screen-name ("@FooBar"), a Skype screenname ("foo.bar"), or anything else like that. Mapping the first three properties of Player using Fluent NHibernate is easy enough:

public class PlayerMap : ClassMap<Player>
{
    public PlayerMap()
    {
        Id(x => x.Id);
        Map(x => x.ScreenName)
            .Not.Nullable();
        References(x => x.Type)
            .Column("TypeId")
    }
}

public class TypeMap : ClassMap<Type>
{
    public TypeMap()
    {
        Id(x => x.Id);
        Map(x => x.Name);
    }
}

But the Unsubscribed property is harder, because I have to get that information from two legacy tables that I can't change and that I must access in a read-only fashion (no inserts, updates, or deletes allowed):

-- Legacy tables, can't change
EmailAddress (
    int Id
    string EmailAddress (not null) -- "[email protected]"
)
Unsubscribed (
    int Id
    int EmailAddressId (not null) -- foreign key to EmailAddress table
)

Only email Players can be unsubscribed, so Players of other types will never have a row in either the EmailAddress nor the Unsubscribed table.

These are the classes the legacy tables:

public class EmailAddress
{
    public virtual int Id { get; set; }
    public virtual string Value { get; set; }
    public virtual IList<Unsubscription> Unsubscriptions{ get; set; }
}

public class Unsubscription
{
    public virtual int Id { get; set; }
    public virtual EmailAddress EmailAddress { get; set; }
}

And here are their Fluent mappings:

public class EmailAddressMap : ClassMap<EmailAddress>
{
    public EmailAddressMap()
    {
        ReadOnly();
        Id(x => x.Id);
        Map(x => x.Value)
            .Column("EmailAddress")
            .Not.Nullable();
        HasMany(x => x.Unsubscriptions)
            .KeyColumn("EmailAddressId");
    }
}

public class EmailOptOutMap : ClassMap<EmailOptOut>
{
    public EmailOptOutMap()
    {
        ReadOnly();
        Id(x => x.Id);
        References(x => x.EmailAddress)
            .Column("EmailAddressId");
    }
}

Problem

The problem I'm having is trying to get the unsubscribed information for email Players.

The only way I can relate the Unsubscribed table to the Player table is through the intermediary EmailAddress table, matching EmailAddress.EmailAddress to Player.AddressIdentifier, but I'm having trouble trying to figure out how to do this with Fluent NHibernate.

I've looked at Join for multiple tables, but all the examples I've found only deal with 2 tables, not three:

Denis Ivin Denis Ivin · Accepted Answer · 2012-02-10T22:44:47

One possible solution would be to use a formula for the read-only Unsubscribed property to populate its value dynamically based on the data stored in the legacy tables. You could map it as follows:

Map(x => x.Unsubscribed).Formula("(CASE WHEN EXISTS (SELECT EA.Id FROM EmailAddress EA INNER JOIN Unsubscribed ON EA.Id = Unsubscribed.EmailAddressId WHERE EA.EmailAddress = ScreenName) THEN 1 ELSE 0 END)").ReadOnly();

Of course, you can improve the select query further by adding a condition for TypeId to filter out non-email Players. Plus it allows you to get rid of the legacy classes & mappings, unless it's used somewhere else in the app.

Fluent NHibernate 3 table relation without primary and foreign keys

Background Info

Problem

2 Answers