CODEWITHSUNDEEP
c#asp.netentity-frameworkasp.net-web-apientity-framework-6
Muhammad Saqlain
Muhammad Saqlain

Reputation: 2202

Getting data through entity model Relational tables in ASP.NET WEB API

I am creating a web api to get all transactions with object of country, currency which is foreign key here.

I have a controller with method

   [Route("transactions")]
        public IHttpActionResult GetCountries()
        {
            return Ok(db.PAY_TRANSACTION);
        }

This is my Entity Model

 public partial class PAY_TRANSACTION
    {
        public PAY_TRANSACTION()
        {
            this.PAY_BATCH_TRANSACTION = new HashSet<PAY_BATCH_TRANSACTION>();
            this.PAY_TRANSACTION_DETAIL = new HashSet<PAY_TRANSACTION_DETAIL>();
        }

        public int ID { get; set; }
        public int CMN_INSTITUTE_ID { get; set; }
        public int USER_ID { get; set; }
        public int MAS_COUNTRY_ID { get; set; }
        public int CMN_CURRENCY_ID { get; set; }
        public double AMOUNT_PAYING { get; set; }
        public double EXCHANGE_RATE { get; set; }
        public double AMOUNT_FX { get; set; }
        public System.DateTime CREATED_ON { get; set; }
        public int CREATED_BY { get; set; }
        public System.DateTime UPDATED_ON { get; set; }
        public int UPDATED_BY { get; set; }
        public bool IS_DELETED { get; set; }
        public bool IS_APPROVED { get; set; }
        public int VERSION_NO { get; set; }

        public virtual CMN_CURRENCY CMN_CURRENCY { get; set; }
        public virtual CMN_INSTITUTE CMN_INSTITUTE { get; set; }
        public virtual MAS_COUNTRY MAS_COUNTRY { get; set; }
        public virtual ICollection<PAY_BATCH_TRANSACTION> PAY_BATCH_TRANSACTION { get; set; }
        public virtual ICollection<PAY_TRANSACTION_DETAIL> PAY_TRANSACTION_DETAIL { get; set; }
    }

When I request for methods I get this error:

Invalid column name 'MAS_COUNTRY_ID1'.\r\nInvalid column name 'CMN_INSTITUTE_ID1'.\r\nInvalid column name 'CMN_CURRENCY_ID1'.

When I set [ForeignKey("CMN_INSTITUTE_ID ")], [ForeignKey("MAS_COUNTRY_ID ")], [ForeignKey("CMN_CURRENCY_ID ")], I get this response

{
  "ID": 7,
  "CMN_INSTITUTE_ID": 2,
  "USER_ID": 3,
  "MAS_COUNTRY_ID": 1,
  "CMN_CURRENCY_ID": 2,
  "AMOUNT_PAYING": 123,
  "EXCHANGE_RATE": 1.2,
  "AMOUNT_FX": 22,
  "CREATED_ON": "2017-01-18T17:50:16.993",
  "CREATED_BY": 0,
  "UPDATED_ON": "2017-01-18T17:50:16.993",
  "UPDATED_BY": 0,
  "IS_DELETED": false,
  "IS_APPROVED": false,
  "VERSION_NO": 0,
  "CMN_CURRENCY": null,
  "CMN_INSTITUTE": null,
  "MAS_COUNTRY": null,
  "PAY_BATCH_TRANSACTION": [],
  "PAY_TRANSACTION_DETAIL": []
}

which is showing my MAS_COUNTRY, CMN_INSTITUTE, CMN_CURRENCY NULL

Upvotes: 0

Views: 104

Answers (1)

Developer
Developer

Reputation: 6430

Those related entities will not get loaded until its accessed( Lazy Loading). In your case you are not accessing those entities before sending it back and hence those properties will never get loaded. You can Eager Load those entities in your case, db.PAY_TRANSACTION.Include (t=> t.MAS_COUNTRY).Include (t=>t.CMN_INSTITUTE).Include (t=>t.CMN_CURRENCY)

Upvotes: 1

Related Questions