Entity Framework Core LINQ Queries Overview

Entity Framework Core LINQ Queries Overview

Entity Framework Core LINQ Queries Entity Framework Core Select all rows from a Table Entity Framework Core Select one row from a Table Entity Framework Core Where clause (Filter rows based on conditions) Entity Framework Core sub-query (Filter rows based on results from another table) Entity Framework Core ORDER BY (Sorting) Entity Framework Core GROUP BY (Grouping)

Entity Framework Core GROUP BY (Grouping)

In the previous posts we saw on how to get data from a SQL Server table using EF Core context, how to filter data using various types of filters and how to Sort the data using sorting keys. In this post we shall see on how to perform Aggregate operations like COUNT, MAX, MIN etc by Grouping rows based on keys. This is similar to the GROUP BY clause in SQL Server.

Let’s start with a GROUP BY query which will get the count of employees in each of the City in the Employee table.

Entity Framework Core ORDER BY (Sorting)

In this post we shall see on how to sort the results from a table using LINQ Query, this is similar to the ORDER BY clause in SQL Server. In EF Core LINQ queries we can achieve this by using OrderBy.

Let us get the list of Employees in the Employee table sorted by the Name of the employee, this can be done using the following LINQ query.

Entity Framework Core sub-query (Filter rows based on results from another table)

In the previous post we saw on how to implement different types of row filters using LINQ queries, like Contains, StartsWith, EndsWith etc. In this post we shall see a more complex type of filter where the rows from the Employee table are filtered based on a specific type of Department Name.

We want to get the list of employees who belong to a Department whose name contains the word Account. In SQL server we can do this using a sub-query as follows.

Entity Framework Core Where clause (Filter rows based on conditions)

To filter a table for specific rows we need to use Where clause and specify a filter condition in the Where clause.

To get all employees who have Age > 30 we need to use the following LINQ Query

Entity Framework Core Select one row from a Table

To select a specific row from a table, we need to use the Select clause and pass the id of the row to be obtained. Let us say we want to get the Employee with EmployeeId = 1 from the employee table then the EF Core query will be as follows.

            var Employee = _context.Employee.Select(e => e.EmployeeId == 1);

Entity Framework Core Select all rows from a Table

Selecting all the rows from a table is very simple, assuming that we have setup the Entity Framework Core context and generated all the Models we can use the context object to select all the rows from the Employee tables as follows.

List_context.Employee.ToList();

If we want to get all the Employees in a MVC Controller then the controller will be as follows

Entity Framework Core LINQ Queries

 Asp.Net Core uses Entity Framework Core to perform database operations like SELECT, INSERT, UPDATE and DELETE. Also SELECT involves various types like selecting specific columns, selecting specific rows, ordering rows, grouping rows, etc. In the following posts we shall see on how to perform various types of LINQ queries to achieve these requirements.

To perform LINQ queries we need an underlying SQL table and some data, for these sample we shall use the following tables and data. In the following posts we will use these table to perform different types of operations list SELECT, JOIN, WHERE, INSERT, UPDATE etc

Asp.Net Core WebAPI Attribute Routing

Asp.Net Core WebAPI Attribute Routing

Attribute Routing in Asp.Net Core Web API Asp.Net Core Web API Attribute Routing with Parameters

Asp.Net Core Web API Attribute Routing with Parameters

We saw on how to define custom attribute routing for Asp.Net Core WebAPI action methods and how to access the action methods using the custom Route. In this post we shall move a step further and see on how to use custom routes to pass parameters to the action methods.

Let us create one more action method to the Department controller which accepts an id parameter in the route.

Attribute Routing in Asp.Net Core Web API

In the previous posts we saw on how to use the HTTP verbs GET, POST, PUT & DELETE to invoke different action methods in an Asp.Net Core WebAPI controller, this approach holds good as long as we have only one Action method per verb in a controller, what if we want multiple methods with the same header or if we want to have custom routing to each action method, this is where attribute routing can help us. In this post we shall see on how to define custom routes to action methods using attribute routing.

Let us another controller DepartmentController, and add a Get method with a custom routing attribute as follows.

Asp.Net Core WebAPI with Entity Framework Core Getting Started

Asp.Net Core WebAPI with EF Core Getting Started

Adding Entity Framework Core to the WebAPI Project Asp.Net Core API Controller with a GET Method Creating a POST (Insert) method in Asp.Net Core API Controller Creating a PUT (Update) method in Asp.Net Core WebAPI Controller Creating a Delete method in Asp.Net Core WebAPI Controller

Creating a Delete method in Asp.Net Core WebAPI Controller

In this post we will create a HttpDelete method in Asp.Net Core WebAPI, which will be used to delete a row in the SQL Server database using Entity Framework Core. We have seen how to setup Entity Framework Core in an Asp.Net Core Web API project in the previous posts, in this post we will jump into creating the HttpDelete method.

Open the Country controller which we used in the previous post and add the following Delete method which uses the HttpDelete verb.

Creating a PUT (Update) method in Asp.Net Core WebAPI Controller

In this post we will create a HttpPut method in Asp.Net Core WebAPI, which will be used to update a row in the SQL Server database using Entity Framework Core. We have seen how to setup Entity Framework Core in an Asp.Net Core Web API project in the previous posts, in this post we will jump into creating the HttpPut method.

Open the Country controller which we used in the previous post and add the following Update method which uses the HttpPut verb.

Creating a POST (Insert) method in Asp.Net Core API Controller

In the previous post we created an API Controller and created a GET method, in this post we will create a POST method which will use Entity Framework Core to insert values to the database. We will use the same country controller and add a Create method.

Open the CountryController and add the following Create method, the method will take an object of type Country and insert the same to the Database.

Asp.Net Core API Controller with a GET Method

In the previous post we added Entity Framework Core context to the Web API project, in this post we will use the EF context to get a list of values from the DB and expose the data in JSON format using a HttpGet method.

To start with we will have to add a reference of the DataAccess project which we created in the previous post to the WebAPI project. All database operations will be performed in the DataAccess project hence we will add a reference to this project.

Next in the WebAPI project we will add a new API Controller, name it CountryController

Adding Entity Framework Core to the WebAPI Project

In the previous post we saw on how to create a basic WebAPI project in Asp.Net Core using Visua Studio 2017. In this post we shall see on how to add Entitiy Framework Core support to the basic project. First let us create a class Library project with name DataAccess. This project will deal with all database interactions and we will implement EntityFramework Core in this project.

Once the DataAccess project is created, delete the default class1.cs which got created with the project. Add the following NuGet packages using the NuGet package Manager.

Asp.Net Core WebAPI Basics

Asp.Net Core WebAPI Basics

What is Asp.Net Core WebAPI? Creating a HelloWorld WebAPI Project in Asp.Net Core Launch Settings in ASP.NET Core WebAPI