EF 6 DB-First系列--Entity Framework中的查询--Linq-to-Entities查询

最新推荐文章于 2024-02-14 12:00:00 发布

一曲春风水长流

最新推荐文章于 2024-02-14 12:00:00 发布

阅读量157

点赞数

分类专栏： Entity Framework 文章标签：数据库 linq c# sql .net microsoft

Entity Framework 专栏收录该内容

49 篇文章 0 订阅

订阅专栏

Linq-to-Entities查询

Entity Framework支持三种类型的查询:1) LINQ-to-Entities, 2) Entity SQL, and 3) Native SQL

在这里，您将学习如何编写linq到实体的查询，并在Entity Framework 6.x 中获得结果，同样也适用于 Entity Framework Core。

DbSet类派生自IQuerayable。因此，我们可以使用LINQ对DbSet进行查询，这将会被转换为SQL查询。EF API对底层数据库执行此SQL查询，以获得结果集，然后将其转换为适当的实体对象并将其作为查询结果返回。

下面是一些可用于linq到实体查询的标准查询操作符(或扩展方法)。

LINQ扩展方法：
First()
FirstOrDefault()
Single()
SingleOrDefault()
ToList()
Count()
Min()
Max()
Last()
LastOrDefault()
Average()

Find()

除了LINQ扩展方法，我们还可以使用DbSet的Find()方法根据主键值搜索实体。

我们假设SchoolDbEntities是我们的DbContext类，Students是DbSet属性。

var ctx = new SchoolDBEntities();

var student = ctx.Students.Find(1);

在上面的例子中，ctx.Student.Find(1)在数据库中返回一个studententid为1的学生记录。如果没有找到记录，则返回null。上面的查询将执行以下SQL查询。

SELECT 
[Extent1].[StudentID] AS [StudentID], 
[Extent1].[StudentName] AS [StudentName], 
[Extent1].[StandardId] AS [StandardId]FROM [dbo].[Student] AS [Extent1]WHERE [Extent1].[StudentId] = @p0',N'@p0 int',@p0=1go

First/FirstOrDefault

如果你想获得一个学生对象，当数据库中有很多学生，他们的名字是“Bill”，然后使用First或FirstOrDefault，如下所示:

LINQ查询语法:

using (var ctx = new SchoolDBEntities())
{    
    var student = (from s in ctx.Students
                where s.StudentName == "Bill"
                select s).FirstOrDefault<Student>();
}

LINQ方法语法:

using (var ctx = new SchoolDBEntities())
{    
    var student = ctx.Students.Where(s => s.StudentName == "Bill")
                              .FirstOrDefault<Student>();
}

EF 6在数据库中为上面的LINQ查询执行以下SQL查询。

SELECT TOP (1) 
[Extent1].[StudentID] AS [StudentID], 
[Extent1].[StudentName] AS [StudentName], 
[Extent1].[StandardId] AS [StandardId]
FROM [dbo].[Student] AS [Extent1]
WHERE 'Bill' = [Extent1].[StudentName]

EF Core在数据库中执行以下查询。

SELECT TOP (1) 
[s].[StudentId], [s].[DoB], [s].[FirstName], [s].[GradeId], 
[s].[LastName], [s].[MiddleName]
FROM [Students] AS [s]
WHERE [s].[FirstName] = N'Bill'

参数化查询

如果LINQ-to-Entities查询使用参数，EF将在数据库中构建并执行参数化查询，如下所示。

using (var ctx = new SchoolDBEntities())
{    
    string name = "Bill";
    var student = ctx.Students
                  .Where(s => s.StudentName == name)
                  .FirstOrDefault<Student>();
}

在EF 6中，上述查询将导致以下SQL查询。

SELECT TOP (1) 
[Extent1].[StudentId] AS [StudentId], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Student] AS [Extent1]
WHERE ([Extent1].[Name] = @p__linq__0) OR (([Extent1].[Name] IS NULL) 
         AND (@p__linq__0 IS NULL))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'Bill'

First和FirstOrDefault之间的区别是，如果提供的条件没有结果数据，First()将抛出异常，而FirstOrDefault()如果没有结果数据，则返回默认值(null)。

ToList

ToList方法返回收集结果。如果你想列出所有同名的学生，那么使用ToList():

using (var ctx = new SchoolDBEntities())
{    
    var studentList = ctx.Students.Where(s => s.StudentName == "Bill").ToList();
}

我们也可以使用ToArray, ToDictionary或ToLookup。上述查询将导致以下数据库查询:

SELECT 
[Extent1].[StudentID] AS [StudentID], 
[Extent1].[StudentName] AS [StudentName], 
[Extent1].[StandardId] AS [StandardId]
FROM [dbo].[Student] AS [Extent1]
WHERE 'Bill' = [Extent1].[StudentName]
go

GroupBy

使用group by操作符或GroupBy扩展方法可根据实体的特定属性获得基于group by的结果。

下面的示例根据每个Standard获得分组的结果。使用foreach循环迭代组。

LINQ查询语法:

using (var ctx = new SchoolDBEntities())
{    
    var students = from s in ctx.Students 
                    group s by s.StandardId into studentsByStandard
                    select studentsByStandard;

    foreach (var groupItem in students)
    {
        Console.WriteLine(groupItem.Key);

        foreach (var stud in groupItem)
        {
            Console.WriteLine(stud.StudentId);
        }
    }
}

LINQ方法语法:

