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
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语句为：
)
[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语句为：
)
[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]
= [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，进行不同的处理