LINQ in Asp.net MVC: Building Database-Driven Web Application

Are you struggling to connect your Asp.net MVC application with a database? Wondering how to perform CRUD operations efficiently? Look no further! In this comprehensive guide, I’ll walk you through creating a fully functional web application using LINQ in Asp.net MVC that handles all database operations seamlessly.

When I first started with Asp.net MVC, integrating database operations felt overwhelming. But trust me, once you understand how LINQ works with MVC, you’ll be building powerful applications in no time. I’ve been using this combination for years, and it’s absolutely revolutionary for .NET developers.

What You’ll Learn in This Tutorial

• Setting up an Asp.net MVC project with LINQ to SQL
• Creating a clean Model-View-Controller architecture
• Implementing all CRUD operations (Create, Read, Update, Delete)
• Adding data validation for a better user experience
• Building a real-world poll application example

Note: This tutorial uses ASP.NET MVC 6 with .NET 6 (updated from the original MVC 3 version) and focuses on LINQ to SQL for data access. It’s perfect for beginners to intermediate developers!

Prerequisites

Before diving in, make sure you have:

1. Visual Studio 2022 installed (Community edition works great)
2. Basic understanding of C# programming
3. SQL Server (Express or higher) installed
4. Familiarity with HTML and CSS basics

Setting Up the Project

Let’s start by creating a new ASP.NET MVC project:

1. Open Visual Studio 2022
2. Click “Create a new project”
3. Select “ASP.NET Core Web App (Model-View-Controller)”
4. Name your project “PollApplication” (or whatever you prefer)
5. Click “Create”

Once your project is set up, we need to add the necessary packages for LINQ to SQL. In modern .NET projects, we’ll use Entity Framework Core, which is the successor to LINQ to SQL but follows similar principles:

csharpInstall-Package Microsoft.EntityFrameworkCore.SqlServer

Database Structure

For our poll application, we’ll create a simple database structure. We’ll need a table for questions:

CREATE TABLE Questions (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(100) NOT NULL,
    Details NVARCHAR(500) NOT NULL
);Code language: PHP (php)

In real applications, you’d likely have additional tables for answers, users, etc., but we’ll keep it simple for this tutorial.

Creating the Data Model

Now, let’s set up our data models. First, create a Models folder if it doesn’t already exist. Then add a new class file called Question.cs:

using System.ComponentModel.DataAnnotations;

namespace PollApplication.Models
{
    public class Question
    {
        public int Id { get; set; }
        
        [Required(ErrorMessage = "Title is required")]
        public string Title { get; set; }
        
        [Required(ErrorMessage = "Description is required")]
        public string Details { get; set; }
    }
}Code language: JavaScript (javascript)

Next, create a DbContext class to handle database operations:

using Microsoft.EntityFrameworkCore;

namespace PollApplication.Models
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public DbSet<Question> Questions { get; set; }
    }
}Code language: HTML, XML (xml)

Setting Up the Service Layer

I always follow the repository pattern for a cleaner architecture. Let’s create our service layer next.

First, create an interface for our question service:

using PollApplication.Models;

namespace PollApplication.Services
{
    public interface IQuestionService
    {
        bool ValidateQuestion(Question question);
        bool CreateQuestion(Question question);
        List<Question> GetAllQuestions();
        Question GetQuestion(int id);
        bool DeleteQuestion(int id);
        bool UpdateQuestion(Question question);
    }
}Code language: HTML, XML (xml)

Then, implement the interface:

using PollApplication.Models;

namespace PollApplication.Services
{
    public class QuestionService : IQuestionService
    {
        private readonly ApplicationDbContext _context;

        public QuestionService(ApplicationDbContext context)
        {
            _context = context;
        }

        public bool ValidateQuestion(Question question)
        {
            if (string.IsNullOrEmpty(question.Title))
                return false;
            
            if (string.IsNullOrEmpty(question.Details))
                return false;
            
            return true;
        }

