Get Started With LINQ Queries Step By Step

INTRODUCTION

In this article, we’re going to learn Queries with LINQ. and understand how to do selecting, filtering, grouping, and joins operations. Those operators are all available in the comprehension query syntax. After that, we will understand some couple of advanced topics. like composing queries with building dynamic queries on LINQ.

OVERVIEW

Query syntax:

Let’s understand step by step LINQ query. Every query is going to have to start with some sort of sequence. that is the data source, those objects could be either local, so it could be a memory, it could be remote, it could come from a database. The LINQ query syntax starts using from the operator.

We have following example

var StudentList = form student in Dbcontext.students where student.Marks >= 80 orderby student.descending select employee;

we have a range variable. So, in this case, our range variable is called a student. It’s going to range over that sequence of student objects. With the range variable, we introduce that so that we can use it later inside of our query operators. So it’s the WHERE operator and the SELECT operator. The WHERE operator uses the range variable to check to see if the students’ marks are greater than some maximum length. And the Select operator uses that range variable to select the student object in its entirety.

Lambda expression:

here, we’re going to write some LINQ. And we have a helper method will simply dump out any students. that we get into an enumerable collection.

Example

var StudentList = Dbcontext.students.where(s => s.Marks > 80).OrderByDescending(s => s.Marks);

we already have an employee class defined, ID name and department properties. And we have an EmployeeRepository that simply fabricate some employees. So it’s just creating an in-memory list of employees with different names and IDs. And when we asked to get all, it will simply return a reference to that list for us.

Using the extension method call StudentList. In this case, we don’t explicitly introduce a range variable. we can implement the WHERE operator using a lambda expression that takes a student and return to bool.

we could have written WHERE invocations. it checks the student marks and then for the ORDER BY though, this is going to work a little bit different. We can’t combine these so, we need to specify a lambda expression that will return that key. that value that should be used to do the sorting.

Query Syntaxes And Lambda expression

So, some writing queries in the comprehension query syntax or writing queries using extension methods and lambda expressions. a previous couple of year using the extension method syntax using lambda expressions. they generally offer a little more control and flexibility. The primary reason is that not all of the LINQ operators are expose as keywords for comprehension queries.

The extension method syntax is it gives you that feeling of an object pipeline. So we know that we’re going to push students to a WHERE filter first and then we’re going to do a Select on them. And between, have had an Order By, Grouping and some other operators. And that’s a great way when you are working with objects in memory.

LINQ to SQL provider would take the holistic view of this query. and would transform it into a transact SQL command. that might be processing our record in a different fashion than what we thought we were writing the query using extension methods. But for in-memory objects this is perfect. And it is important to point out that there is no LINQ operator available. that will change your original sequence.

Deferred Execution

Let’s understand how the query is going to behave. And how the query behaves is going to depend on the operators that you choose to use. So in the first module, we understand about Lazy LINQ. LINQ being lazy and offering deferred execution. whereby we can define a query and no work has to be perform, no data has to be access until try to access the result for the first time. And that is a very important concept to understand because we’ll be able to build some powerful composable queries using that feature.

Syntax

public static class StudentExtension
{
		public static IEnumarable<Student> Where(
		this IEnumarable<Student> sequence, Predicate<Student> predicate)
		{
		   List<Student> list = new List<Student>();
		   foreach(Student student in sequence)
		   {
		     if(predicate(employee))
			   list.Add(student);
		   }
		   return list;
		}
}

The let and into Keywords

let keyword:-

Another keyword that’s available in your comprehension queries is the let keyword. What the let keyword does is introduce a new range variable to your query that you can use inside of the other operators. following example,

var query = from s in Dbcontext.students let lname = s.Name.ToLower() where lname == "john" select lname;

So, the let keyword is generally used to make your queries more readable to move some expressions. like this that you have to use a query into a single place.

into keyword:-

Another keyword that you can use with LINQ is the into keyword. The into keyword can be use to continue a query. after you’ve used the select keywords or after you’ve done a select, you can say put these results into a new range variable. The first range variable that you have will go out of scope. So the into keyword can be use when you want to try to simplify query and try to break it up into more manageable chunks. But the common use of into is actually when you start doing grouping with LINQ queries.

Example

var studlist = from student in Dbcontext.students where students.Name.StartWith("P") select student into pStudent where pStudent.Name.Length < 5 select pStudent;

Grouping

Grouping with LINQ works a little bit different, than the way grouping works in a relational database. If you’ve written the SQL commands that use a GROUP BY clause, So that what you get back from a GROUP BY or a query that has a GROUP BY is still a flat recordset.

Syntax

var StudGroup = from student in Dbcontext.student group student by student.Name;

foreach(var group in StudGroup)
{
Console.Writeline(group.Key);
foreach(var student in group)
{
 Console.Writeline("{0}:{1}", student.Name);
}
}

In LINQ, a grouping works differently. When you perform a grouping on objects. what LINQ, what this LINQ operator will actually do is create a sequence of groupings. and this sequence implements IGrouping of TKey and T where TKey represents what you grouped on. so some attribute that you grouped on like DepartmentID.So the T represents the original type of the entity.

Projection

Now, we’re going to use a projection. the select keyword and a new type that can do that. So here in this query,

