Ef core 一些基础查询

在进行查询介绍之前，我们要先把一些数据插入到数据库当中

private static void InitData()
{
    List<Student> students = GetStudents();
    var sp = GetServiceProvider(); // 获取依赖注入的容器
    var malemaDbContext = sp.GetService<MalemaDbContext>();
    if (malemaDbContext.Students.Count() < 1)
    {
        malemaDbContext.AddRange(students);
        malemaDbContext.SaveChanges();
    }
}

private static List<Student> GetStudents()
{
    var student1 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-12), ClassId = 1, Grade = 2, Name = "张三", TotalScore = 200 };
    var student2 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 1, Grade = 2, Name = "李四", TotalScore = 198 };
    var student3 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 2, Grade = 2, Name = "王五", TotalScore = 197 };
    var student4 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 2, Grade = 2, Name = "赵七", TotalScore = 196 };
    var student5 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 2, Grade = 2, Name = "狗蛋", TotalScore = 195 };
    var student6 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 2, Grade = 2, Name = "江小牙", TotalScore = 180 };
    var students = new List<Student>()
    {
        student1,
        student2,
        student3,
        student4,
        student5,
        student6
    };
    return students;
}

private static ServiceProvider GetSserviceProvider()
{
    var connectionString = "Data Source=127.0.0.1;Initial Catalog=MalemaEFCoreExample;Persist Security Info=True;User Id=sa;Password=xxxxxxx";
    var optionBuilder = new DbContextOptionsBuilder<MalemaDbContext>();
    optionBuilder.UseSqlServer(connectionString);
    var services = new ServiceCollection();
    // services.AddDbContext<MalemaDbContext>(options => options.UseSqlServer(connectionString)); //注入DbContext
    // services.AddDbContextFactory<MalemaDbContext>(options => options.UseSqlServer(connectionString)); // 注入 DbContext factory
    services.AddDbContextPool<MalemaDbContext>(options => options.UseSqlServer(connectionString), poolSize: 64); //注入 DbContext 池
    // 注入 DbContext factory 来从池中生成 Dbcontext 
    services.AddPooledDbContextFactory<MalemaDbContext>(options => options.UseSqlServer(connectionString), poolSize: 64); 
    // services.AddScoped<StudentService, StudentService>();
    var sp = services.BuildServiceProvider();
    return sp;
}

GetServiceProvider() 是依赖注入的东西 https://www.malema.net/efcore/advanced/dbcontext-di.html 看到相关的代码

First FirstOrDefault 取出第一条

取出第一条，默认是按照 Id 升序的， 如果没有会抛出异常 System.InvalidOperationException:“Sequence contains no elements”

            var student = malemaDbContext.Students.First();
            Console.WriteLine(student);

生成的sql如下, 会取出我们数据库的第一条 Id:1,Name:张三,TotalScore:200.00

      SELECT TOP(1) [s].[Id], [s].[BirthDate], [s].[ClassId], [s].[Grade], [s].[Name], [s].[TotalScore]
      FROM [Students] AS [s]

在条件过滤写在FirstOrDefault里面 (当然First也是支持条件过滤的) 取出Id为2的第一条。如果没有的话会返回Null

            var studentWithId2_1 = malemaDbContext.Students.FirstOrDefault(x => x.Id == 2);
            Console.WriteLine(studentWithId2_1);

生成的 sql如下

     SELECT TOP(1) [s].[Id], [s].[BirthDate], [s].[ClassId], [s].[Grade], [s].[Name], [s].[TotalScore]
      FROM [Students] AS [s]
      WHERE [s].[Id] = 2

在where里面进行条件过滤 如果没有这条记录的话返回null

            var studentWithId2_2 = malemaDbContext.Students.Where(x => x.Id == 2).FirstOrDefault();
            Console.WriteLine(studentWithId2_2);

生成的 sql如下,跟上面是一样的

      SELECT TOP(1) [s].[Id], [s].[BirthDate], [s].[ClassId], [s].[Grade], [s].[Name], [s].[TotalScore]
      FROM [Students] AS [s]
      WHERE [s].[Id] = 2

取出来的数据是 Id:2,Name:李四,TotalScore:198.00

Last LastOrDefault 取出最后一条

Last跟first有点区别，使用前得先用OderBy进行排序

var lastStudent = malemaDbContext.Students.OrderBy(x => x.Id).Last();
Console.WriteLine(lastStudent);

生成的sql如下

      SELECT TOP(1) [s].[Id], [s].[BirthDate], [s].[ClassId], [s].[Grade], [s].[Name], [s].[TotalScore]
      FROM [Students] AS [s]
      ORDER BY [s].[Id] DESC

取出了最后一条 Id:6,Name:江小牙,TotalScore:180.00 Last如果不存在的话是会抛异常的。我们可以用LastOrDefault替代它

Single SingleOrDefault 取出一条

Single 跟 First有一个重大的区别是， Single是只允许一条，如果有多条满足条件则会抛出异常 System.InvalidOperationException:“Sequence contains more than one element”

var over198 = malemaDbContext.Students.SingleOrDefault(x => x.Name == "张三");
Console.WriteLine(over198);

生成的sql语句如下

  SELECT TOP(2) [s].[Id], [s].[BirthDate], [s].[ClassId], [s].[Grade], [s].[Name], [s].[TotalScore]
      FROM [Students] AS [s]
      WHERE [s].[Name] = N'张三'

是直接用了 Top 2的方式来查询。如果能查出两条的话，就会抛出异常了。

Where ToList 取出所有的数据

把满足条件的全部取出来，List正常要放在Where之后，或者take之后。不然会把所有的数据都取出来。这个通常是不行的。太慢了。 有很多写代码的时候没有注意到这会出现这个问题。还有的人会先ToList之后再进行where的过滤，这个也是会有性能问题的。

