Join two entities in .NET Core, using lambda and Entity Framework Core

code projected over woman

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();
Author: Jacqui Muller

Feel free to reach out or browse through:

LinkedIn
GitHub
The JPanda – Automation et al. blogs

Leave a Reply

Up ↑

%d bloggers like this: