Left Join

[robertovaldesperez]

Hi @jagregory @LordJZ @gmalyshev @stuartcarnie @andrecarlucci I have this mapping: `using Guru.Model.Abstract; using Guru.Model.Account; using Guru.Utils.Validator; using Newtonsoft.Json; using NHibernate.Envers.Configuration.Attributes; using NHibernate.Validator.Constraints; using System; using System.Drawing;

namespace Guru.Model.Master { [JsonObject] [Audited] public class PortfolioDocument : Entity<long?> { public static readonly string TABLE = "driveby_documento";

    public virtual long? PortfolioId { get; set; }

    public virtual long? DevelopmentId { get; set; }

    public virtual long? DriveById { get; set; }

    public virtual long? AssetId { get; set; }

    public virtual long? UserId { get; set; }

    [NotNull]
    public virtual int? DocumentTypeId { get; set; }

    [NotNullNotEmpty]
    [Length(Max = 200)]
    [SafeHtml(WhiteListType.NONE)]
    public virtual string Name { get; set; }

    [NotNullNotEmpty]
    [Length(Max = 500)]
    [SafeHtml(WhiteListType.NONE)]
    public virtual string Path { get; set; }

    [NotNullNotEmpty]
    [Length(Min = 36, Max = 36)]
    [SafeHtml(WhiteListType.NONE)]
    public virtual string FileUuid { get; set; }

    [Length(Max = 200)]
    [SafeHtml(WhiteListType.NONE)]
    public virtual string Comments { get; set; }

    public virtual bool? Hidden { get; set; }

    public virtual int? Order { get; set; }

    public virtual DateTime? InDate { get; set; }

    [Length(Max = 200)]
    [SafeHtml(WhiteListType.NONE)]
    public virtual string Link { get; set; }

    [Length(Max = 10)]
    [SafeHtml(WhiteListType.NONE)]
    public virtual string Source { get; set; }

    public virtual string Level => AssetId != null ? "Asset" : "Development";

    [NotAudited]
    [JsonIgnore]
    public virtual Development Development { get; set; }

    [NotAudited]
    public virtual Asset Asset { get; set; }

    [NotAudited]
    public virtual User User { get; set; }

    [NotAudited]
    public virtual DocumentType DocumentType { get; set; }

    // No persist
    public virtual RotateFlipType RotateFlipType { get; set; }

    public class Properties
    {
        private Properties()
        {
        }

        public const string ID = "Id";
    }
}

public sealed class PortfolioDocumentMap : EntityMap<PortfolioDocument, long?>
{
    public PortfolioDocumentMap()
    {
        Table(PortfolioDocument.TABLE);
        //Cache.NonStrictReadWrite();

        Id(x => x.Id).Column("Id").GeneratedBy.Native();

        Map(x => x.PortfolioId).Column("ID_cartera").Nullable();
        Map(x => x.DevelopmentId).Column("ID_promocion").Nullable();
        Map(x => x.DriveById).Column("ID_driveby").Nullable();
        Map(x => x.AssetId).Column("ID_Inmueble").Nullable();
        Map(x => x.UserId).Column("ID_Usuario").Nullable();
        Map(x => x.DocumentTypeId).Column("IdTipoDocumento").Not.Nullable();

        Map(x => x.Name).Column("name").Length(200).Not.Nullable();
        Map(x => x.Path).Column("path").Length(500).Not.Nullable();
        Map(x => x.FileUuid).Column("guid").Length(36).Not.Nullable()/*.Unique()*/;
        Map(x => x.Comments).Column("comentarios").Length(200).Nullable();
        Map(x => x.Hidden).Column("oculto").Nullable();
        Map(x => x.Order).Column("orden").Nullable();
        Map(x => x.InDate).Column("fecha").Nullable();
        Map(x => x.Link).Column("link").Length(200).Nullable();
        Map(x => x.Source).Column("source").Length(10).Nullable();

        References(x => x.Development).Column("ID_promocion").NotFound.Ignore().Fetch.Join()/*.Not*/.LazyLoad().ReadOnly();
        References(x => x.Asset).Column("ID_Inmueble").NotFound.Ignore().Fetch.Join().Not.LazyLoad().ReadOnly();
        References(x => x.User).Column("ID_Usuario").NotFound.Ignore().Fetch.Join().Not.LazyLoad().ReadOnly();
        References(x => x.DocumentType).Column("IdTipoDocumento").NotFound.Ignore().Fetch.Join().Not.LazyLoad().ReadOnly();
    }
}

}`

