LINQ to SQL Select查询

2017-01-04 19:18:46来源:cnblogs.com作者:Swich人点击

第七城市

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,进行不同的处理

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台