var class1List = malemaDbContext.Students.Where(x => x.ClassId == 1).ToList();

生成的sql语句如下。可以看到 top没了

SELECT [s].[Id], [s].[BirthDate], [s].[ClassId], [s].[Grade], [s].[Name], [s].[TotalScore]
      FROM [Students] AS [s]
      WHERE [s].[ClassId] = 1

所有class为1的都会被取出来 有2条

Count 统计满足条件的数量

统计 classId为1的数量。 当然count也是可以放在where之后的

var class1Count = malemaDbContext.Students.Count(x => x.ClassId == 1);
Console.WriteLine(class1Count);

生成的sql语句如下

   SELECT COUNT(*)
      FROM [Students] AS [s]
      WHERE [s].[ClassId] = 1

Min 取得最小值

必须得指定用哪一个属性来比较

var minScore = malemaDbContext.Students.Min(it => it.TotalScore);
Console.WriteLine(minScore); // 180
var minScore2 = malemaDbContext.Students.Min(it => it.TotalScore + it.Grade); //两个可以一起运算的属性也是没有问题的

生成的sql语句如下

SELECT MIN([s].[TotalScore])
    FROM [Students] AS [s]

SELECT MIN([s].[TotalScore] + CAST([s].[Grade] AS decimal(18,2)))
    FROM [Students] AS [s]

Max 取得最大值

var maxScore = malemaDbContext.Students.Max(it => it.TotalScore);
Console.WriteLine(maxScore); // 200

生成的sql语句如下

SELECT MAX([s].[TotalScore])
    FROM [Students] AS [s]

Average 取得平均值

var average = malemaDbContext.Students.Average(it => it.TotalScore);
Console.WriteLine(average); // 194.333333

生成的sql语句如下

     SELECT AVG([s].[TotalScore])
      FROM [Students] AS [s]

Min Max Average 没数据的时候

当数据库没有满足条件的记录时，Min Max Average 上面的写法是会抛出异常的System.InvalidOperationException:“Sequence contains no elements” 我们可以把它转成可空类型，如下。

var average2 = malemaDbContext.Students.Average(it => (decimal?)it.TotalScore);

生成的sql语句如下

  SELECT AVG([s].[TotalScore])
      FROM [Students] AS [s]

GroupBy 分组

 var group = malemaDbContext.Students.GroupBy(x => x.ClassId).Select(x => new { x.Key, count = x.Count() });
foreach (var item in group)
{
    Console.WriteLine($"key:{item.Key} count:{item.count}");
}

生成的sql语句如下

   SELECT [s].[ClassId] AS [Key], COUNT(*) AS [count]
      FROM [Students] AS [s]
      GROUP BY [s].[ClassId]

输出：

key:1 count:2
key:2 count:4

OrderBy OrderByDescending ThenBy ThenByDescending 排序

前面我们在Last 和 LastOrDefault的时候就知道我们必须用这个来进行排序。 下面的代码还展示了降序排序。还有排序完后再次跟据某个条件进行排序，这个很多时候是很有用的。

var orderByList = malemaDbContext.Students.OrderByDescending(x => x.ClassId).ThenByDescending(x => x.Id).ToList();
foreach (var item in orderByList)
{
    Console.WriteLine($"classId:{item.ClassId} " + item);
}

生成的sql语句如下

     SELECT [s].[Id], [s].[BirthDate], [s].[ClassId], [s].[Grade], [s].[Name], [s].[TotalScore]
      FROM [Students] AS [s]
      ORDER BY [s].[ClassId] DESC, [s].[Id] DESC

输出

classId:2 Id:6,Name:江小牙,TotalScore:180.00
classId:2 Id:5,Name:狗蛋,TotalScore:195.00
classId:2 Id:4,Name:赵七,TotalScore:196.00
classId:2 Id:3,Name:王五,TotalScore:197.00
classId:1 Id:2,Name:李四,TotalScore:198.00
classId:1 Id:1,Name:张三,TotalScore:200.00

如果上面的 ThenByDescending 被换成了 OrderByDescending 哪排序就只会 最后一个的 Id进行降序排序了

上面的完整代码可以在分支querying/basic看到

git clone https://gitee.com/malema/Examples
git checkout querying/basic

Select 更少的返回更快的速度

很多时候我们不需要返回所有的字段，所以我们需要用select来选择返回的字段。

public class StudentScoreDto
{
    public int Id { get; set; }

    public decimal Score { get; set; }
}

var malemaDbContext = sp.GetService<MalemaDbContext>();
var list = malemaDbContext.Students.Select(x => new StudentScoreDto()
{
    Id = x.Id,
    Score = x.TotalScore
});

上面的代码中我们先创建了一个 StudentScoreDto 类。 生成的sql语句如下, 我们会发现它确实只取了两个字段。

    SELECT [s].[Id], [s].[TotalScore] AS [Score]
      FROM [Students] AS [s]

有时候我们为了方便我们并不相定义dto对象。 可以写成如下的形式. 返回的是一个匿名对象，Unit test会变得不太好写，sql跟上面是一样的

var list2 = malemaDbContext.Students.Select(x => new
        {
            Id = x.Id,
            Score = x.TotalScore
        }).ToList();

上面的完整代码可以在分支querying/select看到

git clone https://gitee.com/malema/Examples
git checkout querying/select

代码在 ConsoleApp\Program.cs

除了上面的这些呢。 EfCore还支持 预先加载 Include ThenInclude 还Include多次。这些可以在下面的章节看到。 同样它还支持 Projection query 同样可以下面的章节看到