Database Queries in MVC Model

Question

In a MVC project if I put LINQ queries in Model, is it 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 should I move these methods (GetNameById, GetAuthorById) to Controller?

Answer 1

use like this

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

Answer 2

When building a Model for ASP.NET MVC applications always it is a good practice to use the Repository pattern so that the DAL layer is easy to change and test as per the need arises. When you use the Repository pattern, you create a separate repository class that contains all of your database access logic CURD with constraints.

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

Answer 3

In a MVC project if I put LINQ queries in Model, is it against the MVC Pattern?

No, it's not against the MVC pattern. Database queries are perfectly fine in the Model. Obviously a clear distinction should be made between the Model and the View Model that you are passing to your views. The view model should not contain any database specific stuff.

Or should I move these methods (GetNameById, GetAuthorById) to Controller?

Absolutely not. The controller's responsibility is not to query a database. A controller responsibility is to talk to the Model, build a view model and pass this view model to the view. A controller shouldn't even know what a database is.