1. 查詢所有欄位 生成SQL語句為: 2. 指定欄位查詢 也可以是: 生成SQL語句為: 3. First和FirstOrDefault 生成SQL語句為: 4. Single和SingleOrDefault 生成的SQL語句為: 5.First,FirstOrDefault,Single,Sin ...
1. 查詢所有欄位
using (NorthwindEntities context = new NorthwindEntities()) { var order = from n in context.Orders select n; foreach (var i in order.ToList()) { Console.Write(i.Customers); Console.WriteLine(); } }
生成SQL語句為:
SELECT [Extent1].[OrderID] AS [OrderID], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[OrderDate] AS [OrderDate], [Extent1].[RequiredDate] AS [RequiredDate], [Extent1].[ShippedDate] AS [ShippedDate], [Extent1].[ShipVia] AS [ShipVia], [Extent1].[Freight] AS [Freight], [Extent1].[ShipName] AS [ShipName], [Extent1].[ShipAddress] AS [ShipAddress], [Extent1].[ShipCity] AS [ShipCity], [Extent1].[ShipRegion] AS [ShipRegion], [Extent1].[ShipPostalCode] AS [ShipPostalCode], [Extent1].[ShipCountry] AS [ShipCountry] FROM [dbo].[Orders] AS [Extent1]
2. 指定欄位查詢
var product = from n in context.Products .Select(p => new { p.CategoryID, p.ProductName }) select n;
也可以是:
var product = context.Products.Select(p => new { p.CategoryID, p.ProductName });
生成SQL語句為:
SELECT 1 AS [C1], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[ProductName] AS [ProductName] FROM [dbo].[Products] AS [Extent1]
3. First和FirstOrDefault
var product = context.Products.FirstOrDefault();
var product = context.Products.First();
生成SQL語句為:
SELECT TOP (1) [c].[ProductID] AS [ProductID], [c].[ProductName] AS [ProductName], [c].[SupplierID] AS [SupplierID], [c].[CategoryID] AS [CategoryID], [c].[QuantityPerUnit] AS [QuantityPerUnit], [c].[UnitPrice] AS [UnitPrice], [c].[UnitsInStock] AS [UnitsInStock], [c].[UnitsOnOrder] AS [UnitsOnOrder], [c].[ReorderLevel] AS [ReorderLevel], [c].[Discontinued] AS [Discontinued] FROM [dbo].[Products] AS [c]
4. Single和SingleOrDefault
var product = context.Products.Single(p => p.ProductID==1);
var product = context.Products.SingleOrDefault(p => p.ProductID==1);
生成的SQL語句為:
SELECT TOP (2) [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[Discontinued] AS [Discontinued] FROM [dbo].[Products] AS [Extent1] WHERE 1 = [Extent1].[ProductID]
5.First,FirstOrDefault,Single,SingleOrDefault的區別
區別:
First:取序列中滿足條件的第一個元素,如果沒有元素滿足條件,則拋出異常。
FirstOrDefault:取序列中滿足條件的第一個元素,如果沒有元素滿足條件,則返回預設值(對於可以為null的對象,預設值為null,對於不能為null的對象,如int,預設值為0)。
Single:返回序列中的唯一一條記錄,如果沒有或返回多條,則引發異常。
SingleOrDefault:返回序列中的唯一一條記錄,如果序列中不包含任何記錄,則返回預設值,如果返回多條,則引發異常。
使用場景:
當沒有元素滿足條件時,First會拋出異常,FirstOrDefault會返回預設值。
1. First當確信有元素滿足條件時,使用First方法。取到元素後,無需判斷是否為null。
2. 當不確信或序列中找不到滿足條件的元素時,使用FirstOrDefault方法。然後一定要對返回值進行判斷是否為null,進行不同的處理