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!

Please Sign in or register to post replies

Write your reply to:

Draft