# LINQ查询
LINQ提供了一种跨数据源和数据格式的统一模型,实现查询。它使用C#语言以查询数据库相同的方式查询内存数据。在LINQ中,可以使用相同的编码模式来查询和转换XML文档、SQL数据库、ADO.NET数据集以及.NET集合中的数据,并支持LINQ查询的任何格式的数据。
LINQ的编写从from关键字开始,结合使用where、select等关键字,实现各种条件查询。如var a = from record in tableName select record
得到tableName中的所有数据。
实例:
实体
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp1
{
class User
{
public string name { get; set; }
public int age { get; set; }
public bool isMarry { get; set; }
public User()
{
}
public User (string name,int age,bool isMarry)
{
this.name = name;
this.age = age;
this.isMarry = isMarry;
}
}
}
主函数
List<User> list = new List<User>();
list.Add(new User("张三",20,false));
list.Add(new User("李四", 21
, false));
list.Add(new User("王五", 22, false));
//按条件查询
var data = from a in list where a.age == 20 select a;
foreach (var item in data)
{
Console.WriteLine(item.age);
Console.WriteLine(item.name);
}
结果
按年龄降序
List<User> list = new List<User>();
list.Add(new User("张三", 26, false));
list.Add(new User("李四", 21, false));
list.Add(new User("王五", 22, false));
IEnumerable <User> data = from a in list orderby a.age descending select a;
foreach (var item in data)
{
Console.WriteLine(item.name+"-"+item.age);
}
LINQ方法语法
LINQ方法语法是非常灵活和重要的,方法语法的本质是通过扩展方法和Lambda表达式来创建查询。
LINQ查询语法有两种语法可以选择,方法语法(Fluent Syntax)和查询语法(Query Expression)。
方法语法原理
实例:
string[] names = { "Jaad","Bob","Saly","Dad" };
IEnumerable<string> query = names
.Where(n => n.Contains("a")) //查询条件 必须含有字母a
.OrderBy(n => n.Length) //按长度升序排序
.Select(n => n.ToUpper()); //字母全部大写
foreach (string item in query)
{
Console.WriteLine(item);
}
结果
Where、Orderby、select、这些标准查询对应Enumerable类中的相应扩展方法。where产生一个经过过滤的Sequence;orderby生成输入Sequence的排序版本;select得到的序列中的每个元素都经过了给定的Lambda表达式的转换。
// (1)加载所有数据
var blogs = BloggingContext.Blogs.ToList();
SELECT [b].[BlogId], [b].[Name], [b].[Title], [b].[Url] FROM [Blogs] AS [b]
//(2)加载单个实体
var blog = BloggingContext.Blogs.Single(b => b.BlogId == 1);
SELECT TOP(2) [b].[BlogId], [b].[Name], [b].[Title], [b].[Url]
FROM [Blogs] AS [b]
WHERE [b].[BlogId] = 1
//(3)筛选
var blogs = BloggingContext.Blogs.Where(b => b.Url.Contains("dotnet")).ToList();
SELECT [b].[BlogId], [b].[Name], [b].[Title], [b].[Url]
FROM [Blogs] AS [b]
WHERE CHARINDEX(N'dotnet', [b].[Url]) > 0
//(4)排序
var blogs = BloggingContext.Blogs.OrderByDescending(b => b.BlogId).Select(b=> new { b.BlogId,b.Name }).ToList();
C#Copy
SELECT [b].[BlogId], [b].[Name]
FROM [Blogs] AS [b]
ORDER BY [b].[BlogId] DESC
//(5) group 找出重复的url,取出最大BlogId
var blogs = from b in BloggingContext.Blogs
group b by new { b.Url} into gs
where gs.Count() >1
select new
{
ID= gs.Max(b=>b.BlogId)
};
//top 1
int maxID = blogs.First().ID;
SELECT TOP(1) MAX([b].[BlogId]) AS [ID]
FROM [Blogs] AS [b]
GROUP BY [b].[Url]
HAVING COUNT(*) > 1
// (6)多表join查询
var query = from b in context.Blogs
join p in context.Posts on b.BlogId equals p.BlogId
where b.BlogId == 1
select new { b.Name,p.Title } ;
var bloglinq= query.ToList();
SELECT [b].[Name], [p].[Title]
FROM [Blogs] AS [b]
INNER JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]
WHERE [b].[BlogId] = 1
Any() 、All()、Contains()
使用如下
var lis = Enumerable.Range(1, 100); //表示生成一个 1到100的泛型
var temp = lis.Where(a => a > 90);
foreach (var item in temp)
{
Console.WriteLine($"符合条件的数是:{item}");
}
if (temp.Any(a => a > 90))
{
Console.WriteLine("存在大于90的数");
}
if (temp.All(a=> a>=80))
{
Console.WriteLine("true");
}
else
{
Console.WriteLine("false");
}
if (temp.Contains(95))
{
Console.WriteLine("列表中存在95");
}
Any() 确定序列中是否有满足条件的元素
All() 确定是否序列中的所有元素都满足条件
Contains() 确定序列中是否包含指定元素
以上反回的都是一个bool值
数据分区操作
Skip(): 跳过序列中指定个数的元素
SkipWhile(): 跳过指定序列中的元素,直到某个元素不满足条件为止
Take(): 提取序列中的指定个数的元素
TakeWhile(): 从序列中提取元素,直到某个元素不满足条件为止
var lis = Enumerable.Range(1, 100); //表示生成一个 1到100的泛型
var fq = lis.Skip(5).Take(5);
foreach (var item in fq)
{
Console.WriteLine($"{item}");
}
var fqs = lis.TakeWhile(a => a <5);
var fa = lis.SkipWhile(a=>a>2);
Distinct() 去除重复数据
var aString = ";lsakfg';dfgk[fdmg;lfdgmlkkldfgjsdlkgjs8fgs;dfkglfdmgl;mdsf;gp30irgfjdlkfjgflkdjglfdksjgiegkljssi4eojgpo4it403ktkg92";
var t = aString.ToCharArray().Distinct().OrderBy(a => a);
foreach (var item in t)
{
Console.WriteLine($"字符{item}出现了:{aString.ToCharArray().Count(a=>a==item)}");
}
LINQ 和文件目录
using System;
using System.Collections.Generic;
using System.Linq;
using ConsoleApp4.Entity;
namespace ConsoleApp4
{
class Program
{
static void Main(string[] args)
{
var starfolder = @"C:\Users\Shinelon\Documents\Tencent Files\2983934033\FileRecv";
var fileList = GetFiles(starfolder);
var temp = fileList.Where(a => a.Extension == ".png").OrderBy(a => a.Name);
foreach (var item in temp)
{
Console.WriteLine(item.FullName);
}
}
static IEnumerable<System.IO.FileInfo> GetFiles(string path)
{
if (!System.IO.Directory.Exists(path))
{
throw new System.IO.DirectoryNotFoundException();
}
var files = new List<System.IO.FileInfo>();
var fileNames = System.IO.Directory.GetFiles(path, "*.*", System.IO.SearchOption.AllDirectories);
foreach (var item in fileNames)
{
files.Add(new System.IO.FileInfo(item));
}
return files;
}
}
}