using (var ctx = new SchoolDBEntities())
{    
    var students = ctx.Students.GroupBy(s => s.StandardId);

    foreach (var groupItem in students)
    {
        Console.WriteLine(groupItem.Key);

        foreach (var stud in groupItem)
        {
            Console.WriteLine(stud.StudentId);
        }
    }
}

上面的查询将执行以下数据库查询:

SELECT 
[Project2].[C1] AS [C1], 
[Project2].[StandardId] AS [StandardId], 
[Project2].[C2] AS [C2], 
[Project2].[StudentID] AS [StudentID], 
[Project2].[StudentName] AS [StudentName], 
[Project2].[StandardId1] AS [StandardId1]
FROM ( SELECT 
    [Distinct1].[StandardId] AS [StandardId], 
    1 AS [C1], 
    [Extent2].[StudentID] AS [StudentID], 
    [Extent2].[StudentName] AS [StudentName], 
    [Extent2].[StandardId] AS [StandardId1], 
    CASE WHEN ([Extent2].[StudentID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM   (SELECT DISTINCT 
        [Extent1].[StandardId] AS [StandardId]
        FROM [dbo].[Student] AS [Extent1] ) AS [Distinct1]
    LEFT OUTER JOIN [dbo].[Student] AS [Extent2] ON ([Distinct1].[StandardId] = [Extent2].[StandardId]) OR (([Distinct1].[StandardId] IS NULL) AND ([Extent2].[StandardId] IS NULL))
)  AS [Project2]
ORDER BY [Project2].[StandardId] ASC, [Project2].[C2] ASC
go

OrderBy

在LINQ查询语法中使用带有升序/降序关键字的OrderBy操作符来获得排序的实体列表。

using (var ctx = new SchoolDBEntities())
{    
        var students = from s in ctx.Students
                       orderby s.StudentName ascending
                       select s;
}

使用OrderBy或OrderbyDescent方法获取排序的实体列表。

using (var ctx = new SchoolDBEntities())
{    
        var students = ctx.Students.OrderBy(s => s.StudentName).ToList();
        // or descending order  
        var  descStudents = ctx.Students.OrderByDescending(s => s.StudentName).ToList();
}

上面的查询将执行以下数据库查询:

SELECT 
[Extent1].[StudentID] AS [StudentID], 
[Extent1].[StudentName] AS [StudentName], 
[Extent1].[StandardId] AS [StandardId]
FROM [dbo].[Student] AS [Extent1]
ORDER BY [Extent1].[StudentName] ASC
go

匿名对象结果

LINQ-to-Entities查询并不总是必须返回实体对象。因此，我们可以选择实体的某些属性。

下面的查询返回一个匿名对象列表，其中包含studententid和StudentName属性。

LINQ查询语法:

using (var ctx = new SchoolDBEntities())
{    
    var anonymousObjResult = from s in ctx.Students
                             where s.StandardId == 1
                             select new { 
                                Id = st.StudentId, 
                                Name = st.StudentName
                             };

    foreach (var obj in anonymousObjResult)
    {
        Console.Write(obj.Name);
    }
}

LINQ方法语法:

using (var ctx = new SchoolDBEntities())
{    
    var anonymousObjResult = ctx.Students
                                .Where(st => st.Standard == 1)
                                .Select(st => new { 
                                            Id = st.StudentId, 
                                            Name = st.StudentName });

    foreach (var obj in anonymousObjResult)
    {
        Console.Write(obj.Name);
    }
}

上面的查询将执行以下数据库查询:

SELECT 
[s].[StudentID] AS [Id], [s].[StudentName] AS [Name]
FROM [Student] AS [s]
WHERE [s].[StandardId] = 1
go

上述查询中的projectionResult将是匿名类型，因为没有具有这些属性的类/实体。因此，编译器会将其标记为匿名。

嵌套查询

你也可以执行嵌套的linq到实体的查询，如下所示:
在这里插入图片描述
上面所示的嵌套查询将产生一个具有StudentName和Course对象的匿名列表。

SELECT 
[Extent1].[StudentID] AS [StudentID], 
[Extent1].[StudentName] AS [StudentName], 
[Join1].[CourseId1] AS [CourseId], 
[Join1].[CourseName] AS [CourseName], 
[Join1].[Location] AS [Location], 
[Join1].[TeacherId] AS [TeacherId]
FROM  [dbo].[Student] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[StudentId] AS [StudentId], 
        [Extent3].[CourseId] AS [CourseId1], [Extent3].[CourseName] AS [CourseName], 
        [Extent3].[Location] AS [Location], [Extent3].[TeacherId] AS [TeacherId]
    FROM  [dbo].[StudentCourse] AS [Extent2]
    INNER JOIN [dbo].[Course] AS [Extent3] 
        ON [Extent3].[CourseId] = [Extent2].[CourseId] ) AS [Join1] 
        ON [Extent1].[StudentID] = [Join1].[StudentId]
WHERE 1 = [Extent1].[StandardId]
go

通过这种方式，您可以根据需要的数据对结果进行投影。

参考

https://www.entityframeworktutorial.net/
https://msdn.microsoft.com/

一曲春风水长流

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
EF 6 DB-First系列--Entity Framework中的查询--Linq-to-Entities查询

Entity Framework支持三种类型的查询:1) LINQ-to-Entities, 2) Entity SQL, and 3) Native SQL在这里，您将学习如何编写linq到实体的查询，并在Entity Framework 6.x 中获得结果，同样也适用于 Entity Framework Core。DbSet类派生自IQuerayable。因此，我们可以使用LINQ对DbSet进行查询，这将会被转换为SQL查询。
复制链接

扫一扫

专栏目录