EntityFramework 学习 一 Entity Framework 查询设计

First/FirstOrDefault:

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

对应的sql语句如下

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

如果没有数据First()将抛出异常，而FirstOrDefault()则返回数据类型的默认值，如引用类型则返回null

Single/SingleOrDefault:

using (var ctx = new SchoolDBEntities())
{    
    var student = (from s in context.Students
                    where s.StudentID == 1
                    select s).SingleOrDefault<Student>();
}

sql语句如下

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

如果返回的结果包含一个以上的元素时，两者都会抛出异常，使用Single可以确定返回结果是否只有一个元素

ToList:

using (var ctx = new SchoolDBEntities())
{    
    var studentList = (from s in ctx.Students
    where s.StudentName == "Student1"
    select s).ToList<Student>();
}
    

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

GroupBy:

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

sql语句如下

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:

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

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

Anonymous Class result:

using (var ctx = new SchoolDBEntities())
{    
    var projectionResult = from s in ctx.Students
                        where s.StudentName == "Student1"
                        select new { 
                        s.StudentName, s.Standard.StandardName, s.Courses 
                        };
}

SELECT 
[Extent1].[StudentID] AS [StudentID], 
[Extent1].[StudentName] AS [StudentName], 
[Extent2].[City] AS [City]
FROM  [dbo].[Student] AS [Extent1]
LEFT OUTER JOIN [dbo].[StudentAddress] AS [Extent2] ON [Extent1].[StudentID] = [Extent2].[StudentID]
WHERE 1 = [Extent1].[StandardId]
go

Nested queries:嵌套查询

sql语句

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