where clause in join mapping fluent nhibernate

Question

I have the following Nhiberhate fluent mapping, but i cant figure out how to get a where clause in to one of the joins. I only want to join the operations table where OperationKind == "TASK". Any idea on how to acheieve that? Without linq outside the mapping.

The sql i want would look something like

SELECT X.PHASE_S , PA.INTERVAL, PA.BOR, OP.DESCRIPTION 
FROM GG.PROJ_PHASE_X X 
inner join GG.PHASE PA 
on X.PHASE_S=PA.PHASE_S 
inner join GG.OPERATIONS OP
ON X.PHASE_S = OP.PHASE_S
WHERE OP.OPERATION_KIND = 'TASK';

Mapping:

public class MySectionMap : ClassMap<MySectionEntity>
{
    public MySectionMap()
    {
        Schema("GG");
        Table("PROJ_PHASE_X");
        Id(x => x.PhaseS, "PHASE_S").GeneratedBy.TriggerIdentity();
        References(x => x.Project).Column("PROJECT_S").Cascade.None().Not.LazyLoad().Not.Nullable();
        Join("PHASE", m =>
                          {
                              m.Schema("GG");
                              m.Fetch.Join();
                              m.KeyColumn("PHASE_S");
                              m.Map(t => t.Interval).Column("INTERVAL");
                              m.Map(t => t.BorS).Column("BOR_S");
                          });
        Join("OPERATIONS", m => // ONLY JOIN WHERE OPERATION_KIND EQUALS TASK?
                               {
                                   m.Schema("GG");
                                   m.Fetch.Join();
                                   m.KeyColumn("PHASE_S");
                                   m.Map(t => t.Description).Column("DESCRIPTION");
                                   m.Map(t => t.OperationType).Column("OPERATION_KIND");
                               });

    }
}

Answer 1

the question is old, but anyway. I had the same problem (legacy db, not allowed to change the schema). If it is in the where clause nhibernate uses the alias of the first table which throws an error.

My solution was to define a view for the join, and then use the view as a joined table. The column that could not go in the where clause is also mapped (so that the inserts work as well). In your case the view could be:

CREATE VIEW FilteredOperations
    SELECT OP.PHASE_S, OP.DESCRIPTION, OP.OPERATION_KIND
    FROM  GG.OPERATIONS OP
    WHERE OP.OPERATION_KIND = 'TASK';

My Fluent mappings:

public class Agency : EntityBase
    {
        public const string AgencyRoleId = "F1776564-1CA0-11d5-A70C-00A0120802D7";

        public Agency()
        {
            CountryId = "de";
            LanguageId = "de";
        }

        public virtual string AgencyId { get; set; }
        public virtual string Name { get; set; }
        public virtual string UserDefinedName { get; set; }
        public virtual string LanguageId { get; set; }
        public virtual string CountryId { get; set; }
        public virtual string RoleId
        {
            get
            {
                return AgencyRoleId;
            }
            set {}
        }
    }

public AgencyMap()
        {
            Table("mgmt_location_102");
            Id(x => x.AgencyId, "LocationId").GeneratedBy.UuidHex("D");
            Map(x => x.CountryId, "CountryId");
            Map(x => x.LanguageId, "LanguageId");
            Map(x => x.Name,"CommonName");

            Join("mgmt_agency_ext", x=>
                                        {
                                            x.Optional();
                                            x.KeyColumn("AgencyLocationId");
                                            x.Map(y => y.UserDefinedName, "AgencyUserDefinedName");

                                        });

            Join("scs2.vAgencyRoles", x =>
            {
                x.KeyColumn("LocationId");
                x.Map(y => y.RoleId, "RoleId").Not.Update();
            });
        }

Answer 2

Join is for simple merge of rows from multiple tables into one entity. What you're trying to achieve is a bit more complex - you're trying to filter some values out and in SQL you're doing it using where clause, not using join, too.

You have two options - either create a view in the database and map your entity to the view (it is done exactly the same way as for tables). Or second option, more code-side is to add restriction corresponding to your where clause on mapping level:

public MySectionMap()
{
    Schema("GG");
    Table("PROJ_PHASE_X");

    Join("PHASE", m => //...
    Join("OPERATIONS", //...

    Where("OPERATION_KIND = 'TASK'");
}