EF中Linq的用法
程序员文章站
2022-06-11 22:49:59
...
Linq
一、用法举例
1.In操作
代码如下（示例）：
private static void QueryIn()
{
using (CodeFirstContext context = new CodeFirstContext())
{
int[] ids = new int[] { 1, 2, 3 };
var clientList1 = from c in context.Clients
where ids.Contains(c.Id)
select c;
foreach (var item in clientList1)
{
Console.WriteLine(item.ClientName);
}
var clientList2 = context.Clients.Where(c => ids.Contains(c.Id));
foreach (var item in clientList2)
{
Console.WriteLine(item.ClientName);
}
var clientList3 = context.Clients.Where(c => new string[] { "Fisea wannfsfsf", "Ganter" }.Contains(c.ClientName));
clientList3.ToList().ForEach(c =>
{
Console.WriteLine(c.ClientName);
});
foreach (var item in clientList3)
{
Console.WriteLine(item.ClientName);
}
}
//Sql:
// SELECT
//[Extent1].[Id] AS[Id],
//[Extent1].[Name] AS[Name],
//[Extent1].[Tel] AS[Tel],
//[Extent1].[Sex] AS[Sex],
//[Extent1].[Address] AS[Address],
//[Extent1].[CreateTime] AS[CreateTime],
//[Extent1].[CreatorId] AS[CreatorId]
//FROM[dbo].[Client] AS[Extent1]
//WHERE[Extent1].[Id] IN(1, 2, 3)
}
2.分页
代码如下（示例）：
private static void QueryPaging()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = context.Clients.Where(c => new int[] { 1, 2, 3 }.Contains(c.Id))
.OrderBy(c => c.CreateTime)
.Select(c => new
{
Name = c.ClientName,
Addr = c.Address
})
.Skip(1)
.Take(3);
foreach (var item in list)
{
Console.WriteLine($"{item.Name},{item.Addr}");
}
}
//Sql:
// SELECT
//[Extent1].[Id] AS[Id],
//[Extent1].[Name] AS[Name],
//[Extent1].[Address] AS[Address]
//FROM[dbo].[Client] AS[Extent1]
//WHERE[Extent1].[Id] IN(1, 2, 3)
//ORDER BY row_number() OVER(ORDER BY[Extent1].[CreateTime] ASC)
//OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY
}
3.Like
代码如下（示例）：
private static void QueryLike()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = context.Clients.Where(c => c.Address.StartsWith("深圳") && c.ClientName.EndsWith("a"))
.Where(c => c.Tel.Contains("159"))
.OrderBy(c => c.CreateTime);
foreach (var client in list)
{
Console.WriteLine(client.ClientName);
}
}
//Sq;
// SELECT
//[Extent1].[Id] AS[Id],
//[Extent1].[Name] AS[Name],
//[Extent1].[Tel] AS[Tel],
//[Extent1].[Sex] AS[Sex],
//[Extent1].[Address] AS[Address],
//[Extent1].[CreateTime] AS[CreateTime],
//[Extent1].[CreatorId] AS[CreatorId]
//FROM[dbo].[Client] AS[Extent1]
//WHERE([Extent1].[Address] LIKE N'深圳%') AND([Extent1].[Name] LIKE N'%a') AND([Extent1].[Tel] LIKE N'%159%')
//ORDER BY[Extent1].[CreateTime] ASC
}
4.Join
代码如下（示例）：
private static void QueryJoin()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = (from o in context.Orders
join p in context.Products on o.ProId equals p.Id
where new int[] { 1, 2, 3 }.Contains(p.Id)
select new
{
UnitPrice = o.Price,
Name = p.Name,
Desc = p.Description
}).OrderBy(c => c.UnitPrice).Skip(1).Take(3);
foreach (var item in list)
{
Console.WriteLine(item.UnitPrice);
}
}
//Sql:
// SELECT
// [Extent1].[ProductId] AS[ProductId],
//[Extent1].[Price] AS[Price],
//[Extent2].[Name] AS[Name],
//[Extent2].[Desc] AS[Desc]
//FROM[dbo].[Order] AS[Extent1]
//INNER JOIN[dbo].[Product] AS[Extent2] ON[Extent1].[ProductId] = [Extent2].[Id]
//WHERE[Extent2].[Id] IN(1, 2, 3)
//ORDER BY row_number() OVER(ORDER BY[Extent1].[Price] ASC)
//OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY
}
5.LeftJoin
代码如下（示例）：
private static void QueryLeftJoin()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from c in context.Clients
join o in context.Orders on c.Id equals o.ClientId
into co
from d in co.DefaultIfEmpty()
where new int[] { 1, 2, 3 }.Contains(c.Id)
select new
{
c.Id,
Name = c.ClientName,
d.Price
};
foreach (var item in list)
{
Console.WriteLine();
}
}
//Sql:
// SELECT
// [Extent1].[Id] AS[Id],
//[Extent1].[Name] AS[Name],
//[Extent2].[Price] AS[Price]
//FROM[dbo].[Client] AS[Extent1]
//LEFT OUTER JOIN[dbo].[Order] AS[Extent2] ON[Extent1].[Id] = [Extent2].[ClientId]
//WHERE[Extent1].[Id] IN(1, 2, 3)
}
6.UpdateInfoBySql
代码如下（示例）：
private static void UpdateBySql()
{
using (CodeFirstContext context = new CodeFirstContext())
{
DbContextTransaction trans = null;
try
{
trans = context.Database.BeginTransaction();
string sql = "update dbo.Client set name='fisea.wang' where [email protected]";
SqlParameter parameter = new SqlParameter("@Id", 1);
context.Database.ExecuteSqlCommand(sql, parameter);
trans.Commit();
}
catch (Exception ex)
{
trans?.Rollback();
throw ex;
}
finally
{
trans?.Dispose();
}
}
}
7.QueryDataBySql
代码如下（示例）：
private static void QueryBySql()
{
using (CodeFirstContext context = new CodeFirstContext())
{
DbContextTransaction trans = null;
try
{
trans = context.Database.BeginTransaction();
string sql = "Select * from dbo.Client where Name ='@Name'";
SqlParameter sqlParameter = new SqlParameter("@Name", "fisea.wang");
var clientInfos = context.Database.SqlQuery<ClientInfo>(sql, sqlParameter).ToList();
trans.Commit();
}
catch (Exception ex)
{
trans?.Rollback();
throw ex;
}
finally
{
trans?.Dispose();
}
}
}
8.Sub Query
代码如下（示例）：
/// <summary>
/// 描述：查询订单数超过 5 的顾客信息
/// </summary>
private static void QuerySub()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var clientList = from c in context.Clients
where (
from o in context.Orders
group o by o.ClientId into ord
where ord.Count() > 5
select ord.Key
).Contains(c.Id)
select c;
foreach (var item in clientList)
{
Console.WriteLine(item.ClientName);
}
}
//Sql:
// SELECT
//[Extent1].[Id] AS[Id],
//[Extent1].[Name] AS[Name],
//[Extent1].[Tel] AS[Tel],
//[Extent1].[Sex] AS[Sex],
//[Extent1].[Address] AS[Address],
//[Extent1].[CreateTime] AS[CreateTime],
//[Extent1].[CreatorId] AS[CreatorId]
//FROM[dbo].[Client] AS[Extent1]
//WHERE EXISTS(SELECT
// 1 AS[C1]
// FROM(SELECT
// [Extent2].[ClientId] AS[K1],
// COUNT(1) AS[A1]
// FROM[dbo].[Order] AS[Extent2]
// GROUP BY[Extent2].[ClientId]
// ) AS[GroupBy1]
// WHERE([GroupBy1].[A1] > 5) AND([GroupBy1].[K1] = [Extent1].[Id])
//)
}
9.Group by
代码如下（示例）：
private static void QueryGrpBy()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from o in context.Orders
group o by o.ClientId;
foreach (var item1 in list)
{
Console.WriteLine(item1.Key);
foreach (var item2 in item1)
{
Console.WriteLine(item2.Price);
}
}
}
//Sql:
// SELECT
//[Project2].[ClientId] AS[ClientId],
//[Project2].[C1] AS[C1],
//[Project2].[Id] AS[Id],
//[Project2].[ClientId1] AS[ClientId1],
//[Project2].[ProductId] AS[ProductId],
//[Project2].[Amount] AS[Amount],
//[Project2].[Price] AS[Price],
//[Project2].[CreateTime] AS[CreateTime],
//[Project2].[CreatorId] AS[CreatorId]
//FROM(SELECT
// [Distinct1].[ClientId] AS[ClientId],
// [Extent2].[Id] AS[Id],
// [Extent2].[ClientId] AS[ClientId1],
// [Extent2].[ProductId] AS[ProductId],
// [Extent2].[Amount] AS[Amount],
// [Extent2].[Price] AS[Price],
// [Extent2].[CreateTime] AS[CreateTime],
// [Extent2].[CreatorId] AS[CreatorId],
// CASE WHEN([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS[C1]
// FROM(SELECT DISTINCT
// [Extent1].[ClientId] AS[ClientId]
// FROM[dbo].[Order] AS[Extent1]) AS[Distinct1]
// LEFT OUTER JOIN[dbo].[Order] AS[Extent2] ON[Distinct1].[ClientId] = [Extent2].[ClientId]
//) AS[Project2]
//ORDER BY[Project2].[ClientId] ASC, [Project2].[C1] ASC
}
10.Max
代码如下（示例）：
/// <summary>
/// 说明：from p in db.Products 表示从表中将产品对象取出来。group p by p.CategoryID into pro表示对p按CategoryId字段归类。其结果命名为g，一旦重新命名，p的作用域就结束了，所以，最后select时，只能select pro。
/// </summary>
private static void QueryMax()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from p in context.Products
group p by p.CategoryId into pro
select new
{
Id = pro.Key,
MaxPrice = pro.Max(q => q.Price)
};
foreach (var item in list)
{
Console.WriteLine($"{item.Id},{item.MaxPrice}");
}
}
//Sql:
// SELECT
//[GroupBy1].[K1] AS[CategoryId],
//[GroupBy1].[A1] AS[C1]
//FROM(SELECT
// [Extent1].[CategoryId] AS[K1],
// MAX([Extent1].[Price]) AS[A1]
// FROM[dbo].[Product] AS[Extent1]
// GROUP BY[Extent1].[CategoryId]
//) AS[GroupBy1]
}
11.Min
代码如下（示例）：
private static void QueryMin()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from p in context.Products
group p by p.CategoryId into pro
select new
{
Id = pro.Key,
MinPrice = pro.Min(q => q.Price)
};
foreach (var item in list)
{
Console.WriteLine($"{item.Id},{item.MinPrice}");
}
}
//Sql:
// SELECT
// [GroupBy1].[K1] AS[CategoryId],
//[GroupBy1].[A1] AS[C1]
//FROM(SELECT
// [Extent1].[CategoryId] AS[K1],
// MIN([Extent1].[Price]) AS[A1]
// FROM[dbo].[Product] AS[Extent1]
// GROUP BY[Extent1].[CategoryId]
//) AS[GroupBy1]
}
12.Avg
代码如下（示例）：
private static void QueryAvg()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from p in context.Products
group p by p.CategoryId into pro
select new
{
Id = pro.Key,
AvgPrice = pro.Average(q => q.Price)
};
foreach (var item in list)
{
Console.WriteLine($"{item.Id},{item.AvgPrice}");
}
}
//Sql:
// SELECT
//[GroupBy1].[K1] AS[CategoryId],
//[GroupBy1].[A1] AS[C1]
//FROM(SELECT
// [Extent1].[CategoryId] AS[K1],
// AVG([Extent1].[Price]) AS[A1]
// FROM[dbo].[Product] AS[Extent1]
// GROUP BY[Extent1].[CategoryId]
//) AS[GroupBy1]
}
13.Count
代码如下（示例）：
private static void QueryCnt()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from p in context.Products
group p by p.CategoryId into pro
select new
{
Id = pro.Key,
Count = pro.Count()
};
foreach (var item in list)
{
Console.WriteLine($"{item.Id},{item.Count}");
}
}
//Sql:
// SELECT
//[GroupBy1].[K1] AS[CategoryId],
//[GroupBy1].[A1] AS[C1]
//FROM(SELECT
// [Extent1].[CategoryId] AS[K1],
// COUNT(1) AS[A1]
// FROM[dbo].[Product] AS[Extent1]
// GROUP BY[Extent1].[CategoryId]
//) AS[GroupBy1]
}
14.Group by 带有Where条件
代码如下（示例）：
private static void QueryWhereGrp()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from p in context.Products
group p by p.CategoryId into pro
where pro.Count() > 10
select new
{
Id = pro.Key,
Cnt = pro.Count()
};
foreach (var item in list)
{
Console.WriteLine(item.Id);
}
}
//Sql:
// SELECT
//[GroupBy1].[K1] AS[CategoryId],
//[GroupBy1].[A2] AS[C1]
//FROM(SELECT
// [Extent1].[CategoryId] AS[K1],
// COUNT(1) AS[A1],
// COUNT(1) AS[A2]
// FROM[dbo].[Product] AS[Extent1]
// GROUP BY[Extent1].[CategoryId]
//) AS[GroupBy1]
//WHERE[GroupBy1].[A1] > 10
}
15.多列Group by
代码如下（示例）：
private static void QueryGrpByMultiCols()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from p in context.Products
group p by new
{
p.CategoryId,
p.SupplierId
}
into pro
select new
{
pro.Key,
pro
};
foreach (var item in list)
{
Console.WriteLine(item.Key.CategoryId);
Console.WriteLine(item.Key.SupplierId);
}
}
//Sql:
// SELECT
//[Project2].[CategoryId] AS[CategoryId],
//[Project2].[C1] AS[C1],
//[Project2].[SupplierId] AS[SupplierId],
//[Project2].[C2] AS[C2],
//[Project2].[Id] AS[Id],
//[Project2].[CategoryId1] AS[CategoryId1],
//[Project2].[SupplierId1] AS[SupplierId1],
//[Project2].[Name] AS[Name],
//[Project2].[Price] AS[Price],
//[Project2].[Desc] AS[Desc]
//FROM(SELECT
// [Distinct1].[CategoryId] AS[CategoryId],
// [Distinct1].[SupplierId] AS[SupplierId],
// [Distinct1].[C1] AS[C1],
// [Extent2].[Id] AS[Id],
// [Extent2].[CategoryId] AS[CategoryId1],
// [Extent2].[SupplierId] AS[SupplierId1],
// [Extent2].[Name] AS[Name],
// [Extent2].[Price] AS[Price],
// [Extent2].[Desc] AS[Desc],
// CASE WHEN([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS[C2]
// FROM(SELECT DISTINCT
// [Extent1].[CategoryId] AS[CategoryId],
// [Extent1].[SupplierId] AS[SupplierId],
// 1 AS[C1]
// FROM[dbo].[Product] AS[Extent1]) AS[Distinct1]
// LEFT OUTER JOIN[dbo].[Product] AS[Extent2] ON([Distinct1].[CategoryId] = [Extent2].[CategoryId]) AND([Distinct1].[SupplierId] = [Extent2].[SupplierId])
//) AS[Project2]
//ORDER BY[Project2].[C1] ASC, [Project2].[CategoryId] ASC, [Project2].[SupplierId] ASC, [Project2].[C2] ASC
}
16.Group by expression
代码如下（示例）：
/// <summary>
/// 按产品单价是否大于10分类。其结果分为两类，大于的是一类，小于及等于为另一类。
/// </summary>
private static void QueryByExp()
{
using (CodeFirstContext context = new CodeFirstContext())
{
var list = from p in context.Products
group p by new
{
IsBigPrice= p.Price>10
}
into pro
select pro;
foreach (var item in list)
{
Console.WriteLine(item.Key);
}
}
}
17.Any
代码如下（示例）：
private static void QueryNotExists()
{
using (EFDBFirstDemoEntities ctx = new EFDBFirstDemoEntities())
{
var list = from c in ctx.Clients
where !c.Orders.Any()
select c;
foreach (var item in list)
{
Console.WriteLine(item.Name);
}
}
//Sql:
// SELECT
// [Extent1].[Id] AS[Id],
// [Extent1].[Name] AS[Name],
// [Extent1].[Tel] AS[Tel],
// [Extent1].[Sex] AS[Sex],
// [Extent1].[Address] AS[Address],
// [Extent1].[CreateTime] AS[CreateTime],
// [Extent1].[CreatorId] AS[CreatorId],
// [Extent1].[City] AS[City]
// FROM[dbo].[Client] AS[Extent1]
// WHERE NOT EXISTS(SELECT
// 1 AS[C1]
// FROM[dbo].[Order] AS[Extent2]
// WHERE[Extent1].[Id] = [Extent2].[ClientId]
//)
}
18.Any中带有where条件
代码如下（示例）：
private static void QueryExistsByWhere()
{
using (EFDBFirstDemoEntities ctx = new EFDBFirstDemoEntities())
{
var list = from g in ctx.Categories
where g.Products.Any(p => p.Discontinued)
select g;
foreach (var item in list)
{
Console.WriteLine(item.Name);
}
}
//Sql:
// SELECT
//[Extent1].[Id] AS[Id],
//[Extent1].[Name] AS[Name],
//[Extent1].[Desc] AS[Desc]
//FROM[dbo].[Category] AS[Extent1]
//WHERE EXISTS(SELECT
// 1 AS[C1]
// FROM[dbo].[Product] AS[Extent2]
// WHERE([Extent1].[Id] = [Extent2].[CatetoryId]) AND([Extent2].[Discontinued] = 1)
//)
}
19.All
代码如下（示例）：
private static void QueryByAll()
{
using (EFDBFirstDemoEntities ctx = new EFDBFirstDemoEntities())
{
var list = from c in ctx.Clients
where c.Orders.All(o => o.ShipCity == c.City)
select c;
foreach (var item in list)
{
Console.WriteLine(item.Name);
}
}
//Sql::
// SELECT
//[Extent1].[Id] AS[Id],
//[Extent1].[Name] AS[Name],
//[Extent1].[Tel] AS[Tel],
//[Extent1].[Sex] AS[Sex],
//[Extent1].[Address] AS[Address],
//[Extent1].[CreateTime] AS[CreateTime],
//[Extent1].[CreatorId] AS[CreatorId],
//[Extent1].[City] AS[City]
//FROM[dbo].[Client] AS[Extent1]
//WHERE NOT EXISTS(SELECT
// 1 AS[C1]
// FROM[dbo].[Order] AS[Extent2]
// WHERE([Extent1].[Id] = [Extent2].[ClientId]) AND
// ((NOT(([Extent2].[ShipCity] = [Extent1].[City]) AND
// ((CASE WHEN([Extent2].[ShipCity] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN([Extent1].[City] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))) OR(CASE WHEN(([Extent2].[ShipCity] = [Extent1].[City]) OR(([Extent2].[ShipCity] IS NULL) AND([Extent1].[City] IS NULL))) THEN cast(1 as bit) WHEN(NOT(([Extent2].[ShipCity] = [Extent1].[City]) AND((CASE WHEN([Extent2].[ShipCity] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN([Extent1].[City] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))) THEN cast(0 as bit) END IS NULL))
//)
}
20.Not Contain
代码如下（示例）：
private static void QueryByNotContains()
{
using (EFDBFirstDemoEntities ctx = new EFDBFirstDemoEntities())
{
var list = from o in ctx.Orders
//where !(new string[] { "shenzhen", "guangzhou" }).Contains(o.ShipCity)
where !(new string[] { "shenzhen", "guangzhou" }.Contains(o.ShipCity))
select o;
foreach (var item in list)
{
Console.WriteLine(item.ShipCity);
}
}
//Sql:
// SELECT
//[Extent1].[Id] AS[Id],
//[Extent1].[ClientId] AS[ClientId],
//[Extent1].[ProductId] AS[ProductId],
//[Extent1].[Amount] AS[Amount],
//[Extent1].[Price] AS[Price],
//[Extent1].[CreateTime] AS[CreateTime],
//[Extent1].[CreatorId] AS[CreatorId],
//[Extent1].[ShipCity] AS[ShipCity]
//FROM[dbo].[Order] AS[Extent1]
//WHERE NOT(([Extent1].[ShipCity] IN (N'shenzhen', N'guangzhou')) AND([Extent1].[ShipCity] IS NOT NULL))
}
21.Group by后，求Min值
代码如下（示例）：
/// <summary>
/// 查找每个类别中单价最低的产品
/// </summary>
private static void QueryMinPriceProduct()
{
using (EFDBFirstDemoEntities ctx = new EFDBFirstDemoEntities())
{
var list = from p in ctx.Products
group p by p.CatetoryId into g
select new
{
CategoryId=g.Key,
MinPricePro=from m in g
where m.Price==g.Min(n=>n.Price)
select m
};
foreach (var item in list)
{
Console.WriteLine(item.MinPricePro.Count());
}
//Sql:
// SELECT
//[Project1].[CatetoryId] AS[CatetoryId],
//[Project1].[C1] AS[C1],
//[Project1].[Id] AS[Id],
//[Project1].[Name] AS[Name],
//[Project1].[Price] AS[Price],
//[Project1].[Desc] AS[Desc],
//[Project1].[CatetoryId1] AS[CatetoryId1],
//[Project1].[Discontinued] AS[Discontinued]
//FROM(SELECT
// [GroupBy1].[K1] AS[CatetoryId],
// [Extent2].[Id] AS[Id],
// [Extent2].[Name] AS[Name],
// [Extent2].[Price] AS[Price],
// [Extent2].[Desc] AS[Desc],
// [Extent2].[CatetoryId] AS[CatetoryId1],
// [Extent2].[Discontinued] AS[Discontinued],
// CASE WHEN([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS[C1]
// FROM(SELECT
// [Extent1].[CatetoryId] AS[K1],
// MIN([Extent1].[Price]) AS[A1]
// FROM[dbo].[Product] AS[Extent1]
// GROUP BY[Extent1].[CatetoryId] ) AS[GroupBy1]
// LEFT OUTER JOIN[dbo].[Product] AS[Extent2] ON([GroupBy1].[K1] = [Extent2].[CatetoryId]) AND([Extent2].[Price] = [GroupBy1].[A1])
//) AS[Project1]
//ORDER BY[Project1].[CatetoryId] ASC, [Project1].[C1] ASC
}
}