Alternative for Linq 2 Sql AssociateWith method

Question

Alternative for Linq 2 Sql AssociateWith method

c# entity-framework-core linq-to-sql

Question

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)

Jan Capuder

View more on Stack Overflow

Camilo Terevinto · Accepted Answer · 7/1/2018 5:20:50 PM

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 Devices 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();
}

Alternative for Linq 2 Sql AssociateWith method

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related