如何从具有外键的表中检索数据 [英] how to retrieve data from tables having foreign key

问题描述

我正在使用ASP.NET MVC。用于数据库和LINQ查询的EntityFramework ....

我无法创建具有外键的表。有人可以帮助如何访问具有外键的表........

当我在TEST类的创建形式中输入值并单击按钮创建它不会返回索引页并且不在数据库中插入值

状态类在数据库中插入值



这是我的代码....





测试类具有FORIEGN状态表的关键字

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

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

状态类

STATUS CLASS

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

数据访问层

DATA ACCESS LAYER

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; }
   }

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

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

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();
           }
       }
   }

推荐答案

哇......好的。

首先，为数据对象创建ViewModel是一个好主意，你有一个有点奇怪的直接耦合。另外，在ViewModel构造函数中进行数据映射而不是作为处理程序，因为你在那里犯了错误。您正在尝试添加一个已经分配了ID的新对象：

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:

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();
       }
   }

相反，使用构造函数inject来复制参数。为了稍后的目的，我将使用TBL_TEST对象：

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:

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
       }
   }

你也好多了从您的代码中抽象出一层或两层，因为您现在正在使用ViewModel（CreateTest）创建一个ViewModel（TestDTO），使用不必要的处理程序对象来创建一个ORM模型（TBL_TEST）。虽然我都是抽象的，但这是不必要的复杂。请记住，DbContext是一个实现Repository模式的抽象层。为它分配处理程序仅在事件驱动的框架中有用，并且MVC本身并不是非常友好的事件。



您当前在上下文中的映射看起来很好乍一看，让我们看看你的控制器动作。我要删除处理程序，因为，恕我直言，这是无关紧要的：

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:

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
   }

这个构造将你的上下文管理放到Controller中，这是它真正设计在MVC中的地方。它还将ViewModel链接到您的ORM对象，并大大简化了代码。

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.

这篇关于如何从具有外键的表中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！