With this article, we take a look at building a console application using .NET Core 2.0 and Entity Framework (EF). The goal is to show how to start a project with .Net core, install the dependencies and run a small test to extract data from a SQL Server database using a complex join.
We start by creating a Console Application using the .NET Core project template. Make sure to target the .NET Core 2.0 framework.
Install Dependencies
We can install the necessary Entity Framework dependencies using the NuGet Package Manager Console. We are only targeting SQL Server as our data platform, so we need to install that dependency as well.
On the console, type the following:
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools
|
Defining the Database
Our database Entity Relation Diagram (ERD) is shown below. We want to query task information with the extended properties like status, type, severity and priority.
Defining the Models
We want to be able to join all those entities, so we need to define a model for each entity shown on the ERD: Task, TaskType, TaskStatus, TaskPriority, TaskSeverity.
public class Task
{
public int TaskId { get; set; }
public string Description { get; set; }
}
public class TaskStatus
{
public int TaskStatusId { get; set; }
public string Name { get; set; }
}
public class TaskType
{
public int TaskTypeId { get; set; }
public string Name { get; set; }
}
public class TaskPriority
{
public int TaskPriorityId { get; set; }
public string Name { get; set; }
}
public class TaskSeverity
{
public int TaskSeverityId { get; set; }
public string Name { get; set; }
}
|
Since we are joining all these tables, we want to create a model that can define the result of our join. Therefore, we add the TaskDetail model. This is basically a view model for the result we want which does not need to bind to physical table.
public class TaskDetail
{
public int TaskId { get; set; }
public string Description { get; set; }
public string Status { get; set; }
public string Severity { get; set; }
public string Priority { get; set; }
public string Type { get; set; }
}
|
Defining the Database Context
We need to get data to our models, so we need to define the DbContext with a DbSet property to map to each entity. This is what we can use to make queries from our database.
public class TaskContext : DbContext
{
public TaskContext(DbContextOptions<TaskContext> options)
: base(options)
{ }
public DbSet<Task> Tasks { get; set; }
public DbSet<TaskStatus> TaskStatus { get; set; }
public DbSet<TaskType> TaskTypes { get; set; }
public DbSet<TaskSeverity> TaskSeverity { get; set; }
public DbSet<TaskPriority> TaskPriority { get; set; }
}
|
Doing the Work
We are ready to test our approach using our console application. The logic is to execute a SQL statement with inner joins with the look up tables to get extended task information. We want to return a view model that brings all the relevant properties of a task.
var connection = @"Server=.;Database=dev;Trusted_Connection=True;";
var optionsBuilder = new DbContextOptionsBuilder<TaskContext>();
optionsBuilder.UseSqlServer(connection);
using (TaskContext context = new TaskContext(optionsBuilder.Options))
{
var results = from task in context.Tasks
join status in context.TaskStatus
on task.TaskStatusId equals status.TaskStatusId
join type in context.TaskTypes
on task.TaskTypeId equals type.TaskTypeId
join severity in context.TaskSeverity
on task.TaskSeverityId equals severity.TaskSeverityId
join priority in context.TaskPriority
on task.TaskPriorityId equals priority.TaskPriorityId
select new TaskDetail
{
TaskId = task.TaskId,
Description = task.Description,
Status = status.Name,
Type = type.Name,
Severity = severity.Name,
Priority = priority.Name
};
foreach (var task in results)
{
Console.WriteLine($" {task.TaskId}, {task.Description}, {task.Priority}, " +
$"{task.Status}, {task.Type}, {task.Severity}," +
$" {task.Priority}");
}
Console.WriteLine($"Total Records {results.Count()}");
|
The join is done using the primary keys for each entity which maps to a foreign key constraint on the task entity. We then just return the task description and names from each of the look up tables using our view model, TaskDetail.
Conclusion
We can create complex joins using EF and LINQ, and we can also define views models that can help us abstract the complexity of the database entities for our apps. There are also other strategies to get this done like creating a view on the database or executing a raw SQL statement and build the view model without defining each entity model.
Check this article for a Single Model from a Multi-Join Query .NET Core Entity Framework SQL Server
It really depends on the approach and coding standards that you need to follow. I hope this can provide a simple way to do complex join using .Net Entity Framework
