LINQ to SQL Select查詢，linqselect

LINQ to SQL Select查詢，linqselect

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，進行不同的處理