I am rewriting ASP.NET MVC application to ASP.NET CORE and have came accross with a case using Linq 2 Sql AssociateWith method for which I am not sure how to rewrite it properly. In .NET Core I am now using Entity framework.
Linq 2 Sql code:
var option = new System.Data.Linq.DataLoadOptions();
option.LoadWith<Device>(e => e.SensorStatus);
option.LoadWith<Device>(e => e.SensorVisibilities);
option.AssociateWith<Device>(e => e.SensorStatus.OrderByDescending(c => c.SensorDataID).Take(1));
option.LoadWith<SensorStatus>(e => e.SensorDatas);
ctx.LoadOptions = option;
Current EF code, for which I am not sure if I got it right:
var devices = ctx.Device.Include(x => x.SensorStatus)
.ThenInclude(x => x.SensorData).OrderByDescending(x => x.SensorStatus.Select(y => y.SensorDataId)).Take(1)
.Include(x => x.SensorVisibility);
ps. I've searched posts on Stack but found only one similar topic, where there was just one join. (Entity Framework vs. AssociateWith)
There is no option in Entity Framework to filter what you are loading when you use .Include()
.
The calls to .OrderByDescending().Take()
in your query is affecting how the Device
s are loaded, not SensorStatus.SensorData
.
You have two options:
Load everything as you are doing, but leave aside the calls to OrderByDescending().Take()
as that's not what you want:
var devices = ctx.Device
.Include(x => x.SensorStatus)
.ThenInclude(x => x.SensorData)
.Include(x => x.SensorVisibility);
Load up to SensorStatus
and choose what to load afterwards:
var devices = ctx.Device
.Include(x => x.SensorStatus)
.Include(x => x.SensorVisibility);
foreach (var device in devices)
{
device.SensorStatus.SensorData = ctx.SensorStatus
.Where(x => x.SensorStatusId == device.SensorStatusId) // I'm assuming the name of the key here
.OrderByDescending(x => x.SensorDataId)
.FirstOrDefault();
}