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