Why Query returns some null values to my list Object using dapper

Question

I have this functional query where I only used fictive tables names for security concerns :

SELECT 
h.CENID,
 h.BMHFMC, 
 h.BMHDONEMIDASSTEP1, 
 h.BMHDONEMIDASSTEP2,
 h.LMIID, 
 h.BMHHOLD,
 h.BMHBATCHMIDAS,
 h.BMHFMCVALUEDATE AS HeaderValueDate, 
 h.SUNID, 
 h.BRAID,
 d.BMHID, 
 d.BMDRUBRIQUE,
 d.BMDCLIENT,
 d.BMDSEQUENCE,
 d.BMDDATE, 
 d.BMDDEVISE, 
 d.BMDMONTANT,
 d.BMDTYPE, 
 d.BMDNOTE, 
 d.BMDENTRYNBRE,
 v.DEVDECIMAL ,
 NVL(t.TYPVERIFCOMPTEMIDAS, 0) AS TYPVERIFCOMPTEMIDAS 
 FROM dbo.TableOne h 
 INNER JOIN dbo.Tabletwoo d
 ON h.BMHID = d.BMHID 
 INNER JOIN dbo.tableThree v
 ON d.BMDDEVISE = v.DEVID 
 LEFT JOIN dbo.TableFour t 
 ON  t.TYPID=h.BMHFMC  
 WHERE d.BMDMONTANT != 0 
 AND h.BMHDONEMIDASSTEP1 = 0
 AND h.BMHDONEMIDASSTEP2 = 0 
 AND h.LMIID = 0 
 AND h.BMHHOLD = 0

And I made a class in order to bind every fields

 public class Batch :BaseRepository ,IList<Batch>
    {
        public Batch()
        {
        }

        private string cendid;
        private string bmhfmc;
        private double bmhdonemidasstep1;
        private double bmhdonemidasstep2;
        private double lmiid;
        private double bmhhold;
        private double bmhbatchmidas;
        private DateTime headervaluedateordinal;
        private double sunid; // 
        private string bradid; //
        private double bmhid;
        private string bmdrubirique; //
        private string bmdclient;
        private string bmdsequence;
        private DateTime bmddate;
        private string bmddevise;
        private double bmdmontant;
        private string bmdtype;
        private string bmdnote;
        private string bmdentrynbre; //
        private double devdecimalordinal;
        private double typverifcomptemidasordinal;

        public Batch(string cendid, string bmhfmc, double bmhdonemidasstep1, double bmhdonemidasstep2, double lmiid, double bmhhold, double bmhbatchmidas, DateTime headervaluedateordinal, double sunid, string bradid, double bmhid, string bmdrubirique, string bmdclient, string bmdsequence, DateTime bmddate, string bmddevise, double bmdmontant, string bmdtype, string bmdnote, string bmdentrynbre, double devdecimalordinal, double typverifcomptemidasordinal)
        {
            this.cendid = cendid;
            this.bmhfmc = bmhfmc;
            this.bmhdonemidasstep1 = bmhdonemidasstep1;
            this.bmhdonemidasstep2 = bmhdonemidasstep2;
            this.lmiid = lmiid;
            this.bmhhold = bmhhold;
            this.bmhbatchmidas = bmhbatchmidas;
            this.headervaluedateordinal = headervaluedateordinal;
            this.sunid = sunid;
            this.bradid = bradid;
            this.bmhid = bmhid;
            this.bmdrubirique = bmdrubirique;
            this.bmdclient = bmdclient;
            this.bmdsequence = bmdsequence;
            this.bmddate = bmddate;
            this.bmddevise = bmddevise;
            this.bmdmontant = bmdmontant;
            this.bmdtype = bmdtype;
            this.bmdnote = bmdnote;
            this.bmdentrynbre = bmdentrynbre;
            this.devdecimalordinal = devdecimalordinal;
            this.typverifcomptemidasordinal = typverifcomptemidasordinal;
        }

        public string Cendid
        {
            get { return cendid; }
            set { cendid = value; }
        }

        public string Bmhfmc
        {
            get { return bmhfmc; }
            set { bmhfmc = value; }
        }

        public double Bmhdonemidasstep1
        {
            get { return bmhdonemidasstep1; }
            set { bmhdonemidasstep1 = value; }
        }

        public double Bmhdonemidasstep2
        {
            get { return bmhdonemidasstep2; }
            set { bmhdonemidasstep2 = value; }
        }

        public double Lmiid
        {
            get { return lmiid; }
            set { lmiid = value; }
        }

        public double Bmhhold
        {
            get { return bmhhold; }
            set { bmhhold = value; }
        }

        public double Bmhbatchmidas
        {
            get { return bmhbatchmidas; }
            set { bmhbatchmidas = value; }
        }

        public DateTime Headervaluedateordinal
        {
            get { return headervaluedateordinal; }
            set { headervaluedateordinal = value; }
        }

        public double Sunid
        {
            get { return sunid; }
            set { sunid = value; }
        }

        public string Bradid
        {
            get { return bradid; }
            set { bradid = value; }
        }

        public double Bmhid
        {
            get { return bmhid; }
            set { bmhid = value; }
        }

        public string Bmdrubirique
        {
            get { return bmdrubirique; }
            set { bmdrubirique = value; }
        }

        public string Bmdclient
        {
            get { return bmdclient; }
            set { bmdclient = value; }
        }

        public string Bmdsequence
        {
            get { return bmdsequence; }
            set { bmdsequence = value; }
        }

        public DateTime Bmddate
        {
            get { return bmddate; }
            set { bmddate = value; }
        }

        public string Bmddevise
        {
            get { return bmddevise; }
            set { bmddevise = value; }
        }

        public double Bmdmontant
        {
            get { return bmdmontant; }
            set { bmdmontant = value; }
        }

        public string Bmdtype
        {
            get { return bmdtype; }
            set { bmdtype = value; }
        }

        public string Bmdnote
        {
            get { return bmdnote; }
            set { bmdnote = value; }
        }

        public string Bmdentrynbre
        {
            get { return bmdentrynbre; }
            set { bmdentrynbre = value; }
        }

        public double Devdecimalordinal
        {
            get { return devdecimalordinal; }
            set { devdecimalordinal = value; }
        }

        public double Typverifcomptemidasordinal
        {
            get { return typverifcomptemidasordinal; }
            set { typverifcomptemidasordinal = value; }
        }

Now when I execute the query into a list using dapper

Connection conn = new Connection();
 OracleConnection connection = conn.GetDBConnection();
  myList= connection.Query<Batch>(querySql).ToList();

Now,while debugging, all fields returns the expected values .But, I noticed those fields below are null in myList not empty but really null , but the problem is they aren't null in the database

Bmdrubirique , Sunid, Bmdentrynbre, Bradid ,Cenid

In oracle database those fields are like the following :

CENID is VARCHAR2(3 BYTE)` 
Bmhid is VARCHAR2(3 BYTE) 
Sunid is NUMBER(38,0)
Bradid is VARCHAR2(3 BYTE)

I don't get it , where did it go wrong? why other fields are properly loaded while those returns null value ?

Marc Gravell · Accepted Answer · 2017-12-07 21:34:18Z

1

My default assumption would be that there is a typo in the real code and the constructor is assigning a value from a field to itself. However, frankly: since you have a public Batch() {} constructor, I'm not sure what the benefit of the second one is - it just adds risk of errors. Likewise with the fields and manual properties.

So if this as me, where you currently have (simplified to two properties):

public class Batch
{
    private string cendid;
    private string bmhfmc;
    public Batch() {}
    public Batch(string cendid, string bmhfmc)
    {
        this.cendid = cendid;
        this.bmhfmc = bmhfmc;
    }

    public string Cendid
    {
        get { return cendid; }
        set { cendid = value; }
    }

    public string Bmhfmc
    {
        get { return bmhfmc; }
        set { bmhfmc = value; }
    }
}

I would have literally just:

public class Batch
{
    public string Cendid {get;set;}
    public string Bmhfmc {get;set;}
}

All of the rest of the code is just opportunities to make coding errors.

Now: the reason that Cendid is null is because: the column is CENID - only one d. This means that dapper isn't even using your custom constructor, because it isn't a perfect match between the constructor and the columns. Ditto the other fields like BRAID vs BRADID.

So the next thing to do is to fix the typos.

answered Dec 7, 2017 at 21:34

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

napi15 Over a year ago

Indeed, it was the typo causing the issue. I thought Dapper don't care about fields name but only the types . Thank you , the more I learn about this amazing ORM the better I achieve good results . And to answer your concern about getter and setter, I just right click and let Resharper ( an legendary tool ) generate them for me and save my self hassles and headache ,

Marc Gravell Over a year ago

@napi15 but... what headache? an automatically implemented property is cleaner - less code to read, and behaves identically

napi15 Over a year ago

Yes but for some reason , my current version of VS don't have feature that implement property automatically , Resharper did save my life

Collectives™ on Stack Overflow

Why Query returns some null values to my list Object using dapper

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related