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

Question

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

Developer · Accepted Answer · 2017-01-18 16:02:30Z

1

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)

answered Jan 18, 2017 at 16:02

Developer

6,4703 gold badges21 silver badges24 bronze badges

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

3 Comments

Muhammad Saqlain Over a year ago

I am getting error Self referencing loop detected with type 'AspNetIdentity.Models.PAY_TRANSACTION'. Path 'CMN_CURRENCY.PAY_TRANSACTION'.",

Developer Over a year ago

Either set

GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.Re‌ferenceLoopHandling = ReferenceLoopHandling.Ignore;

or assign the output to custom object before sending back.

Muhammad Saqlain Over a year ago

Thanks alot pal. You are PRO!

Collectives™ on Stack Overflow

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

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