Linq Query Agains Previous Linq Results
Linq-to-Entities Query
Here, you will larn how to write LINQ-to-Entities queries and get the effect in Entity Framework six.ten as well as in Entity Framework Cadre. Visit LINQ Tutorials to learn LINQ step by footstep.
The DbSet course is derived from IQuerayable. So, nosotros can use LINQ for querying against DbSet, which will be converted to an SQL query. EF API executes this SQL query to the underlying database, gets the flat result prepare, converts it into appropriate entity objects and returns it as a query result.
The following are some of the standard query operators (or extension methods) that can be used with LINQ-to-Entities queries.
| LINQ Extension Methods |
|---|
| First() |
| FirstOrDefault() |
| Single() |
| SingleOrDefault() |
| ToList() |
| Count() |
| Min() |
| Max() |
| Last() |
| LastOrDefault() |
| Average() |
Find()
In addition to LINQ extension methods, we can employ the Find() method of DbSet to search the entity based on the primary primal value.
Let's assume that SchoolDbEntities is our DbContext form and Students is the DbSet property.
var ctx = new SchoolDBEntities(); var student = ctx.Students.Find(1);
In the higher up example, ctx.Student.Find(i) returns a student record whose StudentId is ane in the database. If no record is found, then it returns null. The above query will execute the following SQL query.
SELECT [Extent1].[StudentID] AS [StudentID], [Extent1].[StudentName] As [StudentName], [Extent1].[StandardId] Every bit [StandardId] FROM [dbo].[Student] Equally [Extent1] WHERE [Extent1].[StudentId] = @p0',Due north'@p0 int',@p0=1 go
First/FirstOrDefault
If you want to become a single student object, when there are many students, whose name is "Nib" in the database, then use Kickoff or FirstOrDefault, as shown below:
LINQ Query Syntax:
using (var ctx = new SchoolDBEntities()) { var student = (from s in ctx.Students where s.StudentName == "Neb" select south).FirstOrDefault<Pupil>(); }
LINQ Method Syntax:
using (var ctx = new SchoolDBEntities()) { var student = ctx.Students .Where(due south => s.StudentName == "Nib") .FirstOrDefault<Student>(); }
EF vi executes the following SQL query in the database for the to a higher place LINQ query.
SELECT TOP (one) [Extent1].[StudentID] Equally [StudentID], [Extent1].[StudentName] AS [StudentName], [Extent1].[StandardId] AS [StandardId] FROM [dbo].[Educatee] AS [Extent1] WHERE 'Bill' = [Extent1].[StudentName]
EF Core executes the following query in the database.
SELECT TOP (ane) [s].[StudentId], [s].[DoB], [s].[FirstName], [s].[GradeId], [s].[LastName], [s].[MiddleName] FROM [Students] Equally [s] WHERE [due south].[FirstName] = N'Bill'
Parameterized Query
EF builds and executes a parameterized query in the database if the LINQ-to-Entities query uses parameters, such equally below.
using (var ctx = new SchoolDBEntities()) { string name = "Bill"; var educatee = ctx.Students .Where(due south => s.StudentName == name) .FirstOrDefault<Student>(); }
The in a higher place query volition result into the following SQL query in EF 6.
SELECT Meridian (1) [Extent1].[StudentId] As [StudentId], [Extent1].[Name] AS [Name] FROM [dbo].[Student] Every bit [Extent1] WHERE ([Extent1].[Name] = @p__linq__0) OR (([Extent1].[Proper name] IS NULL) AND (@p__linq__0 IS NULL))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=Due north'Nib'
The difference between First and FirstOrDefault is that Showtime() will throw an exception if in that location is no result data for the supplied criteria, whereas FirstOrDefault() returns a default value (zero) if there is no outcome information.
ToList
The ToList method returns the collection result. If you want to list all the students with the same name and then use ToList():
using (var ctx = new SchoolDBEntities()) { var studentList = ctx.Students.Where(s => southward.StudentName == "Bill").ToList(); }
Nosotros may also use ToArray, ToDictionary or ToLookup. The above query would result in the following database query:
SELECT [Extent1].[StudentID] AS [StudentID], [Extent1].[StudentName] Every bit [StudentName], [Extent1].[StandardId] Equally [StandardId] FROM [dbo].[Educatee] Equally [Extent1] WHERE 'Bill' = [Extent1].[StudentName] go
GroupBy
Use the group by operator or GroupBy extension method to become the result based on the grouping by the particular property of an entity.
The post-obit case gets the results grouped by each Standard. Use the foreach loop to iterate the grouping.
LINQ Query Syntax:
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) { Panel.WriteLine(stud.StudentId); } } }
LINQ Method Syntax:
using (var ctx = new SchoolDBEntities()) { var students = ctx.Students.GroupBy(s => s.StandardId); foreach (var groupItem in students) { Panel.WriteLine(groupItem.Key); foreach (var stud in groupItem) { Console.WriteLine(stud.StudentId); } } }
The above query volition execute the following database query:
SELECT [Project2].[C1] AS [C1], [Project2].[StandardId] AS [StandardId], [Project2].[C2] As [C2], [Project2].[StudentID] Equally [StudentID], [Project2].[StudentName] Every bit [StudentName], [Project2].[StandardId1] As [StandardId1] FROM ( SELECT [Distinct1].[StandardId] AS [StandardId], 1 AS [C1], [Extent2].[StudentID] AS [StudentID], [Extent2].[StudentName] AS [StudentName], [Extent2].[StandardId] Equally [StandardId1], CASE WHEN ([Extent2].[StudentID] IS Nothing) And so Cast(NULL Every bit int) ELSE 1 Finish AS [C2] FROM (SELECT Distinct [Extent1].[StandardId] Every bit [StandardId] FROM [dbo].[Student] AS [Extent1] ) AS [Distinct1] LEFT OUTER Bring together [dbo].[Pupil] Equally [Extent2] ON ([Distinct1].[StandardId] = [Extent2].[StandardId]) OR (([Distinct1].[StandardId] IS Nix) AND ([Extent2].[StandardId] IS NULL)) ) Equally [Project2] Order Past [Project2].[StandardId] ASC, [Project2].[C2] ASC get
OrderBy
Use the OrderBy operator with ascending/descending keywords in LINQ query syntax to get the sorted entity listing.
using (var ctx = new SchoolDBEntities()) { var students = from southward in ctx.Students orderby s.StudentName ascending select s; }
Use the OrderBy or OrderByDescending method to become the sorted entity list.
using (var ctx = new SchoolDBEntities()) { var students = ctx.Students.OrderBy(southward => southward.StudentName).ToList(); // or descending order var descStudents = ctx.Students.OrderByDescending(s => s.StudentName).ToList(); }
The higher up query volition execute the post-obit database query:
SELECT [Extent1].[StudentID] AS [StudentID], [Extent1].[StudentName] AS [StudentName], [Extent1].[StandardId] Equally [StandardId] FROM [dbo].[Student] As [Extent1] ORDER BY [Extent1].[StudentName] ASC go
Anonymous Object Result
LINQ-to-Entities queries practise not ever have to render entity objects. Nosotros may choose some of the properties of an entity as a result.
The following query returns a listing of anonymous objects which contains StudentId and StudentName properties.
LINQ Query Syntax:
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.Proper noun); } }
LINQ Method Syntax:
using (var ctx = new SchoolDBEntities()) { var anonymousObjResult = ctx.Students .Where(st => st.Standard == one) .Select(st => new { Id = st.StudentId, Proper name = st.StudentName }); foreach (var obj in anonymousObjResult) { Console.Write(obj.Proper name); } }
The above query will execute the following database query:
SELECT [s].[StudentID] AS [Id], [s].[StudentName] AS [Proper noun] FROM [Student] Equally [south] WHERE [s].[StandardId] = 1 go
The projectionResult in the above query will be the bearding type, because there is no class/entity which has these backdrop. So, the compiler will marker information technology as bearding.
Nested queries
You can also execute nested LINQ-to-entity queries as shown below:
The nested query shown above will issue in an anonymous list with a StudentName and Course object.
SELECT [Extent1].[StudentID] As [StudentID], [Extent1].[StudentName] Every bit [StudentName], [Join1].[CourseId1] AS [CourseId], [Join1].[CourseName] As [CourseName], [Join1].[Location] Every bit [Location], [Join1].[TeacherId] AS [TeacherId] FROM [dbo].[Educatee] Every bit [Extent1] INNER JOIN (SELECT [Extent2].[StudentId] As [StudentId], [Extent3].[CourseId] Every bit [CourseId1], [Extent3].[CourseName] As [CourseName], [Extent3].[Location] AS [Location], [Extent3].[TeacherId] Equally [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
In this way, you can practise a projection of the result, as yous want the information to be.
Source: https://www.entityframeworktutorial.net/querying-entity-graph-in-entity-framework.aspx
0 Response to "Linq Query Agains Previous Linq Results"
Post a Comment