EF Core: how to load releated data with only having a FK of the related data-CodePudding

First of all apologies, this is a rookie question.

I have an existing EF Core 6 model I can't change, unfortunately. Part of this model are the following Wiring and Location classes.

// cannot change this entity
public class Wiring
{
    public int Id {get; set;}
    public string Name {get; set;}
    public int? LocationAId {get; set;}
    public int? LocationBId {get; set;}
}

// cannot change this entity
public class Location
{
    public int Id {get; set;}
    public string Name {get; set;}
}

In my business logic layer, I have a DTO which contains the names of the wiring and the two locations it connects. While a wiring always hase a name, the name of the locations may be null or empty.

public class WiringDto
{
    public string Name {get; set;}
    public string? LocationAId {get; set;}
    public string? LocationBId {get; set;}
}

What is the proper way of loading the releated location data when loading wirings?

I have a working solution, which gives me the following warning:

Captured variable is disposed in the outer scope

My actual code loading the wirings with related data which produces this warning:

var wiringDtos = await context.Wirings
.AsNoTracking()
.Select(w => new WiringDto
{
    w.Name,
    context.Locations.FirstOrDefault(l => l.Id == w.LocationAId) != null ? context.Locations.FirstOrDefault(l => l.Id == w.LocationAId).Name : null,
    context.Locations.FirstOrDefault(l => l.Id == w.LocationBId) != null ? context.Locations.FirstOrDefault(l => l.Id == w.LocationBId).Name : null,
})
.ToListAsync();

CodePudding user response：

You can manually join entities like this :


var result = await (from w in context.Wirings
join la in context.Locations on w.LocationAId equals la.Id into lax
from la in lax.DefaultIfEmpty()
join lb in context.Locations on w.LocationBId equals lb.Id into lbx
from lb in lbx.DefaultIfEmpty()
select new WiringDto {
Name = w.Name,
LocationAId = la.Name,
LocationBId = lb.Name 
}).ToArrayAsync();

You can also do this with Extension methods but I preferred this syntax for readability reasons. DefaultIfEmpty() usage is to make joins left joins.

CodePudding user response：

Use the following query:

var wiringDtos = await context.Wirings
    .Select(w => new WiringDto
    {
        Name = w.Name,
        LocationAId = context.Locations.Where(l => l.Id == w.LocationAId).Select(l => l.Name).FirstOrDefault(),
        LocationBId = context.Locations.Where(l => l.Id == w.LocationBId).Select(l => l.Name).FirstOrDefault(),
    })
    .ToListAsync();

Note that AsNoTracking() is not needed if you have Select