Dfyjkt

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)?

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