Database Queries in the MVC Model

Question

In an MVC project, if I put LINQ queries in Model , is this against the MVC pattern?

 namespace DocLibrary.Models { public class Author { private DocLibraryContext db = new DocLibraryContext(); [Key] public Int32 AuthorId { get; set; } [StringLength(20)] public String Name { get; set; } .. public string GetNameById(int AuthorId) { var query = from a in db.Author where a.AuthorId == AuthorId select a.Name; return query.FirstOrDefault(); } public Author GetAuthorById(int AuthorId) { var query = from a in db.Author where a.AuthorId.Equals(AuthorId) select a; return query.FirstOrDefault(); } } 

Or do I need to move these methods ( GetNameById , GetAuthorById ) to the controller?

Answer 1

In an MVC project, if I put LINQ queries in Model, is this against the MVC pattern?

No, this is not against the MVC pattern. Database queries are great for the model. Obviously, a clear distinction should be made between the Model and the Viewing Model that you pass on to your views. The view model should not contain any data specific to the database.

Or do I need to move these methods (GetNameById, GetAuthorById) to the controller?

Absolutely not. The responsibility of the dispatcher is not to query the database. It’s the responsibility of the dispatcher to talk to the Model, build a view model, and pass that view model to the view. The controller does not even have to know what a database is.

Answer 2

When creating a model for ASP.NET MVC applications, it is always recommended to use the repository template so that the DAL layer is easily changed and tested as needed. When you use the repository template, you create a separate repository class that contains all the restricted access logic for the CURD database.

When we create a repository class, we create an interface that represents all the methods used by the repository class. Inside the controllers, we write our code against the interface instead of the repository.

Answer 3

use

 internal IQueryable<Table1> GetArmyList2() { //var lists = from list in db.Table1 // select list; //return lists; var query = from Table1 in db.Table1 where Table1.Username.Equals("asik") & Table1.Password.Equals("asik") select Table1; return query; } 

and controller code

 public ActionResult asik() { var armyList = cl.GetArmyList2(); return View(armyList); // return View(); }