LINQ to SQL语句

一、简单的where语句，Where操作包括3种形式，分别为简单形式、关系条件形式、First()形式
  1.简单式：

 1.简单式：使用where筛选在伦敦的客户
  
 var a=form c db.city where c.cityName='伦敦' select a
 
 2.筛选1994 年或之后雇用的雇员

 var q =
    from e in db.Employees
    where e.HireDate >= new DateTime(1994, 1, 1)
    select e;

    2.关系式代码 ：筛选出UnitPrice 大于10 或已停产的产品：

var q =
    from p in db.Products
    where p.UnitPrice > 10m || p.Discontinued
    select p;

    3.Frist（）

1.选择CustomerID 为“BONAP”的单个客户
Customer cust = db.Customers.First(c => c.CustomerID == "BONAP");

2.选择运费大于10的

Order ord = db.Orders.First(o => o.Freight > 10.00M);

 

二、Select/Distinct操作符
1.简单的用法

1.查询人名
var q =
    from c in db.Customers
    select c.ContactName;

  2.匿名函数类型

  

1.
var q =
    from c in db.Customers
    select new {c.ContactName, c.Phone};
上面语句描述：使用 SELECT 和匿名类型返回仅含客户联系人姓名和电话号码的序列

2.
var q =
    from e in db.Employees
    select new
    {
        Name = e.FirstName + " " + e.LastName,
        Phone = e.HomePhone
    };
上面语句描述：使用SELECT和匿名类型返回仅含雇员姓名和电话号码的序列，并将FirstName和LastName字段合并为一个字段“Name”，此外在所得的序列中将HomePhone字段重命名为Phone。

  3.条件形式

1.说明：生成SQL语句为：case when condition then else。
var q =
    from p in db.Products
    select new
    {
        p.ProductName,
        Availability =
        p.UnitsInStock - p.UnitsOnOrder < 0 ? 
        "Out Of Stock" : "In Stock"
    };
上面语句描述：使用SELECT和条件语句返回产品名称和产品供货状态的序列。

 

 4.指定类型

说明：该形式返回你自定义类型的对象集。
var q =
    from e in db.Employees
    select new Name
    {
        FirstName = e.FirstName,
        LastName = e.LastName
    };
上面语句描述：使用SELECT和已知类型返回雇员姓名的序列。

 

 5.嵌套类型式

说明：返回的对象集中的每个对象DiscountedProducts属性中，又包含一个集合。也就是每个对象也是一个集合类。
var q =
    from o in db.Orders
    select new {
        o.OrderID,
        DiscountedProducts =
            from od in o.OrderDetails
            where od.Discount > 0.0
            select od,
        FreeShippingDiscount = o.Freight
    };
语句描述：使用嵌套查询返回所有订单及其OrderID 的序列、打折订单中项目的子序列以及免送货所省下的金额。

 

 6.Distinct类型

说明：筛选字段中不相同的值。用于查询不重复的结果集。生成SQL语句为：SELECT DISTINCT [City] FROM [Customers]
var q = (
    from c in db.Customers
    select c.City )
    .Distinct();

 

 

三、关于SUM/COUNT/AVG/MAX/MIN函数的用法

1.SUM/COUNT/MIN/MAX类型

var q = db.Customers.Count();//简单

var q = db.Products.Count(p => !p.Discontinued);//带条件

var q = db.Orders.Select(o => o.Freight).Sum();简单

var q = db.Products.Sum(p => p.UnitsOnOrder);//映射形式

var q = db.Products.Select(p => p.UnitPrice).Min();//简单

var q = db.Orders.Min(o => o.Freight);//映射形式

var categories =
    from p in db.Products
    group p by p.CategoryID into g
    select new {
        CategoryID = g.Key,
        CheapestProducts =
            from p2 in g
            where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
            select p2
    };元素类型：查找任意产品中的最低价格

var q = db.Products.Max(p => p.UnitsInStock);//映射形式

var q = db.Products.Average(p => p.UnitPrice);//映射形式

 

四、LINQ关于增删改

1.简单的增
NorthwindDataContext db = new NorthwindDataContext();
var newCustomer = new Customer
{
    CustomerID = "MCSFT",
    CompanyName = "Microsoft",
    ContactName = "John Doe",
    ContactTitle = "Sales Manager",
    Address = "1 Microsoft Way",
    City = "Redmond",
    Region = "WA",
    PostalCode = "98052",
    Country = "USA",
    Phone = "(425) 555-1234",
    Fax = null
};
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();

2.一对多的增（分先后顺序的添加）
var newCategory = new Category
{
    CategoryName = "Widgets",
    Description = "Widgets are the ……"
};
var newProduct = new Product
{
    ProductName = "Blue Widget",
    UnitPrice = 34.56M,
    Category = newCategory
};
db.Categories.InsertOnSubmit(newCategory);
db.SubmitChanges();

3.多对多的增
var newEmployee = new Employee
{
    FirstName = "Kira",
    LastName = "Smith"
};
var newTerritory = new Territory
{
    TerritoryID = "12345",
    TerritoryDescription = "Anytown",
    Region = db.Regions.First()
};
var newEmployeeTerritory = new EmployeeTerritory
{
    Employee = newEmployee,
    Territory = newTerritory
};
db.Employees.InsertOnSubmit(newEmployee);
db.Territories.InsertOnSubmit(newTerritory);
db.EmployeeTerritories.InsertOnSubmit(newEmployeeTerritory);
db.SubmitChanges();

4.简单的改
Customer cust =
    db.Customers.First(c => c.CustomerID == "ALFKI");
cust.ContactTitle = "Vice President";
db.SubmitChanges();

5.多项更改
var q = from p in db.Products
        where p.CategoryID == 1
        select p;
foreach (var p in q)
{
    p.UnitPrice += 1.00M;
}
db.SubmitChanges();

6.简单的删除
OrderDetail orderDetail =
    db.OrderDetails.First
    (c => c.OrderID == 10255 && c.ProductID == 36);
db.OrderDetails.DeleteOnSubmit(orderDetail);
db.SubmitChanges();

7.一对多的删除（分先后顺序）
var orderDetails =
    from o in db.OrderDetails
    where o.Order.CustomerID == "WARTH" &&
    o.Order.EmployeeID == 3
    select o;
var order =
    (from o in db.Orders
     where o.CustomerID == "WARTH" && o.EmployeeID == 3
     select o).First();
foreach (OrderDetail od in orderDetails)
{
    db.OrderDetails.DeleteOnSubmit(od);
}
db.Orders.DeleteOnSubmit(order);
db.SubmitChanges();