Linq查询

最基本--查询语法

string[] names = { "aaa", "accc", "abbb", "ddd" };
var query = from c in names where c.StartsWith("a") orderby c descending select c;

View Code

Linq方法语法

c#编译器把Lambda表达式成c=>c.StartWith("a")编译为一个匿名方法.Where()方法,是筛选,(条件),会在names数组上的每个元素执行这个方法,

var query = names.Where(n => n.StartsWith("a"));

View Code

排序orderby字句

1,方法语法

 //方法语法(都可以),按照元素默认排序
var query = names.OrderBy(n => n).Where(n => n.StartsWith("a"));
//var query = names.Where(n => n.StartsWith("a")).OrderBy(n =>n);

View Code

2,查询语法

 var query = from c in names
            where c.StartsWith("a")
            orderby c descending
            select c;

View Code

聚合运算符[Count(),Min(),Max(),Average(),Sum()]

var query = customers.Select(n => n);
Console.WriteLine(query.Min());//最小值
Console.WriteLine(query.Max());//最大值
Console.WriteLine(query.Average());//平均值
Console.WriteLine(query.Count());//总个数
Console.WriteLine(query.Sum(n=>(long)n));//总和

View Code

去除重复Distinct()

var query = (from c in customers select c.Country).Distinct();

View Code

Linq提供的两个布尔方法[Any(),All()],用来确定数据是否满足某个条件,或者确保所有数据都满足某个条件.

创建对象并且初始化

 List<Customer> customers = new List<Customer> { 
            new Customer{ID="A",City="New York",Country="USA",Sales=9999,Region="North America"},
            new Customer{ID="B",City="Mumbai",Country="India",Sales=8888,Region="Asia"},
            new Customer{ID="C",City="Delhi",Country="India",Sales=7777,Region="Asia"},
            new Customer{ID="D",City="New York",Country="Delhi",Sales=6666,Region="Sourth America"},
            };

判断

bool anyUsa = customers.Any(n => n.Country == "USA");
if (anyUsa)
{
    Console.WriteLine("有一些人来自usa");
}
bool allAsia = customers.All(n => n.Region == "Asia");
     if (allAsia)
{
      Console.WriteLine("所有人都是Asia");
}

输出结果,anyusa为true,allasia为false

多级排序

1,查询语法

先按照地区,再按照国家,再按照城市排序

var query = from n in customers orderby n.Region, n.Country, n.City select new { n.ID, n.Region, n.Country, n.City };

输出结果结果id,区域国家城市等

{ ID = C, Region = Asia, Country = India, City = Delhi }

{ ID = B, Region = Asia, Country = India, City = Mumbai }

{ ID = A, Region = North America, Country = USA, City = New York }

{ ID = D, Region = Sourth America, Country = Delhi, City = New York }

2,方法语法[使用ThenBy()升序和Orderby(),以及ThenByDescending()降序]

var query = customers.OrderBy(c => c.Region)
    .ThenBy(c => c.Country)
    .ThenBy(c => c.City)
    .Select(c => new { c.ID, c.Region, c.Country, c.City });

View Code

组合查询

group by

var query=from c in customers
         group c by c.Region into cg
         select new{TotalSalary=cg.Sum(c=>c.Sales),Region=cg.Key};
var query1 = from cg in query
                         orderby cg.TotalSalary descending
                         select cg;

View Code

组合查询中的数据通过一个键(key)字段来组合,每个组中的所有成员都共享这个字段值,上面的键字段是

group c by c.Region

要计算每个组的总和,应声称一个新的结果集cg

group c by c.Region into cg

前几条数据与后几条数据(分区运算符)

Take()和Skip()方法(通过长与orderby一起用)

Take()表示去前n个数据,Skip()与Take()相反,是跳过前n个,返回剩余的结果

var query = from c in customers
                        orderby c.Sales descending
                        select new { c.ID, c.City, c.Country, c.Sales };
foreach (var item in query.Take(3))//取结果前3个
{
        Console.WriteLine(item);
}
foreach (var item in query.Skip(3))//去结果后三个
{
         Console.WriteLine(item);
}

View Code

First()和FirstOrDefault()方法

First(),返回结果集中第一个匹配给定条件的元素,如果没有匹配的元素,会报异常"序列不包含任何匹配元素"

FirstOrDefault()返回结果集中第一个匹配给定条件的元素,如果没有匹配的元素,不会报异常,返回默认值,为空""

 var query=from c in customers
            select new { c.ID, c.City, c.Country, c.Sales };
//输出以第一
Console.WriteLine(query.First(c => c.Country == "India"));
//报异常
Console.WriteLine(query.First(c => c.Country == "I222ndia"));    
输出空白      Console.WriteLine(query.FirstOrDefault(c=>c.Country=="123456"));

View Code