how to retrieve data from tables having foreign key

Question

0.00/5 (No votes)

See more:

I am using ASP.NET MVC. EntityFramework for database and LINQ to query....
I am unable to create a table having foreign key. Can some one help how to access table having foreign key........
When i enter values in create form of TEST class and click button create it does not return to index page and does not insert values in database
Status class insert values in database

Here is my code....

TEST CLASS HAVING FORIEGN KEY OF STATUS TABLE

C#

public class TestDTO
   {
       public int ID { get; set; }
       public string Description { get; set; }
       public int StatusID { get; set; }
   }

STATUS CLASS

C#

public class StatusDTO
   {
       public int ID { get; set; }
       public string Description { get; set; }
   }

DATA ACCESS LAYER

C#

public partial class TBL_STATUS
   {
       public TBL_STATUS()
       {
           TBL_TEST = new HashSet<TBL_TEST>();
       }

       public int ID { get; set; }

       [Required]
       [StringLength(50)]
       public string DESCRIPTION { get; set; }

       public virtual ICollection<TBL_TEST> TBL_TEST { get; set; }
   }

C#

public partial class TBL_TEST
    {
        public int ID { get; set; }

        [Required]
        [StringLength(50)]
        public string DESCRIPTION { get; set; }

        public int STATUS_ID { get; set; }

        public virtual TBL_STATUS TBL_STATUS { get; set; }
    }

CONTEXT CLASS

C#

public partial class TestContext : DbContext
   {
       public TestContext()
           : base("name=TestContext")
       {
       }

       public virtual DbSet<TBL_STATUS> TBL_STATUS { get; set; }
       public virtual DbSet<TBL_TEST> TBL_TEST { get; set; }

       protected override void OnModelCreating(DbModelBuilder modelBuilder)
       {
           modelBuilder.Entity<TBL_STATUS>()
               .HasMany(e => e.TBL_TEST)
               .WithRequired(e => e.TBL_STATUS)
               .HasForeignKey(e => e.STATUS_ID)
               .WillCascadeOnDelete(false);
       }
   }

BUSSINESSLAYER

public class StatusHandler
   {
       TestContext db = new TestContext();

       public List<StatusDTO> GetData()
       {
           var temp = from s in db.TBL_STATUS
                      select new StatusDTO
                      {
                          ID = s.ID,
                          Description = s.DESCRIPTION
                      };
           return temp.ToList();
       }

       public void Insert(StatusDTO temp)
       {
           TBL_STATUS s = new TBL_STATUS();
           s.DESCRIPTION = temp.Description;

           db.TBL_STATUS.Add(s);
           db.SaveChanges();
       }
   }

public class TestHandler
   {
       TestContext db = new TestContext();

       public List<TestDTO> GetData()
       {
           var temp = from s in db.TBL_TEST
                      select new TestDTO
                      {
                          ID = s.ID,
                          Description = s.DESCRIPTION,
                          StatusID = s.ID
                      };
           return temp.ToList();
       }

       public void Insert(TestDTO temp)
       {
           TBL_TEST s = new TBL_TEST();
           s.DESCRIPTION = temp.Description;
           s.ID = temp.StatusID;

           db.TBL_TEST.Add(s);
           db.SaveChanges();
       }
   }

CONTROLLER

public class StatusController : Controller
   {
       // GET: Status
       public ActionResult Index()
       {
           List<StatusDTO> status = new StatusHandler().GetData();
           return View(status);
       }

       // GET: Status/Details/5
       public ActionResult Details(int id)
       {
           return View();
       }

       // GET: Status/Create
       public ActionResult Create()
       {
           return View();
       }

       // POST: Status/Create
       [HttpPost]
       public ActionResult Create(CreateStatus model)
       {
           try
           {
               StatusDTO status = new StatusDTO();
               status.Description = model.Description;

               new StatusHandler().Insert(status);
               return RedirectToAction("Index");
           }
           catch
           {
               return View();
           }
       }

       // GET: Status/Edit/5
       public ActionResult Edit(int id)
       {
           return View();
       }

       // POST: Status/Edit/5
       [HttpPost]
       public ActionResult Edit(int id, FormCollection collection)
       {
           try
           {
               // TODO: Add update logic here

               return RedirectToAction("Index");
           }
           catch
           {
               return View();
           }
       }

       // GET: Status/Delete/5
       public ActionResult Delete(int id)
       {
           return View();
       }

       // POST: Status/Delete/5
       [HttpPost]
       public ActionResult Delete(int id, FormCollection collection)
       {
           try
           {
               // TODO: Add delete logic here

               return RedirectToAction("Index");
           }
           catch
           {
               return View();
           }
       }
   }

