Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Rasmus Eeg 91 posts 457 karma points c-trib
    Jun 24, 2016 @ 12:27
    Rasmus Eeg
    3

    How to use InnerJoin or LeftJoin with POCO

    What did you do?

    I've created the following models:

    [TableName("udfUnionMunicipalities")]
    [PrimaryKey("id", autoIncrement = false)]
    [ExplicitColumns]
    public class UnionMunicipality
    {
        /// <summary>
        /// OfficialCode
        /// </summary>
        [Column("id")]
        [PrimaryKeyColumn(AutoIncrement = false)]
        public string Id { get; set; }
    
        [Column("name")]
        public string Name { get; set; }
    }
    
    /// <summary>
    /// Company Category
    /// </summary>
    [TableName("udfUnionCategories")]
    [PrimaryKey("id", autoIncrement = false)]
    [ExplicitColumns]
    public class UnionCategory
    {
        [PrimaryKeyColumn(AutoIncrement = false)]
        [Column("id")]
        public int Id { get; set; }
    
        [Column("name")]
        public string Name { get; set; }
    }
    
    /// <summary>
    /// Company
    /// </summary>
    [TableName("udfUnions")]
    [PrimaryKey("id", autoIncrement = false)]
    [ExplicitColumns]
    public class Union
    {
        [Column("id")]
        [PrimaryKeyColumn(AutoIncrement = false)]
        public string Id { get; set; }
    
        [Column("name")]
        public string Name { get; set; }
    
        [Column("categoryId")]
        [ForeignKey(typeof(UnionCategory))]
        public int CategoryId { get; set; }
    
        [ForeignKey(typeof(UnionMunicipality))]
        [Column("municipalityId")]
        public string MunicipalityId { get; set; }
    
        [ResultColumn]
        public UnionMunicipality Municipality { get; set; }
    
        [ResultColumn]
        public UnionCategory Category { get; set; }
    }
    

    Then i used the following query to join Category and Municipality on Union table

    var sql = new Sql()
                .Select("udfUnions.*, udfUnionMunicipalities.*, udfUnionCategories.*")
                .From<Union>(dbContext.SqlSyntax);
    
                sql.InnerJoin<UnionMunicipality>(dbContext.SqlSyntax)
                   .On<Union, UnionMunicipality>(dbContext.SqlSyntax, u => u.CategoryId, c => c.Id)
                   .InnerJoin<UnionCategory>(dbContext.SqlSyntax)
                   .On<Union, UnionCategory>(dbContext.SqlSyntax, u => u.CategoryId, c => c.Id);
    
                LogHelper.Info<UnionsRepository>(() => sql.SQL);
    
                return db.Fetch<Union, UnionMunicipality, UnionCategory, Union>((u, m, c) => {
                   u.Municipality = m;
                   u.Category = c;
                   return u;
                }, sql);
    

    Outputs following sql:

    SELECT udfUnions.*, udfUnionMunicipalities.*, udfUnionCategories.*
    FROM [udfUnions]
    LEFT JOIN [udfUnionMunicipalities]
    ON [udfUnions].[municipalityId] = [udfUnionMunicipalities].[id]
    LEFT JOIN [udfUnionCategories]
    ON [udfUnions].[categoryId] = [udfUnionCategories].[id]
    

    I also tried writing the SQL manually like so:

    SELECT *
    FROM udfUnions
    INNER JOIN udfUnionMunicipalities
    ON udfUnions.MunicipalityId = udfMunicipalties.Id
    INNER JOIN udfUnionCategories
    ON udfUnions.CategoryId = udfUnionCategories.Id
    

    Like the heading says, i also tried using LEFT JOIN

    What did you expect to happen? That the tables would be mapped to correct result columns

    What actually happened? I know it not much, but this is what the log shows:

    System.NullReferenceException: Object reference not set to an instance of an object.
    

    Any help would be appreciated!

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies