Entity Framework Core 2.1 problem with DetachedLazyLoadingWarning

c# code-first ef-code-first entity-framework entity-framework-core

Question

I get an exception with DetachedLazyLoadingWarning:

Error generated for warning 'Microsoft.EntityFrameworkCore.Infrastructure.DetachedLazyLoadingWarning: An attempt was made to lazy-load navigation property 'Product' on detached entity of type 'DeliveryProxy'. Lazy-loading is not supported for detached entities or entities that are loaded with 'AsNoTracking()'.'. This exception can be suppressed or logged by passing event ID 'CoreEventId.DetachedLazyLoadingWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

while trying to query SQL database with Entity Framework Code 2.1

This is my query:

var orders = 
    _context
    .Set<Order>()
    .Where(v => v.CompanyId == companyId)
    .Include(v => v.Details)
    .ThenInclude(d => d.Delivery)
    .ThenInclude(v => v.Product)
    .OrderByDescending(v=> v.Details.FirstOrDefault().Delivery.Product.ProductId)
    .ThenByDescending(v=> v.Details.FirstOrDefault().Delivery.Value)
    .ThenByDescending(v => v.CreatedAt)
    .Page(request.Page, request.RowsPerPage);

Here are entities and relationships:

public class Order : IEntity<int>
{
    public int CompanyId { get; set; }

    public virtual ICollection<OrderDetails> Details { get; set; }

    [Required]
    public DateTimeOffset CreatedAt { get; set; }

    [Key]
    public int Id { get; set; }
}

public class OrderDetails : IEntity<int>
{
    public int OrderId { get; set; }

    public int DeliveryId { get; set; }

    [ForeignKey(nameof(OrderId))]
    public virtual Order Order { get; set; }

    [ForeignKey(nameof(DeliveryId))]
    public virtual Delivery Delivery { get; set; }

    [Key]
    public int Id { get; set; }
}

public class Delivery : IEntity<int>
{
    [Required]
    public int ProductId { get; set; }

    public int Value { get; set; }

    [ForeignKey(nameof(ProductId))]
    public virtual Product Product { get; set; }

    [Key]
    public int Id { get; set; }
}

[Table("Products")]
public class Product : IEntity<int>
{
    [Required]
    public byte ProductCategoryId { get; set; }


    public virtual ICollection<Delivery> Deliveries { get; set; }

    [Key]
    public int Id { get; set; }
}

Looks like Details.FirstOrDefault() detaches the entity Delivery. The same solution worked with Entity Framework 6. How can I improve my query to get the date from database using only one query (suppressing the warining didn't help)?

1
1
11/10/2018 5:58:42 PM

Popular Answer

You should also be seeing a lot of Client evaluation logging warnings. And currently client evaluation doesn't play well with explicit/lazy loading.

The cause of the client evaluation in this case is the v.Details.FirstOrDefault() expression inside the ordering methods. And the challenge with the current stage of EF Core is to find the supported translatable equivalent LINQ construct.

In this particular scenario the solution (workaround) is to use intermediate SelectMany projection with Take(1). Replace the part starting from .OrderByDescending(..) up to Page(...) with the following:

.SelectMany(
    o => o.Details.Select(d => d.Delivery).Take(1).DefaultIfEmpty(),
    (o, d) => new { Order = o, Delivery = d })
.OrderByDescending(v => v.Delivery.ProductId)
.ThenByDescending(v => v.Delivery.Value)
.ThenByDescending(v => v.Order.CreatedAt)
.Select(v => v.Order) // restore the original projection
2
11/11/2018 5:06:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow