When building data solutions with Entity Framework (EF), we tend to create entity models for each table that we need. For instances when we need to join multiple tables, we either create a SQL Server view and bound EF Model or join the data entities using LINQ.
In this article, we take a look at creating a single EF model which maps to a raw query with multiple joins executed on the backend using .NET Entity Framework
Defining the Database
Our challenge is to obtain task information using the ERD below. There is the Task main table and four look up tables for the different properties like status, type, severity and priority.
Defining the SQL Statement
The SQL query to get that information is just a simple multi-join statement with all the lookup tables. The resulting data is useful in defining the model that we need to map the results.
select t.taskid,[description]
,ts.name as status
,tt.name type
, tsv.name as severity
, tp.name as priority
from [bi].task t (NOLOCK)
inner join [bi].[TaskStatus] ts (NOLOCK) on t.taskstatusid= ts.taskstatusid
inner join [bi].[TaskType] tt (NOLOCK) on t.tasktypeid= tt.tasktypeid
inner join [bi].[TaskSeverity] tsv (NOLOCK) on t.taskseverityid= tsv.taskseverityid
inner join [bi].[TaskPriority] tp (NOLOCK) on t.taskpriorityid= tp.taskpriorityid
|
Defining the Model
Since we only need to one entity with all the lookup values, we can define a simple model which we can use to map the results of our query.
public class Task
{
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; }
}
|
We can move on to do the EF implementation.
Defining the Database Context
Now that we have our model, we need the DbContext with a DbSet property map to our Task model. This is what we can use to fetch the data from our database.
public class TaskContext : DbContext
{
public TaskContext(DbContextOptions<TaskContext> options)
: base(options)
{ }
public DbSet<Task> Tasks { get; set; }
}
|
Doing the Work
We are ready to test our approach using a simple console application. The main logic is shown below:
var connection = @"Server=.\sqlexpress;Database=dev;Trusted_Connection=True";
var optionsBuilder = new DbContextOptionsBuilder<TaskContext>();
optionsBuilder.UseSqlServer(connection);
using (TaskContext context = new TaskContext(optionsBuilder.Options))
{
string sql = @" select t.taskid,[description]
,ts.name as status
,tt.name type
, tsv.name as severity
, tp.name as priority
from [bi].task t (NOLOCK)
inner join [bi].[TaskStatus] ts (NOLOCK) on t.taskstatusid= ts.taskstatusid
inner join [bi].[TaskType] tt (NOLOCK) on t.tasktypeid= tt.tasktypeid
inner join [bi].[TaskSeverity] tsv (NOLOCK) on t.taskseverityid= tsv.taskseverityid
inner join [bi].[TaskPriority] tp (NOLOCK) on t.taskpriorityid= tp.taskpriorityid";
List<Task> tasks = context.Tasks.FromSql<Task>(sql).ToList();
foreach(var task in tasks)
{
Console.WriteLine($" {task.TaskId}, {task.Description},
{task.Priority}, {task.Status}, {task.Type},
{task.Severity}, {task.Priority}");
}
|
We first set the connection string using a DbContextOptionsBuilder. Next, we instantiate the TaskContext object and execute our SQL query with the statement we defined previously. The result of the query is then mapped to our entity.
Now that we have the result on a list, we can display the result on the console.
Conclusion
The ideal solution to handle these cases would be to create the views or tables that have all the data that is required to read. This however may not be an option due to some constraint, so we need to take an approach that can provide the same service on the middleware.
Check this article for a Complex Joins .NET Core 2.0 Entity Framework and SQL Server
I hope this can provide a simple way to do complicated join using .NET Entity Framework.
0 comments :
Post a Comment
What do you think?