and linq generate this sql: select portfoliod0_.ID_promocion as col_0_0_, developmen1_.ID_driveby as col_1_0_, developmen1_.referencia as col_2_0_, portfolio2_.nombre as col_3_0_, developmen1_.ID_cartera as col_4_0_, (count((case when not (asset3_.out_of_scope=1) then 1 else null end))) as col_5_0_, developmen1_.main_type as col_6_0_, developmen1_.property_status as col_7_0_, developmen1_.surface_sum as col_8_0_, developmen1_.direccion_completa as col_9_0_, geographic4_.nom_03 as col_10_0_, geographic4_.nom_02 as col_11_0_, developmen1_.ID_tipo_valoracion as col_12_0_, (count(distinct portfoliod0_.Id)) as col_13_0_, LTRIM(RTRIM(CONCAT(LTRIM(RTRIM(CONCAT(user5_.FIRST_NAME, ' ', ISNULL(user5_.SECOND_NAME, '')))), ' ', ISNULL(user5_.LAST_NAME, '')))) as col_14_0_, LTRIM(RTRIM(CONCAT(LTRIM(RTRIM(CONCAT(user6_.FIRST_NAME, ' ', ISNULL(user6_.SECOND_NAME, '')))), ' ', ISNULL(user6_.LAST_NAME, '')))) as col_15_0_, developmen1_.final_value as col_16_0_, developmen1_.final_value/NULLIF(developmen1_.surface_sum, 0) as col_17_0_, (developmen1_._STATE) as col_18_0_, case when developmen1_._STATE=2 then 1 else 0 end as col_19_0_, developmen1_.ID_driveby as col_20_0_, case when developmen1_._STATE=1 then 1 else 0 end as col_21_0_, developmen1_.fecha_terminado as col_22_0_, case when developmen1_._STATE=2 then 1 else 0 end as col_23_0_, developmen1_.fecha_revisado as col_24_0_, developmen1_.c_fechamodif as col_25_0_ from dbo.driveby_documento portfoliod0_ **inner join** dbo.promocion developmen1_ on portfoliod0_.ID_promocion=developmen1_.ID_promocion left outer join dbo.cartera portfolio2_ on developmen1_.ID_cartera=portfolio2_.ID_cartera left outer join dbo.GEOGRAPHICAL_LEVELS geographic4_ on developmen1_.id_cod_base=geographic4_.objectid left outer join dbo.USER_ user5_ on developmen1_.ID_responsable_desktop=user5_.ID left outer join dbo.USER_ user6_ on developmen1_.id_revisado=user6_.ID left outer join dbo.inmueble asset3_ on developmen1_.ID_promocion=asset3_.ID_promocion where asset3_.ID_cartera = 728 group by portfoliod0_.ID_promocion , developmen1_.ID_driveby , developmen1_.referencia , portfolio2_.nombre , developmen1_.ID_cartera , developmen1_.main_type , developmen1_.property_status , developmen1_.surface_sum , developmen1_.direccion_completa , geographic4_.nom_03 , geographic4_.nom_02 , developmen1_.ID_tipo_valoracion , LTRIM(RTRIM(CONCAT(LTRIM(RTRIM(CONCAT(user5_.FIRST_NAME, ' ', ISNULL(user5_.SECOND_NAME, '')))), ' ', ISNULL(user5_.LAST_NAME, '')))) , LTRIM(RTRIM(CONCAT(LTRIM(RTRIM(CONCAT(user6_.FIRST_NAME, ' ', ISNULL(user6_.SECOND_NAME, '')))), ' ', ISNULL(user6_.LAST_NAME, '')))) , developmen1_.final_value , developmen1_._STATE , developmen1_.fecha_terminado , developmen1_.fecha_revisado , developmen1_.c_fechamodif -- having (count(distinct portfoliod0_.Id)) = 0 order by portfoliod0_.ID_promocion desc OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY

I don't understand why linq generates an inner join instead of a left join, can you help me?