var StudGroup = from student in Dbcontext.student group student by student.Name into sGroup orderby sGroup.key descending where sGroup.key < 3 select new
{
  StudentName = sGroup.key,
  Count = sGroup.count(),
  Students = sGroup
};

foreach(var group in StudGroup)
{
Console.Writeline("SName: {0}, Count{1}",
 group.Name,
 group.Count
);
foreach(var student in group.Students)
{
 Console.Writeline("{0}:{1}", student.Name);
}
}

We’re getting all the students from the table, regrouping them by the Student Name and also the first letter of their name. so that’s an anonymous type that we used to do the grouping. We’re putting those into gGroup, so that’s the variable we’re going to be working with. So, additional filter here, so we’re saying we only want groupings that have a count greater than 1 and then we’re going to perform a projection.

Nested Queries

Now, you can also perform nested queries with LINQ. and this would be similar to using nested SELECT statements in SQL Server or any relational database. but, you do have to be very careful with performance when you’re using LINQ to Objects.

If you build a LINQ query for LINQ to SQL or the entity framework. a LINQ provider is going to examine that query as a whole piece and it’s going to transform it into structured query language and send that to the database.

But when we’re using LINQ to Objects, we are executing extension methods and we have a lot of iteration going on. So if we look at this query here,

var AllStudents = from student in Dbcontext.students where student.DepartmentID == ( from department in Dbcontext.department where department.Name == "Computer" select department).First().ID select student;
var  studlist = from student in Dbcontext.students select new 
{
  Name = student.Name,
  Department = (from department in Dbcontext.students where department.ID == student.ID select student).First().Name
};

We’re getting all the students from our student repository. and then we’re going to filter the students by specific DepartmentID. So, we have a department repository also returns department, this department has names. this could still have performance implications and there’s actually a better way to do this by using a JOIN.

Joins

So the joins in LINQ work very much like the joins in our relational database. They connect two different collections of information and with LINQ. we will understand the outer sequence which would be the sequence of objects that we introduced with the FROM keyword and inner sequence. which is the second sequence.

var query = from s in Dbcontext.students join d in Dbcontext.departments on s.StudID equals d.DepartID select new { Student = s.Name,
      Department = d.Name
	  };
	  
foreach(var q in query)
{
   Console.Writeline("{0} {1}",s.Student, s.Department);
}

We introduced that with the JOIN keyword, and the way we join things together. we have to use an equals keyword so that always trips people up. Now, what LINQ will do is take the inner sequence. that you’re bringing in with the JOIN keyword then it’s going to put it into a key collection.

Group Joins

In LINQ, when you use the into the keyword, it is known as a Group Join. So you have a Group Join whenever you have an into keyword after the JOIN keyword. If you’re using the JOIN keyword you get back a flat result set. If you use the into keyword we get back groupings.

var studlist = from department in Dbcontext.departments join student in Dbcontext.students on department.ID equals student.ID into sg select new { Name = department.Name, Students = eg };

Sorting

The sorting with LINQ we know that there’s an ORDER BY keyword and there’s an ORDER BY operator to do the ordering. If you don’t specify ascending or descending after an ORDER BY and you’re using a comprehension query then the default is to order by in ascending order. So you don’t need to specify that. You only need to explicitly specify this keyword if you’re doing a descending sort. And you can have as many sort expressions here as you need, separate them by commas.

var studlist = from student in Dbcontext.students orderby student.ID ascending, student.Name descending select student;

//Using extension method

var studlist = Dbcontext.students.OrderBy(s => s.ID).ThenByDescecnding(s => s.Name);

So the primary sort here is going to be on DepartmentID, a secondary sort is by name. We can add a tertiary sort and so forth. That’s a little bit different if you’re working with the extension method syntax. Because there is an ORDER BY operator and that ORDER BY operator you have to specify the property, that’s going to be the primary sort. So, if you want a secondary sort, there are also THEN BY operators. So you have. OrderBy and. OrderByDescending and you have. ThenBy and. ThenByDescecnding. And each of those accepts a single property that allows you to do your sorting.

Composition

The composition of queries. it turns out we’ve already sort of been composing queries. We get all the students and then we take that sequence that we get back from the repository and we use it in a LINQ query. The beauty of this is that with LINQ’s deferred execution. that allows us to keep adding to that query and keep passing it through different components.

public IEnumarable<Student> GetByDepartmentID(int departmentID)
{
  return from student in Dbcontext.students where student.ID == departmentID select student;
}

Dynamic Queries

So this composition of an ORDER BY actually leads to an interesting scenario in LINQ where the static typing becomes a bit of a burden. if we don’t know how we want to order this thing by until we actually get to runtime. So it’s something the user clicks on or types in of how they want to order something.

var query = Dbcontext.students.AsQueryable().OrderBy("Name").Where("DepartmentID = 1");

we can specify that as a string. And then we need to put those two together using the getter that we defined here, what to get, and the parameter of type. At the runtime when we compile this, what it will turn into is a lambda. that will go get the property with this name on this parameter that we pass in.

So, in this article, we’ve seen that there are two different ways to express a query with LINQ. You can choose the comprehension query syntax which looks very much like structured query language or you can use the extension method lambda syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *