Linq To SQL基本语句（查询）

1.Select

var q =
   (from e in db.Employees
    select new
    {
        Name = e.FirstName + " " + e.LastName,
        Phone = e.HomePhone,
        IsAdult=e.Age>18? "Yes ":"No "
    }).Distinct();

相当于SQL中

Select Distinct FirstName + "" + LastName AS Name, HomePhone AS Phone,
             CASE WHEN  Age>18  THEN 'Yes' ELSE 'No' END AS IsAdult
From  db.Employees

(e.FirstName + "" + e.LastName可以写成外部方法后调用)

Count

var q = db.Products.Count(p => !p.Discontinued);
var q = db.Customers.LongCount();

Sum

var q = db.Orders.Select(o => o.Freight).Sum();
var q = db.Products.Sum(p => p.UnitsOnOrder);

Min

var q = db.Products.Select(p => p.UnitPrice).Min();
var q = db.Orders.Min(o => o.Freight);

Max

var q = db.Employees.Select(e => e.HireDate).Max();
var q = db.Products.Max(p => p.UnitsInStock);

Average

var q = db.Orders.Select(o => o.Freight).Average();
var q = db.Products.Average(p => p.UnitPrice);

 

2.Where

var q =
   db.Products.Where(p=>p.UnitPrice >10m).Where(p=>p.Discontinued);

等同于

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

(p.Discontinued意思是Discontinued==true，!p.Discontinued意思是Discontinued==false)

 

3.First

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

(First()表示筛选出来的结果的第一个值。()中写筛选条件。该方法后面加”.字段名”可以得到所需值。)

 

4.Join

InnerJoin

var innerjoin = from p in db.Products
                       join c in db.Categories
                       onp.CategoryID equals c.CategoryID
                       select p.ProductName;

相当于SQL中

Select   ProductName From db.Products
Inner  Join db.Categories on db.Products. CategoryID = db.Categories. CategoryID

LeftJoin

var leftjoin = from p in db.Products
                       join c in db.Categories
                       on p.CategoryID equals c.CategoryID
                       into pro
                       from x in pro.DefaultIfEmpty()
                       select p.ProductName;

相当于SQL中

Select   ProductName From db.Products
Left  Outer  Joindb.Categories on db.Products. CategoryID = db.Categories. CategoryID

 

5.OrderBy

var q =
    from c in db.Customers
    orderby c.City, c.ContactName descending
    select c;

等同于

var q = 
    .OrderBy(c => c.City)
    . ThenByDescending(c => c.ContactName).ToList();

 等同于

var q = 
    db.Customers
    .OrderBy(c => c.ContactName)
    .OrderByDescending(c => c.City).ToList();

 

6.Group By

var q = 
    from p in db.Products
    group p by p.CategoryID into g
    orderby g.Key
    select new {
        g.Key,
        MostExpensiveProducts =
            from p2 in g
            where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
            select p2
};

 

var q =
    from p in db.Products
    group p by p.CategoryID into g
    select new { CategoryID = g.Key, g }; 

(group by 后面的语句以group by的结果开头)