ASP.NET MVC 5 LinQ to SQL
Tags: csharp, webdev, sql
Published: 2019-10-26 15:09:05

Today, I will shared example Linq to SQL command in C# and ASP.NET. I think everyone knows something more or less about this LinQ statement
In the article. I will do example basic LinQ to SQL
**Prepare**
+ Create a project name "BasicLinQ"
+ Install Entity Framework, you using Nutget Manager to setup it 
+ Import a database to SQL SERVER 2012
Okay, you can see figure the following
![ASP.NET MVC 5 LinQ to SQL - hoanguyenit.com](https://thepracticaldev.s3.amazonaws.com/i/lkd9nsydtutd16zjzj25.jpg)

![ASP.NET MVC 5 LinQ to SQL - hoanguyenit.com](https://thepracticaldev.s3.amazonaws.com/i/p4hx38dgxq9x4uj11m1s.jpg)

![ASP.NET MVC 5 LinQ to SQL - hoanguyenit.com](https://thepracticaldev.s3.amazonaws.com/i/m1pwj8f5p6o9gssyoh8s.jpg)

![ASP.NET MVC 5 LinQ to SQL - hoanguyenit.com](https://thepracticaldev.s3.amazonaws.com/i/z0znvzaoy03wlyk16twu.jpg)

![ASP.NET MVC 5 LinQ to SQL - hoanguyenit.com](https://thepracticaldev.s3.amazonaws.com/i/ioht2pn6flsjkyvrvne6.jpg)

if you import success! Let's go you create a HomeController.cs file in BasicLinQ/Controllers directory

The connect to database, you need add two command to HomeController.cs file, pass the following below code
```csharp
 using BasicLinQ.Models;
 private QLTSEntities _db = new QLTSEntities();
```
**HomeController.cs**
```csharp
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using BasicLinQ.Models;
namespace BasicLinQ.Controllers
{
    public class HomeController : Controller
    {
        private QLTSEntities _db = new QLTSEntities();
        
        // GET: Home
        public ActionResult Index()
        {
            return View();
        }
    }
}
```
Okay, you can connect to database and write command LinQ to SQL

**Example 1:** Show user from table NHANVIEN 
You modify HomeController.cs file, the following code below
```csharp
public ActionResult Index()
        {
            //TH1:
            var nhanvien = (from s in _db.NHANVIENs select s).ToList();
            ViewBag.nhanviens = nhanvien;
            //TH2:
            //var nhanvien = _db.NHANVIENs.ToList();
            //ViewBag.nhanviens = nhanvien;
            return View();
        }
```
Continue! you to **Views/Home/Index.cshtml** directory, configuration **foreach()** 
```csharp
<table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>MANV</th>
                            <th>TENNV</th>
                            <th>TENDV</th>
                        </tr>
                    </thead>
                    <tbody>
                        @foreach (var item in ViewBag.nhanviens)
                        {
                        <tr>
                            <td>@item.MaNV</td>
                            <td>@item.TenNV</td>
                            <td>@item.DONVI.TenDV</td>
                        </tr>
                        }
                    </tbody>
                </table>
```
You will see result
![ASP.NET MVC 5 LinQ to SQL - hoanguyenit.com](https://thepracticaldev.s3.amazonaws.com/i/nxsr5ip189yqyv4x0j7g.jpg)

What happen?,we don't relationship to **DONVI** table, how dit we get **TenNV**, Because in LinQ support,It maps between tables together, you can see NHANVIEN models, the following code below
```csharp
public partial class NHANVIEN
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public NHANVIEN()
        {
            this.SOMUONs = new HashSet<SOMUON>();
        }
    
        public string MaNV { get; set; }
        public string TenNV { get; set; }
        public string Diachi { get; set; }
        public string Dienthoai { get; set; }
        public string MaDV { get; set; }
    
        public virtual DONVI DONVI { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<SOMUON> SOMUONs { get; set; }
    }
```
So you can point to the TenDV object: **@item.DONVI.TenDV**
**Example 2:**Sorting in ascending order (ASC) / descending (DESC)
```csharp
var nhanvien = (from s in _db.NHANVIENs
                            orderby s.MaNV ascending
                            select s
                            ).ToList();

//OR

            var nhanvien = (from s in _db.NHANVIENs
                            orderby s.MaNV descending
                            select s
                           ).ToList();
```
Use the query method
```csharp
var nhanvien = _db.NHANVIENs.OrderBy(s => s.MaDV).ToList();
var nhanvien = _db.NHANVIENs.OrderByDescending(s => s.MaDV).ToList();
```
**Example 3:**Find employees of unit 'KKHCB', we will link between tables NHANVIEN & DONVI
```csharp
var nhanvien = (from s in _db.NHANVIENs
                            where s.DONVI.MaDV == "KKHCB"
                            select s).ToList();
							
var nhanvien2 = _db.NHANVIENs
                .Where(s => s.DONVI.MaDV.Contains("KKHCB"))
                .ToList();
				
            ViewBag.nhanviens = nhanvien;
            ViewBag.nhanviens2 = nhanvien2;
            return View();
```

**Example 4:** add employess to NHANVIEN table
```csharp
public ActionResult Store(NHANVIEN _nv)
        {
		    
			
            _nv.TenNV = "Ts.Lưu Văn Thành";
            _nv.Diachi = "HCM";
            _nv.Dienthoai = "0123456789";
            _nv.MaDV = "KKHCB";
            _db.NHANVIENs.Add(_nv);
            _db.SaveChanges();
            return RedirectToAction("Index");
        }
```
OR
```csharp
_db.NHANVIENs.Add(_nv);
 _db.SaveChanges();

```

**Example 5:** Update employess in NHANVIEN table, key MaNV="KHCB003"
```csharp
NHANVIEN _nv = _db.NHANVIENs.Where(s => s.MaNV.Equals("KHCB003")).FirstOrDefault();
_nv.TenNV = "update ten";
 _db.SaveChanges();

//OR 

 NHANVIEN _nv = _db.NHANVIENs.Single(s => s.MaNV == "KHCB003");
            _nv.TenNV = "update ten 2";
            _db.SaveChanges();
```

**Example 6:** Delete employess in NHANVIEN table, key MaNV="KHCB003"
```csharp
var delete = _db.NHANVIENs.Where(s => s.MaNV.Equals("KHCB003")).FirstOrDefault();
            _db.NHANVIENs.Remove(delete);
            _db.SaveChanges();

//or
var delete2 = (from s in _db.NHANVIENs
                          where s.MaNV == "KHCB003"
                          select s).First();
            _db.NHANVIENs.Remove(delete2);
            _db.SaveChanges();
```

**Example 7:** Statistic total employess in DONVI table
If you using SQL in SQL SERVER
```sql
SQL:
select a.TenDV, b.TS
from DONVI a,
(select MaDV, COUNT(MaDV) TS from NHANVIEN b group by b.MaDV) b
where a.MaDV=b.MaDV

SQL:
select a.TenDV, COUNT(b.MaDV) AS TL
from DONVI a, NHANVIEN b
where a.MaDV= b.MaDV
group by a.TenDV,b.MaDV
```
LinQ in ASP.NET
```csharp
var donvi = _db.DONVIs.GroupBy(s => s.MaDV)
                .Select(g => new { 
                    g.FirstOrDefault().TenDV,
                    g.FirstOrDefault().NHANVIENs.Count
                });

var donvi = from s in _db.DONVIs
                           group s by s.MaDV into g
                           select new
                           {
                               TenDV = g.FirstOrDefault().TenDV,
                               TL = g.FirstOrDefault().NHANVIENs.Count
			};
```

**Example 8:** We statistic total estate of unit
```csharp
var taisan = from s in _db.TAISANs
                         from x in _db.GIATRITAISANs
                         from y in _db.DONVITAISANs
                         from z in _db.DONVIs
                         where s.MaTS==x.MaTS
                         where y.MaTS==s.MaTS
                         where z.MaDV == y.MaDV
                         select new
                         {
                             TenTS = s.TenTS,
                             SoLuong = s.GIATRITAISANs.FirstOrDefault().Soluong,
                             TenDV = (s.DONVITAISANs.FirstOrDefault()).DONVI.TenDV,
                             SoLuongDV = s.DONVITAISANs.FirstOrDefault().Soluong,
                             ConLai = (s.GIATRITAISANs.FirstOrDefault().Soluong - s.DONVITAISANs.FirstOrDefault().Soluong)

                         };
```
OR
```csharp
var taisan = from s in _db.TAISANs
                         join x in _db.GIATRITAISANs on s.MaTS equals x.MaTS
                         join y in _db.DONVITAISANs on s.MaTS equals y.MaTS
                         join z in _db.DONVIs on y.MaDV equals z.MaDV
                         select new
                         {
                             TenTS = s.TenTS,
                             SoLuong = x.Soluong,
                             TenDV = z.TenDV,
                             SoLuongDV = y.Soluong,
                             ConLai = (x.Soluong - y.Soluong)

                         };
```

**Example 9:** Unit Statistic, What assets are there?
SQL
```sql
select a.TenDV, count(b.MaDV) as Total 
	from DONVI a,DONVITAISAN b
	where a.MaDV=b.MaDV
	group by a.TenDV
```
LinQ
```csharp
var taisan = from s in _db.DONVIs
                        join x in _db.DONVITAISANs on s.MaDV equals x.MaDV
                        group s by s.MaDV into g
                        select new
                        {
                            TenDV = g.FirstOrDefault().TenDV,
                            Total = g.FirstOrDefault().DONVITAISANs.Count()

                        };
					 
var taisan = from s in _db.DONVIs
                        join x in _db.DONVITAISANs on s.MaDV equals x.MaDV
                        group s by s.MaDV into g
                        select new
                        {
                            TenDV = g.FirstOrDefault().TenDV,
                            Total = g.Count()

                        };
```

**Example 10:**List of assets by year
```csharp
var taisan = from s in _db.TAISANs
                         where (s.Namsx >= new DateTime(2014, 01, 01) && s.Namsx <= new DateTime(2014, 05, 01))
                         select new
                         {
                             s.TenTS,
                             s.Namsx

                         };
```

The article:[ASP.NET MVC 5 LinQ to SQL](https://hoanguyenit.com/aspnet-mvc5-linq-to-sql.html)
You can see more:
+ [ASP.NET MVC 5 Hello World](https://dev.to/skipperhoa/asp-net-mvc-5-helllo-world-1o55)
+ [ASP.NET MVC 5 Create Shared Layout Razor & ViewStart](https://dev.to/skipperhoa/asp-net-mvc-5-create-shared-layout-razor-viewstart-4h3)
+ [ASP.NET MVC 5 Install Bootstrap](https://dev.to/skipperhoa/asp-net-mvc-5-install-bootstrap-2d3a)
+ [ASP.NET MVC 5 Using DBContext Connect Database](https://dev.to/skipperhoa/asp-net-mvc-5-using-dbcontext-connect-database-3846)