站点图标 江湖人士

Entity Framework Core查询入门示例

江小编
Entity Framework Core查询入门示例

Entity Framework Core中的查询与EF 6.x中的相同，具有更优化的SQL查询以及将C＃/ VB.NET函数包含在LINQ到实体查询中的能力。 本篇文章将介绍Entity Framework Core中引入的查询的新功能。

C#和VB.NET 查询功能

EF Core在LINQ-to-Entities中有一个新功能，我们可以在查询中包含C＃或VB.NET函数。这在EF 6是不被支持的。 

private static void Main(string[] args)
{
    var context = new SchoolContext();
    var studentsWithSameName = context.Students
                                      .Where(s => s.FirstName == GetName())
                                      .ToList();
}

public static string GetName() {
    return "Bill";
}

在上面的Linq to Entities 查询中，在Where子句中包含了C＃函数【 GetName() 】。这将在数​​据库中执行以下查询： 

exec sp_executesql N'SELECT [s].[StudentId], [s].[DoB], [s].[FirstName], 
    [s].[GradeId], [s].[LastName], [s].[MiddleName]
FROM [Students] AS [s]
WHERE [s].[FirstName] = @__GetName_0',N'@__GetName_0 nvarchar(4000)',
    @__GetName_0=N'Bill'
Go

EF Core懒加载（延迟加载）

EF Core使用Include()扩展方法和投影查询支持与EF 6相同的相关实体的加载加载。除此之外，它还提供了ThenInclude()加载多级相关实体的扩展方法。（EF 6不支持该ThenInclude()方法。）

Include

与EF 6不同，我们可以将lambda表达式指定为Include()方法中的参数，以指定导航属性，如下所示。 

var context = new SchoolContext();

var studentWithGrade = context.Students
                           .Where(s => s.FirstName == "Bill")
                           .Include(s => s.Grade)
                           .FirstOrDefault();

在上面的示例中，.Include(s => s.Grade)传递lambda表达式s => s.Grade以指定要Student在单个SQL查询中从数据库加载实体数据的引用属性。以上查询在数据库中执行以下SQL查询。 

SELECT TOP(1) [s].[StudentId], [s].[DoB], [s].[FirstName], [s].[GradeId],[s].[LastName], 
        [s].[MiddleName], [s.Grade].[GradeId], [s.Grade].[GradeName], [s.Grade].[Section]
FROM [Students] AS [s]
LEFT JOIN [Grades] AS [s.Grade] ON [s].[GradeId] = [s.Grade].[GradeId]
WHERE [s].[FirstName] = N'Bill'

我们还可以在Include()方法中将属性名称指定为字符串，与EF 6中的相同。 

var context = new SchoolContext();

var studentWithGrade = context.Students
                        .Where(s => s.FirstName == "Bill")
                        .Include("Grade")
                        .FirstOrDefault();

建议不要使用上面的示例，因为如果属性名称拼写错误或不存在，它将抛出运行时异常。始终将该Include()方法与lambda表达式一起使用，以便在编译期间检测错误。

的Include()扩展方法，也可以在之后使用FromSql()如下所示的方法。

var context = new SchoolContext();

var studentWithGrade = context.Students
                        .FromSql("Select * from Students where FirstName ='Bill'")
                        .Include(s => s.Grade)
                        .FirstOrDefault();

注：该Include()扩展方法不能在之后使用DbSet.Find()的方法。例如，context.Students.Find(1).Include()在EF Core 2.0中无法实现。这在将来的版本中可能是可能的。

Multiple Include

Include()多次 使用该方法可以加载同一实体的多个导航属性。例如，以下代码加载Grade和StudentCourses相关实体Student。 

var context = new SchoolContext();

var studentWithGrade = context.Students.Where(s => s.FirstName == "Bill")
                        .Include(s => s.Grade)
                        .Include(s => s.StudentCourses)
                        .FirstOrDefault();

上述查询将在单个数据库往返中执行两个SQL查询。 

