public void linqInnerJoinExample() { var query = from product in products join product_ProductTypeRelationship in product_ProductTypeRelationships on product.Id equals product_ProductTypeRelationship.ProductID join productType in productTypes on product_ProductTypeRelationship.ProductTypeID equals productType.Id select new { product.Id, product.Name, product.Price, productTypeName = productType.Name }; var result = query.ToList(); }
結果:
說明:因為是InnerJoin，都要有重疊才會關聯出，而XBOX Series X的Id沒有在product_ProductTypeRelationships裡面，所以最後沒有被顯示出來。 基本上跟原生SQL的寫法挺像的。
public void linqLeftJoinExample() { // product 跟 product_ProductTypeRelationship 先left join var query1 = from product in products join product_ProductTypeRelationship in product_ProductTypeRelationships on product.Id equals product_ProductTypeRelationship.ProductID into tmp from product_ProductTypeRelationship in tmp.DefaultIfEmpty() select new { product.Id, product.Name, product.Price, ProductTypeID = product_ProductTypeRelationship?.ProductTypeID };
// query1結果再跟 productTypes left join var query2 = from q in query1 join productType in productTypes on q.ProductTypeID equals productType.Id into tmp from product_ProductTypeRelationship in tmp.DefaultIfEmpty() select new { q.Id, q.Name, q.Price, ProductName = product_ProductTypeRelationship?.Name }; var result = query2.ToList(); }
結果:
說明:可以得到XBOX Series X了。 因為LINQ沒有LEFT JOIN這種簡單作法，只能靠DefaultIfEmpty來操作，作法比較複雜、難直覺判斷。 ((ps.不是要用來讓大家好用的查詢嗎?搞複雜了
作法2:組多個DefaultIfEmpty
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
public void linqLeftJoinExample2() { var query = from a in products join b in product_ProductTypeRelationships on a.Id equals b.ProductID into result1 from ab in result1.DefaultIfEmpty() join c in productTypes on ab?.ProductTypeID equals c.Id into result2 from abc in result2.DefaultIfEmpty() select new{ a.Id, a.Name, a.Price, ProductName = abc?.Name } ; var result = query.ToList(); }
結果:
說明:可以得到XBOX Series X了。 這種多個DefaultIfEmpty組法感覺蠻難讀的。 要獨立出一個值，導致得用a、b、c來組不然難知道誰是誰，就很難閱讀 且要小心有些值讀出來是空的要使用「?.」的做法，把empty的值設定為null才有辦法繼續下去，否則會執行到中間就報錯了。
var p = (from a in products select new ProductViewModel { Id = a.Id, Name = a.Name, Price = a.Price, }).ToList();
foreach (var item in p) { var productTypeIDs = from a in product_ProductTypeRelationships where a.ProductID == item.Id select a.ProductTypeID;
var productTypeData = from a in productTypeIDs join b in productTypes on a equals b.Id select new ProductType { Id = b.Id, Name = b.Name }; item.ProductTypes = new List<ProductType>(); foreach (var x in productTypeData) { item.ProductTypes.Add(x); } } var result = p; string jsonData = JsonConvert.SerializeObject(result); System.Diagnostics.Debug.WriteLine(jsonData); }
Language Integrated Query (LINQ) 是一組以直接將查詢功能整合至 C# 語言為基礎之技術的名稱。 傳統上，資料查詢是以簡單的字串表示，既不會在編譯時進行類型檢查，也不支援 IntelliSense。 此外，您還必須針對每種資料來源類型學習不同的查詢語言：SQL 資料庫、XML 文件、各種 Web 服務等等。透過 LINQ，查詢會是第一級語言建構，和類別、方法及事件相同。 您可以使用語言關鍵字和熟悉的運算子，針對強型別的物件集合撰寫查詢。 LINQ 技術系列會針對物件 (LINQ to Object)、關聯式資料庫 (LINQ to SQL) 與 XML (LINQ to XML)，提供一致的查詢體驗。
using Microsoft.AspNetCore.Authorization; namespace CookieAuthentication.Authorization { public class PermissionAuthorizationRequirement : IAuthorizationRequirement { public string[] Permissions { get; set; }
public PermissionAuthorizationRequirement(string[] permissions) { Permissions = permissions; } } }
using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.Filters; using Microsoft.Extensions.DependencyInjection; using System; using System.Threading.Tasks;
namespace CookieAuthentication.Authorization { public class PermissionFilter:Attribute, IAsyncAuthorizationFilter { public string[] permissions { get; set; }
public PermissionFilter(params string[] permissions) { this.permissions = permissions; }
public async Task OnAuthorizationAsync(AuthorizationFilterContext context) { var authorizationService = context.HttpContext.RequestServices.GetRequiredService<IAuthorizationService>(); var authorizationResult = await authorizationService.AuthorizeAsync(context.HttpContext.User, null, new PermissionAuthorizationRequirement(permissions)); if (!authorizationResult.Succeeded) { // 如果授權失敗，設定為未授權 context.Result = new UnauthorizedResult(); } } } }
說明: 重點在21～27，使用authorizationService.AuthorizeAsync去認證權限，就會把資料傳到我們上面建立的PermissionAuthorizationHandler.cs裡面判斷。 如果最後判斷沒權限則設定context.Result = new UnauthorizedResult();表示禁止。
using CookieAuthentication.Authorization; using Microsoft.AspNetCore.Authentication.Cookies; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Builder; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using System; namespace CookieAuthentication { public class Startup { public Startup(IConfiguration configuration) { Configuration = configuration; }
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { // 註冊需求和處理常式，套用自訂權限控制器 services.AddSingleton<IAuthorizationHandler, PermissionAuthorizationHandler>();
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IWebHostEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } else { app.UseExceptionHandler("/Home/Error"); // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts. app.UseHsts(); } app.UseHttpsRedirection(); app.UseStaticFiles();
using Microsoft.AspNetCore.Authentication.Cookies; using Microsoft.AspNetCore.Builder; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.HttpsPolicy; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks;
namespace CookieAuthentication { public class Startup { public Startup(IConfiguration configuration) { Configuration = configuration; }
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) {
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IWebHostEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } else { app.UseExceptionHandler("/Home/Error"); // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts. app.UseHsts(); } app.UseHttpsRedirection(); app.UseStaticFiles();
using CookieAuthentication.Models; using Microsoft.AspNetCore.Authentication; using Microsoft.AspNetCore.Authentication.Cookies; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.Logging; using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Security.Claims; using System.Text; using System.Threading.Tasks;
namespace CookieAuthentication.Controllers {
public class HomeController : Controller { private readonly ILogger<HomeController> _logger;
public HomeController(ILogger<HomeController> logger) { _logger = logger; }
public IActionResult Index() { return View(); }
[Authorize] public IActionResult Privacy() { StringBuilder sb = new StringBuilder(); sb.AppendLine("<ul>");
<div class="text-center"> <h1 class="display-4">Welcome</h1> <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p> </div>
using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using WebApplication1.Models;
namespace WebApplication1.ViewComponents { public class ShowUserInfo : ViewComponent { public async Task<IViewComponentResult> InvokeAsync(string number, string name,string tel) { Customer customer = new Customer(); customer.Name = name; customer.Number = number; customer.Tel = tel; return View(customer); } } }
using Microsoft.AspNetCore.Mvc; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using WebApplication1.Models;
namespace WebApplication1.Controllers { public class TestController : Controller { public IActionResult Index() { // 使用ViewData ViewData["Message"] = "Hello World!";
TestViewModel testViewModel = new TestViewModel(); testViewModel.Customer = new Customer() ; testViewModel.Customer.Name = "YU HSIANG"; testViewModel.Customer.Number = "A001"; testViewModel.Customer.Tel = "00-00000000";
testViewModel.Products = new List<Product>(); testViewModel.Products.Add(new Product() { Name = "PS4", Number = "P001", Price = 11000 }); testViewModel.Products.Add(new Product() { Name = "PS5", Number = "P002", Price = 15000 });
return View(testViewModel); } public JsonResult TestJsonResult() { return Json(new { data = 5 }); }
@model WebApplication1.Models.TestViewModel @* For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860 *@ @ViewData["Message"]<br> @Model.Customer.Name<br> @Model.Customer.Number<br> @Model.Customer.Tel<br>
@model WebApplication1.Models.TestViewModel @* For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860 *@ @ViewData["Message"]<br> @ViewBag.msg<br> @Model.Customer.Name <br> @Model.Customer.Number <br> @Model.Customer.Tel <br>
下圖顯示完整的查詢作業。
