Entity Framework Core is a great library to use which assists with mapping data and data operations to a data source within a code base. Each table in a database is represented as an entity in the code base which Entity Framework communicates with, through a data context.
There are many use cases for Entity Framework, one of which includes joining entities to get very specific data, the same way you would in SQL.
Joining Entities: Retrieve all data
This is what a normal join in SQL would look like:
SELECT *
FROM FirstEntity AS fe
JOIN SecondEntity AS se
ON fe.PrimaryKeyField = se.ForeignKeyField
The equivalent in .NET Core would be:
var results = await _context.FirstEntity.Join(
_context.SecondEntity,
firstentity => firstentity.PrimaryKeyField,
configAccess => secondentity.ForeignKeyField,
(firstentity, secondentity) => new
{
FirstEntity= firstentity,
SecondEntity = secondentity
})
.ToListAsync();
Joining Entities: Retrieve data from one entity
Now let’s only select all information from the FirstEntity in SQL:
SELECT fe.*
FROM FirstEntity AS fe
JOIN SecondEntity AS se
ON fe.PrimaryKeyField = se.ForeignKeyField
The equivalent in C# would be:
var results = await _context.FirstEntity.Join(
_context.SecondEntity,
firstentity => firstentity.PrimaryKeyField,
configAccess => secondentity.ForeignKeyField,
(firstentity, secondentity) => new
{
FirstEntity= firstentity,
SecondEntity = secondentity
})
.Select(entity => entity.FirstEntity);
.ToListAsync();
Joining Entities: Retrieve data from one entity, with a condition
If we had to add a condition to the query in SQL, it would look like this:
SELECT fe.*
FROM FirstEntity AS fe
JOIN SecondEntity AS se
ON fe.PrimaryKeyField = se.ForeignKeyField
WHERE se.ForeignKeyField IS NOT NULL
The equivalent in C# looks as follows:
var results = await _context.FirstEntity.Join(
_context.SecondEntity,
firstentity => firstentity.PrimaryKeyField,
configAccess => secondentity.ForeignKeyField,
(firstentity, secondentity) => new
{
FirstEntity= firstentity,
SecondEntity = secondentity
})
.Where(entity => entity.SecondEntity.ForeignKeyField != null)
.Select(entity => entity.FirstEntity);
.ToListAsync();
Leave a Reply