public class TestController : Controller
   {
       // GET: Test
       public ActionResult Index()
       {
           List<TestDTO> status = new TestHandler().GetData();
           return View(status);
       }

       // GET: Test/Details/5
       public ActionResult Details(int id)
       {
           return View();
       }

       // GET: Test/Create
       public ActionResult Create()
       {
           return View();
       }

       // POST: Test/Create
       [HttpPost]
       public ActionResult Create(CreateTest model)
       {
           try
           {
               TestDTO status = new TestDTO();
               status.Description = model.Description;
               status.StatusID = model.StatusID;

               new TestHandler().Insert(status);

               return RedirectToAction("Index");
           }
           catch
           {
               return View();
           }
       }

       // GET: Test/Edit/5
       public ActionResult Edit(int id)
       {
           return View();
       }

       // POST: Test/Edit/5
       [HttpPost]
       public ActionResult Edit(int id, FormCollection collection)
       {
           try
           {
               // TODO: Add update logic here

               return RedirectToAction("Index");
           }
           catch
           {
               return View();
           }
       }

       // GET: Test/Delete/5
       public ActionResult Delete(int id)
       {
           return View();
       }

       // POST: Test/Delete/5
       [HttpPost]
       public ActionResult Delete(int id, FormCollection collection)
       {
           try
           {
               // TODO: Add delete logic here

               return RedirectToAction("Index");
           }
           catch
           {
               return View();
           }
       }
   }

Posted 7-Oct-15 5:32am

Member 10740412

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nathan Minier · Answer 1 · 2015-10-07T06:27:00

Wow...okay.
First off, while making ViewModels for your data objects is a good idea, you have a direct coupling which is a bit odd. Also, do your data-mapping in the ViewModel Constructor rather than as a handler, because you made a mistake there. You're trying to add a new object that you've already assigned an ID to:

C#

public class TestHandler
   {
       TestContext db = new TestContext();
 
       public List<TestDTO> GetData()
       {
           var temp = from s in db.TBL_TEST
                      select new TestDTO
                      {
                          ID = s.ID,
                          Description = s.DESCRIPTION,
                          StatusID = s.ID // WHOOPS
                      };
           return temp.ToList();
       }
 
       public void Insert(TestDTO temp)
       {
           TBL_TEST s = new TBL_TEST();
           s.DESCRIPTION = temp.Description;
           s.ID = temp.StatusID; // Big WHOOPS!
 
           db.TBL_TEST.Add(s);
           db.SaveChanges();
       }
   }

Instead, use a constructor inject to copy parameters over. For the sake of a later point I'm going to use the TBL_TEST object:

C#

public partial class TBL_TEST
   {
        public int ID { get; set; }
 
        [Required]
        [StringLength(50)]
        public string DESCRIPTION { get; set; }
 
        public int STATUS_ID { get; set; }
 
        public virtual TBL_STATUS TBL_STATUS { get; set; }

       public TestDTO(CreateTest test)
       {
          DESCRIPTION = test.Description;
          STATUS_ID= test.StatusID
       }
   }

You're also much better off taking a layer of abstraction or two out of your code, since right now you're using a ViewModel (CreateTest) to create a ViewModel (TestDTO), using an unnecessary handler object, to create a ORM model (TBL_TEST). While I'm all for abstraction, this is needlessly complex. Remember that a DbContext is ALREADY an abstraction layer that implements the Repository pattern. Assigning handlers to it is only useful in an event-driven framework, and MVC is not natively terribly event friendly.

Your current mapping on the context looks fine at first blush, so let's look at just your controller actions. I'm going to strip out the handler because, IMHO, it's extraneous:

C#

public class TestController : Controller
   {
       protected TestContext _context = new TestContext(); 
       
       // GET: Test
       public ActionResult Index()
       {
           return View(_context.TBL_TEST.ToArray());
       }
 
       // GET: Test/Details/5
       public ActionResult Details(int id)
       {
           return View(_context.TBL_TEST.Find(id));
       }
 
       // GET: Test/Create
       public ActionResult Create()
       {
           return View();
       }
 
       // POST: Test/Create
       [HttpPost]
       public ActionResult Create(CreateTest model)
       {
           try
           {
               _context.TBL_TEST.Add(new TBL_TEST(model));
 
               return RedirectToAction("Index");
           }
           catch
           {
               return View();
           }
       }

       #region implement IDisposable
       protected bool _disposed = false;
       protected override void Dispose(bool disposing)
       {
          if(!_disposed)
          {
             if(disposing)
             {
                _context.Dispose();
             }
             _disposed = true;
          }
       }
       #endregion
   }

This construct places management of your context into the Controller, which is where it's really designed to be in MVC. It also links your ViewModel to your ORM object, and vastly simplifies the code.