Introduction To LINQ With Entity Framework In Visual Studio

Introduction to LINQ Query

LINQ query is similar to the SQL query created for developers to maintain a standardized coding approach. It deals with the data objects, therefore, it is similar to OOPs. All LINQ queries mostly have these operations in common.

• Obtain the data source: Ensure from where the data is going to come. We need to ensure the data source implicitly/ explicitly supports IEnumerable<T> interface.
• Create the query: Generate the query based on the requirement
• Execute the query

Why use LINQ Query

The biggest advantage of LINQ query is that it doesn’t load all the objects at the compile time. Instead, it creates an execution plan and when the compiler asks to perform its operation, then only the objects are loaded into memory.

Therefore, it is the fastest and most efficient way to code.

Let’s start with the project...

First, we will be adding the Entity Framework to our project. Here, I have used database first approach for this example. I created the database first and then I switched to coding.

The below steps will demonstrate how to include Entity Framework into the Visual Studio project.

1. Right-click the project, click on Add and then add a new item.
   
   
   
   
2. Choose ADO.NET Entity Data Model and click Add.
   
   
   

3. Now, we have to use an existing database which we had created before. Therefore, choose EF Designer from the database. Then, click "Next".
   
   
   
   
4. Now, choose the database and SQL Server and get the connection string. Then, click the "Finish" button. This step might take a long time to execute because the framework will generate essential classes and their relationship between them.
   
   
   

Following is the database structure that I have used.

Database explained

We assumed we have database for online learning. Relationships are clearly described in the above diagram. Following are the entities involved in the proposed database.

• Course: describes the set of courses
• Person : which enrolls for any course
• Author: creates course as many as they want
• Content: each course have one or many contents in it
• Course_comment: comment posted by user on particular course
• Content_comment: comment posted by user on particular content
• Content_rating: rating user gives to a particular content

Basic Select Query

As we know, we can get the list of our entity very easily with this approach. Now, let’s talk about the coding part where we used various basic operations such as select, join, and include in LINQ query. 

public void FillGridDetails()
      {
          //Select Query demo
          onlineLearningEntities CourseDB = new onlineLearningEntities();
          CourseList = CourseDB.courses.ToList();
          var q = (from p in CourseList.AsEnumerable()
                   select new {
                       p.name, p.created_date,p.authors.Count
                   }).ToList();
          grdDetails.ItemsSource = q;

      }

Output

Basic Join Query

Above, we just executed the basic Select query. We converted the courses entity into the list, upon which, we implemented the Select query. We had stored the resultant into variable “q”.

public void BasicJoinQuery()
       {
           //Basic Join Query
           onlineLearningEntities CourseDB = new onlineLearningEntities();
           CourseList = CourseDB.courses.ToList();
           ContentList = CourseDB.contents.ToList();
           var q = (from p in CourseList
                    join c in ContentList on p.id equals c.courseid
                    orderby p.id
                    select new
                    {
                        coursename = p.name,
                        p.created_date,
                        content_name = c.name,
                        c.introduction,
                        c.summary,
                        c.description
                    }).ToList();
           grdDetails.ItemsSource = q;
       }

Output

Basic Includes Query

Above, we just executed the basic Join query between course and its content. We converted courses entity into list upon which we implemented Join query with content. We had stored the resultant into variable “q”.

public void CourseMainPageQuery()
      {
          onlineLearningEntities CourseDB;
          using (CourseDB = new onlineLearningEntities())
          {
              CourseList = CourseDB.courses.Include("authors").Include("people").Include("course_comment").ToList();
              var q = (from p in CourseList select new { p.name,enrolled= string.Join(",",p.people.Select(x=> x.first_name)), comments = string.Join("\n", p.course_comment.Select(x=> x.comment)),
                  authors =string.Join(",", p.authors.Select(x => x.name))}).ToList();
              grdDetails.ItemsSource = q;
          }

      }

We have used includes with the course list. "Includes" comes into play when we have to query on the ICollection property. ICollection is created by Entity Framework to establish relations, such as one-to-one, many-to-many , one-to-many vice versa. In the above snippet, we described many to many relationship among the course and author.

Output