        public bool CreateQuestion(Question question)
        {
            try
            {
                _context.Questions.Add(question);
                _context.SaveChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public List<Question> GetAllQuestions()
        {
            // This is where LINQ magic happens!
            return _context.Questions.ToList();
        }

        public Question GetQuestion(int id)
        {
            // Another LINQ query to fetch a single record
            return _context.Questions.FirstOrDefault(q => q.Id == id);
        }

        public bool DeleteQuestion(int id)
        {
            try
            {
                // LINQ to find and remove the question
                var question = _context.Questions.FirstOrDefault(q => q.Id == id);
                if (question != null)
                {
                    _context.Questions.Remove(question);
                    _context.SaveChanges();
                    return true;
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        public bool UpdateQuestion(Question question)
        {
            try
            {
                // LINQ to find and update
                var existingQuestion = _context.Questions.FirstOrDefault(q => q.Id == question.Id);
                if (existingQuestion != null)
                {
                    existingQuestion.Title = question.Title;
                    existingQuestion.Details = question.Details;
                    _context.SaveChanges();
                    return true;
                }
                return false;
            }
            catch
            {
                return false;
            }
        }
    }
}Code language: HTML, XML (xml)

Configuring Dependency Injection

We need to register our service with the dependency injection container to use it. Open Program.cs and add:

// Add services to the container.
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<IQuestionService, QuestionService>();Code language: JavaScript (javascript)

And make sure to add your connection string in appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=PollApplication;Trusted_Connection=True;MultipleActiveResultSets=true"
}Code language: JavaScript (javascript)

Creating the Controller

Now, let’s create our QuestionController to handle all the operations:

using Microsoft.AspNetCore.Mvc;
using PollApplication.Models;
using PollApplication.Services;

namespace PollApplication.Controllers
{
    public class QuestionController : Controller
    {
        private readonly IQuestionService _questionService;

        public QuestionController(IQuestionService questionService)
        {
            _questionService = questionService;
        }

        // GET: /Question/
        public IActionResult Index()
        {
            List<Question> questions;
            try
            {
                questions = _questionService.GetAllQuestions();
            }
            catch
            {
                questions = new List<Question>();
            }
            return View(questions);
        }

        // GET: /Question/Details/5
        public IActionResult Details(int id)
        {
            Question question;
            try
            {
                question = _questionService.GetQuestion(id);
                if (question == null)
                {
                    return NotFound();
                }
            }
            catch
            {
                return NotFound();
            }
            return View(question);
        }

        // GET: /Question/Create
        public IActionResult Create()
        {
            return View();
        }

        // POST: /Question/Create
        [HttpPost]
        [ValidateAntiForgeryToken]
        public IActionResult Create(Question question)
        {
            if (ModelState.IsValid)
            {
                try
                {
                    _questionService.CreateQuestion(question);
                    return RedirectToAction(nameof(Index));
                }
                catch
                {
                    // Log the error here
                }
            }
            return View(question);
        }

        // GET: /Question/Edit/5
        public IActionResult Edit(int id)
        {
            Question question;
            try
            {
                question = _questionService.GetQuestion(id);
                if (question == null)
                {
                    return NotFound();
                }
            }
            catch
            {
                return NotFound();
            }
            return View(question);
        }

        // POST: /Question/Edit/5
        [HttpPost]
        [ValidateAntiForgeryToken]
        public IActionResult Edit(int id, Question question)
        {
            if (id != question.Id)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                try
                {
                    _questionService.UpdateQuestion(question);
                    return RedirectToAction(nameof(Index));
                }
                catch
                {
                    // Log the error
                }
            }
            return View(question);
        }

        // GET: /Question/Delete/5
        public IActionResult Delete(int id)
        {
            Question question;
            try
            {
                question = _questionService.GetQuestion(id);
                if (question == null)
                {
                    return NotFound();
                }
            }
            catch
            {
                return NotFound();
            }
            return View(question);
        }

        // POST: /Question/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public IActionResult DeleteConfirmed(int id)
        {
            try
            {
                _questionService.DeleteQuestion(id);
                return RedirectToAction(nameof(Index));
            }
            catch
            {
                return View();
            }
        }
    }
}Code language: HTML, XML (xml)

Creating the Views

Now, let’s create our views. First, update the _Layout.cshtml to add a link to our Questions page:

<ul class="navbar-nav flex-grow-1">
    <li class="nav-item">
        <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>
    </li>
    <li class="nav-item">
        <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>
    </li>
    <li class="nav-item">
        <a class="nav-link text-dark" asp-area="" asp-controller="Question" asp-action="Index">Questions</a>
    </li>
</ul>Code language: HTML, XML (xml)

Now, let’s create the necessary views for our Question controller:

Index.cshtml

@model IEnumerable<PollApplication.Models.Question>

@{
    ViewData["Title"] = "Questions";
}

<h1>Poll Questions</h1>

<p>
    <a asp-action="Create" class="btn btn-primary">Create New</a>
</p>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Title)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Details)
            </th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Title)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Details)
            </td>
            <td>
                <a asp-action="Edit" asp-route-id="@item.Id" class="btn btn-sm btn-warning">Edit</a> |
                <a asp-action="Details" asp-route-id="@item.Id" class="btn btn-sm btn-info">Details</a> |
                <a asp-action="Delete" asp-route-id="@item.Id" class="btn btn-sm btn-danger">Delete</a>
            </td>
        </tr>
        }
    </tbody>
</table>Code language: HTML, XML (xml)

Create.cshtml

@model PollApplication.Models.Question

@{
    ViewData["Title"] = "Create Question";
}

<h1>Create New Question</h1>

<hr />
<div class="row">
    <div class="col-md-6">
        <form asp-action="Create">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="Title" class="control-label"></label>
                <input asp-for="Title" class="form-control" />
                <span asp-validation-for="Title" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Details" class="control-label"></label>
                <textarea asp-for="Details" class="form-control" rows="4"></textarea>
                <span asp-validation-for="Details" class="text-danger"></span>
            </div>
            <div class="form-group mt-3">
                <input type="submit" value="Create" class="btn btn-primary" />
                <a asp-action="Index" class="btn btn-secondary">Back to List</a>
            </div>
        </form>
    </div>
</div>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}Code language: HTML, XML (xml)

Edit.cshtml

@model PollApplication.Models.Question

@{
    ViewData["Title"] = "Edit Question";
}

<h1>Edit Question</h1>

<hr />
<div class="row">
    <div class="col-md-6">
        <form asp-action="Edit">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <input type="hidden" asp-for="Id" />
            <div class="form-group">
                <label asp-for="Title" class="control-label"></label>
                <input asp-for="Title" class="form-control" />
                <span asp-validation-for="Title" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Details" class="control-label"></label>
                <textarea asp-for="Details" class="form-control" rows="4"></textarea>
                <span asp-validation-for="Details" class="text-danger"></span>
            </div>
            <div class="form-group mt-3">
                <input type="submit" value="Save" class="btn btn-primary" />
                <a asp-action="Index" class="btn btn-secondary">Back to List</a>
            </div>
        </form>
    </div>
</div>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}Code language: HTML, XML (xml)

Details.cshtml

@model PollApplication.Models.Question

@{
    ViewData["Title"] = "Question Details";
}

<h1>Question Details</h1>

<div>
    <hr />
    <dl class="row">
        <dt class="col-sm-2">
            @Html.DisplayNameFor(model => model.Title)
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Title)
        </dd>
        <dt class="col-sm-2">
            @Html.DisplayNameFor(model => model.Details)
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Details)
        </dd>
    </dl>
</div>
<div>
    <a asp-action="Edit" asp-route-id="@Model.Id" class="btn btn-warning">Edit</a> |
    <a asp-action="Index" class="btn btn-secondary">Back to List</a>
</div>Code language: HTML, XML (xml)

Delete.cshtml

@model PollApplication.Models.Question

@{
    ViewData["Title"] = "Delete Question";
}

<h1>Delete Question</h1>

<h3>Are you sure you want to delete this question?</h3>
<div>
    <hr />
    <dl class="row">
        <dt class="col-sm-2">
            @Html.DisplayNameFor(model => model.Title)
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Title)
        </dd>
        <dt class="col-sm-2">
            @Html.DisplayNameFor(model => model.Details)
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Details)
        </dd>
    </dl>
    
    <form asp-action="Delete">
        <input type="hidden" asp-for="Id" />
        <input type="submit" value="Delete" class="btn btn-danger" /> |
        <a asp-action="Index" class="btn btn-secondary">Back to List</a>
    </form>
</div>Code language: HTML, XML (xml)

Understanding LINQ in ASP.NET MVC

The true power of this application comes from LINQ (Language Integrated Query). LINQ allows us to write database queries directly in C# without falling back to raw SQL strings. Here’s how LINQ makes our MVC application better:

1. Type Safety: LINQ queries are checked at compile-time, catching errors before runtime
2. IntelliSense Support: Visual Studio provides autocomplete for your LINQ queries
3. Unified Syntax: Use the same syntax to query databases, collections, XML, and more
4. Testability: LINQ queries are easier to mock and test than raw SQL strings

Let’s examine some of the LINQ queries we used:

// Get all questions
return _context.Questions.ToList();

// Get a single question
return _context.Questions.FirstOrDefault(q => q.Id == id);

// Delete a question (find then delete)
var question = _context.Questions.FirstOrDefault(q => q.Id == id);
if (question != null)
{
    _context.Questions.Remove(question);
    _context.SaveChanges();
}Code language: JavaScript (javascript)

The lambda expressions like q => q.Id == id are essentially telling LINQ: “Find me a question where the Id property equals the provided id value.” This is much cleaner and safer than constructing SQL strings with parameters.

Extending the Application

Once you have this basic structure working, you can easily extend the application:

1. Add Authentication: Implement user authentication so that only registered users can create polls
2. Create Answer Model: Add functionality for users to answer poll questions
3. Add Reporting: Create reports and visualizations based on poll results
4. Implement Search: Add LINQ-powered search functionality to find specific polls

Common Pitfalls and Best Practices

When working with LINQ in ASP.NET MVC, keep these tips in mind:

1. Avoid N+1 Query Problems: Use Include() to eagerly load related data
2. Be Careful with Large Result Sets: Use paging with Skip() and Take()
3. Optimize LINQ Queries: Specify only the columns you need with Select()
4. Handle Exceptions: Always wrap database operations in try-catch blocks
5. Use Asynchronous Methods: For better performance, use ToListAsync() and other async methods

Conclusion

LINQ is a game-changer for ASP.NET MVC development. It bridges the gap between your C# code and database operations elegantly, letting you focus on building features rather than wrestling with SQL syntax.

In this tutorial, we’ve built a complete poll application using LINQ in ASP.NET MVC, implementing all CRUD operations. We’ve seen how the repository pattern keeps our code organized and how data annotations provide built-in validation.

The combination of ASP.NET MVC’s structured approach and LINQ’s powerful querying capabilities makes building database-driven web applications faster and more maintainable than ever before.

Now go ahead and build something amazing with LINQ in ASP.NET MVC! If you have any questions or need help, feel free to leave a comment below. Happy coding!