SELECT TOP(1) [s].[StudentId], [s].[DoB], [s].[FirstName], [s].[GradeId], [s].[LastName], 
        [s].[MiddleName], [s.Grade].[GradeId], [s.Grade].[GradeName], [s.Grade].[Section]
FROM [Students] AS [s]
LEFT JOIN [Grades] AS [s.Grade] ON [s].[GradeId] = [s.Grade].[GradeId]
WHERE [s].[FirstName] = N'Bill'
ORDER BY [s].[StudentId]
Go

SELECT [s.StudentCourses].[StudentId], [s.StudentCourses].[CourseId]
FROM [StudentCourses] AS [s.StudentCourses]
INNER JOIN (
    SELECT DISTINCT [t].*
    FROM (
        SELECT TOP(1) [s0].[StudentId]
        FROM [Students] AS [s0]
        LEFT JOIN [Grades] AS [s.Grade0] ON [s0].[GradeId] = [s.Grade0].[GradeId]
        WHERE [s0].[FirstName] = N'Bill'
        ORDER BY [s0].[StudentId]
    ) AS [t]
) AS [t0] ON [s.StudentCourses].[StudentId] = [t0].[StudentId]
ORDER BY [t0].[StudentId]
Go

ThenInclude

EF Core引入了新的ThenInclude()扩展方法来加载多个级别的相关实体。请考虑以下示例： 

var context = new SchoolContext();

var student = context.Students.Where(s => s.FirstName == "Bill")
                        .Include(s => s.Grade)
                            .ThenInclude(g => g.Teachers)
                        .FirstOrDefault();

在上面的示例中，.Include(s => s.Grade)将加载实体的Grade引用导航属性Student。 .ThenInclude(g => g.Teachers)将加载实体的Teacher集合属性Grade。该ThenInclude方法必须在后调用Include方法。以上将在数据库中执行以下SQL查询。 

SELECT TOP(1) [s].[StudentId], [s].[DoB], [s].[FirstName], [s].[GradeId], [s].[LastName],
         [s].[MiddleName], [s.Grade].[GradeId], [s.Grade].[GradeName], [s.Grade].[Section]
FROM [Students] AS [s]
LEFT JOIN [Grades] AS [s.Grade] ON [s].[GradeId] = [s.Grade].[GradeId]
WHERE [s].[FirstName] = N'Bill'
ORDER BY [s.Grade].[GradeId]
Go

SELECT [s.Grade.Teachers].[TeacherId], [s.Grade.Teachers].[GradeId], [s.Grade.Teachers].[Name]
FROM [Teachers] AS [s.Grade.Teachers]
INNER JOIN (
    SELECT DISTINCT [t].*
    FROM (
        SELECT TOP(1) [s.Grade0].[GradeId]
        FROM [Students] AS [s0]
        LEFT JOIN [Grades] AS [s.Grade0] ON [s0].[GradeId] = [s.Grade0].[GradeId]
        WHERE [s0].[FirstName] = N'Bill'
        ORDER BY [s.Grade0].[GradeId]
    ) AS [t]
) AS [t0] ON [s.Grade.Teachers].[GradeId] = [t0].[GradeId]
ORDER BY [t0].[GradeId]
go

Projection Query

我们也可以通过使用投影查询，而不是加载多个相关实体Include()或ThenInclude()方法。下面的例子说明了投影查询来加载Student，Grade和Teacher实体。

var context = new SchoolContext();

var stud = context.Students.Where(s => s.FirstName == "Bill")
                        .Select(s => new
                        {
                            Student = s,
                            Grade = s.Grade,
                            GradeTeachers = s.Grade.Teachers
                        })
                        .FirstOrDefault();

在上面的例子中，.Select扩展方法用于包括的Student，Grade并且Teacher实体在结果中。这将执行与上述ThenInclude()方法相同的SQL查询。

延迟加载

Entity Framework Core 2.0已经支持延迟加载。

退出移动版