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
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
using BasicLinQ.Models;
private QLTSEntities _db = new QLTSEntities();
HomeController.cs
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
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()
<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
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
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)
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
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
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
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
_db.NHANVIENs.Add(_nv);
_db.SaveChanges();
Example 5: Update employess in NHANVIEN table, key MaNV="KHCB003"
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"
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:
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
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
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
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
select a.TenDV, count(b.MaDV) as Total
from DONVI a,DONVITAISAN b
where a.MaDV=b.MaDV
group by a.TenDV
LinQ
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
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
You can see more:
Top comments (1)
You should not write code in Vietnamese Language, no one will understand what those fields are. Better using English.