EFCore抓取Sql语句
日志输出
1).Nuget引入
Microsoft.Extensions.Logging.Console
2).在第一篇EF中的 中的OnConfiguring方法添加配置
optionsBuilder.UseLoggerFactory(LoggerFactory.Create(builder=> {
builder.AddConsole();
}));
3).结果
EFCoreLingQuery
public static void Show()
{
#region 其他查询
using (UserContext context = new UserContext())
{
//Lambda查询
{
var idlist = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 134, 46, 23, 46, 34 };//in查询
var list = context.UserData.Where(u => idlist.Contains(u.PId));//in查询
foreach (var item in list)
{
Console.WriteLine(item.PName);
}
}
//LINQ查询
{
var list = from u in context.UserData
where new int[] { 1, 2, 3, 4, 5, 6, 7, 8 }.Contains(u.PId)
select u;
foreach (var item in list)
{
Console.WriteLine(item.PName);
}
}
//一般用于分页:Skip、Take——跳过第一条2,取2条
{
//var list = (from u in context.Employees
// where new int[] { 1, 2, 3, 4, 5, 6, 7, 7 }.Contains(u.Id)
// orderby u.Id
// select new
// {
// Name = u.Name,
// Img = u.PictureUrl
// }).Skip(1).Take(2);
//foreach (var item in list)
//{
// Console.WriteLine(item.Name);
//}
}
//多条件
{
var list = context.UserData.Where(u => u.PName.StartsWith("h") && u.PName.EndsWith("o"))
.Where(u => u.PName.StartsWith("h"))
.Where(u => u.PName.Length < 5)
.OrderBy(u => u.PId);
foreach (var item in list)
{
Console.WriteLine(item.PName);
}
}
//连接查询
{
var list = (from u in context.UserData
join c in context.Departments
on u.PId equals c.DId //注意:条件不能写 "==" 等于号,要使用equals
where new int[] { 1, 2, 3, 4, 5, 6, 7 }.Contains(u.PId)
select new
{
Id = u.PId,
Name = u.PName,
Departments = c.DName
}).OrderBy(u => u.Id);
foreach (var item in list)
{
Console.WriteLine($"{item.Id}---{item.Name}---{item.Departments}");
}
}
//左连接
//没有右连接,如果要做右连接,就把顺序调换一下就行了
{
var list = (from u in context.UserData
join c in context.Departments on u.PId equals c.DId
into ucList
from uc in ucList.DefaultIfEmpty()
where new int[] { 1, 2, 3, 4, 5, 6 }.Contains(u.PId)
select new
{
Id = u.PId,
Name = u.PName,
Departments = uc.DName
}).OrderBy(u => u.Id);
foreach (var item in list)
{
Console.WriteLine($"{item.Id}---{item.Name}---{item.Departments}");
}
}
#endregion
}
EFCoreState
数据库的增改动作都是统一由SaveChanges之后,统一提交到数据库;通过状态跟踪,任何一个增删改查的操作都会记录一个状态在内存中;一旦SaveChanges,就根据状态去落实到数据中去的。
状态跟踪实现了增删改便捷,但是也会有性能损耗;
EntityState.cs
// 摘要:
// The state in which an entity is being tracked by a context.
public enum EntityState
{
//
// 摘要:
// The entity is not being tracked by the context.
Detached = 0,
//
// 摘要:
// The entity is being tracked by the context and exists in the database. Its property
// values have not changed from the values in the database.
Unchanged = 1,
//
// 摘要:
// The entity is being tracked by the context and exists in the database. It has
// been marked for deletion from the database.
Deleted = 2,
//
// 摘要:
// The entity is being tracked by the context and exists in the database. Some or
// all of its property values have been modified.
Modified = 3,
//
// 摘要:
// The entity is being tracked by the context but does not yet exist in the database.
Added = 4
}
使用
Console.WriteLine(context.Entry<UserDatum>(Add).State);
EFCore事务
SaveChange就是保证事务的转述;多个对于数据库的操作,统一SaveChanges,就是开启了一个事物。
EF Core自带的两个事务:
(1)SavaChanges
(2)IDbContextTransaction
public class TransactionTest
{
public static void Show()
{
using (UserContext context = new UserContext())
{
using (IDbContextTransaction transaction =context.Database.BeginTransaction())
{
try
{
context.UserData.Add(new UserDatum()
{
PId = 2,
PName = "hello",
PAge = "892",
PInfo = "what happen to you?"
});
context.UserData.Add(new UserDatum()
{
PId = 3,
PName = "login",
PAge = "5367",
PInfo = "how are you?"
});
//保存修改
context.SaveChanges();
//提交事物
context.Database.CommitTransaction();
} catch(Exception ex)
{
//事物的回滚
transaction.Rollback();
throw;
}
}
}
}
}