标签标签标签

一句话的事儿：本章将EFCore如何添加和查询数据

首先添加日志记录

补充说明：

记录添加、查询的记录

01.安装插件Microsoft.Extensions.Logging.Console

02.使用插件前，先引用

private static readonly ILoggerFactory ConsoleLoggerFactory =
  LoggerFactory.Create(builder =>
  {
      builder.AddFilter((category, level) =>
              category == DbLoggerCategory.Database.Command.Name
              && level == LogLevel.Information)
      .AddConsole();
  });
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
  optionsBuilder.UseLoggerFactory(ConsoleLoggerFactory)
                .UseSqlServer("Data Source=.;Initial Catalog=EFCoreDemo;Persist Security Info=True;User ID=sa;Password=Atser123");
}

03.log效果

添加

01.添加一个serieA

static void Main(string[] args)
{
  using var context = new DataContext();
  var serieA = new League
  {
      Country = "ITaly",
      Name = "Serie A"
  };
  context.Add(serieA);
  var count = context.SaveChanges();
  Console.WriteLine(count);
}

02.添加集合以及不同类型，如这里的Club于League是不同的类型

```csharp static void Main(string[] args) { using var context = new DataContext(); //var serieA = new League //{ // Country = “ITaly”, // Name = “Serie A” //};

var serieB = new League {

  Country = "ITaly",
  Name = "Serie B"

};

var serieC = new League {

  Country = "ITaly",
  Name = "Serie C"

};

var serieA = context.Leagues.Single(x=>x.Name== “Serie A”); var milan = new Club {

  Name = "AC milan",
  City = "MiLan",
  DateOfEstablishment = new DateTime(1899, 12, 16),
  League = serieA

};

//context.Add(serieA);
//context.AddRange(serieB, serieC);
//context.AddRange(new List<League> { serieB, serieC });
context.AddRange(serieB, serieC,milan);
var count = context.SaveChanges();
Console.WriteLine(count);

}

![image.png](https://cdn.nlark.com/yuque/0/2020/png/446847/1598627338008-cf96cf6e-fb77-43eb-abde-0b42031d6f0c.png#align=left&display=inline&height=638&margin=%5Bobject%20Object%5D&name=image.png&originHeight=1276&originWidth=2069&size=271721&status=done&style=none&width=1034.5)
<a name="isj2L"></a>
# 查询
<a name="QwC2W"></a>
## 01.基础查询
```csharp
//01.查询League中所有的数据
//01.1 LinQ
var leagues = context.Leagues
    .Where(x => x.Country == "ITaly")
    .ToList();
//01.2 From ... Where ...
var leagues2 = (from lg in context.Leagues
                where lg.Country == "ITaly"
                select lg).ToList();
//补充说明：只有执行ToList()，遍历等才会执行查询
foreach (var league in leagues)
{
    Console.WriteLine(league.Name);
}

02.从基础查询，我们不能得知，查询条件是写死的，即x => x.Country == “ITaly”，那么此时的SQL 语句是：

03.接下来，我们采取引用变量的方法：

var italy = "ITaly";
var leagues = context.Leagues
    .Where(x => x.Country == italy)
    .ToList();

04.此时，我们可以观察到SQL语句中的参数是❓，这里我们需要更改一下默认设置，让其输出

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLoggerFactory(ConsoleLoggerFactory)
                  .EnableSensitiveDataLogging()        //更改处，添加
                  .UseSqlServer("Data Source=.;Initial Catalog=EFCoreDemo;Persist Security Info=True;User ID=sa;Password=Atser123");
}

05.接下来做一个模糊查询，比如某个国家英文字母中含有e,Country Like “%e%”

方法一

var leagues = context.Leagues
  .Where(x => x.Country.Contains("e"))
  .ToList();

方法二

var leagues = context.Leagues
  .Where(X=>EF.Functions.Like(X.Country,"%e%"))
  .ToList();

温馨小贴士：

:::info 遇到哪些命令才真正执行查询呢？

ToList()
First(),FirstOrDefault()
Single(),SingleOrDefault()
Last(),LastOrDefault()
Count(),LongCount();Min(),Max(),Average();Sum()
Find() :::
06.直接对主键进行查询
```
var first = context.Leagues.SingleOrDefault(x=>x.Id==2);
var one = context.Leagues.Find(2);
```
07.Find（）的重大发现，
:::info 此时我们不难发现，SQL语句并没有执行两遍。
这是因为Find会先去内存中寻找，如果已经查询过了，则直接使用，那么如果我们将其顺序调换一下会怎样呢？咱们接着尝试。。。 :::
08.对调SingleOrDefault()与Find()查询顺序
```csharp var one = context.Leagues.Find(2); var first = context.Leagues.SingleOrDefault(x=>x.Id==2);

![image.png](https://cdn.nlark.com/yuque/0/2020/png/446847/1598629875986-cc32d1aa-4721-46c3-98fb-5d0ea4b14a52.png#align=left&display=inline&height=288&margin=%5Bobject%20Object%5D&name=image.png&originHeight=575&originWidth=1920&size=123835&status=done&style=none&width=960)<br />很显然，SQL 语句执行了两次，这到底是什么原因呢？<br />其实原理很简单，就是Find（）会去内存找，第一次寻找的结果是没有找到，那么就开始执行查询；而SingleOrDefault(）是不考虑内存中是否有做过查询动作的
<a name="1xbuy"></a>
## 09.LastOrDefault（）查询
```csharp
var last = context.Leagues.LastOrDefault(x=>x.Name.Contains("e"));

:::warning System.InvalidOperationException: ‘The LINQ expression ‘DbSet
.Where(l => l.Name.Contains(“e”)) ::: :::warning .LastOrDefault()’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.’ :::

这是因为我们不知道哪一个才是最后一个，所以我们要先进行排序

var last = context.Leagues
    .OrderByDescending(x => x.Id)
    .LastOrDefault(x => x.Name.Contains("e"));

查询成功

本文作者：GeekPower - Felix Sun
版权声明： 本博客所有文章除特别声明外，均采用 MIT 许可协议。转载请注明出处！

后端教程

05.添加、查询