Update and retrieve data problem

[raysefo]

Hello guys,


In my Blazor Server Application, there is this master-detail data grid. This data grid is populated with IsActive = 1 data OnInitializedAsync method.
Here is the Order repository and related query which retrieves active data:

public class OrderRepository : IOrderRepository
    {
        private readonly IMSContext _db;
        
        public OrderRepository(IMSContext db)
        {
            _db = db;
           
        }
public async Task<IEnumerable<Order?>> GetAllOrders(ClaimsPrincipal user)
        {
            if (user.IsInRole("Administrators"))
            {
                return await _db.Orders.Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).ThenInclude(v => v.Vendor).ToListAsync();
            }
            
            return await _db.Orders.Where(u => u.DoneBy == user.Identity.Name).Include(d => d.OrderDetails.Where(od => od.IsActive == 1)).ThenInclude(v => v.Vendor).ToListAsync();

        }

And here is the OrderDetail repository which sets the related order detail to IsActive = 0

namespace IMS.Plugins.EFCore
 {
     public class OrderDetailRepository : IOrderDetailRepository
     {
         private readonly IMSContext _db;
    
         public OrderDetailRepository(IMSContext db)
         {
             _db = db;
         }
    
    
         public async Task PassiveOrderDetailAsync(OrderDetail orderDetail)
         {
              
             var detail = await this._db.OrdersDetail.FindAsync(orderDetail.Id);
             if (detail != null)
             {
                    
                 detail.IsActive = 0; // 0-Passive
                    
                 await _db.SaveChangesAsync();
             }
         }
          
     }
 }

Here is the method I am updating record IsActive = 0 and get all the records which are IsActive = 1. (GetAllOrders)

async Task PassiveDetail(OrderDetail orderDetail)
        {
            ...
    
             await PassiveOrderDetailUseCase.ExecuteAsync(orderDetail); // sets record IsActive=0
            _orders = await ViewAllOrdersUseCase.ExecuteAsync(user); //GetAllOrders, suppose to retrieve only IsActive = 1, but somehow it returns IsActive=0 the updated record
            
            StateHasChanged();       
    
        }

 

[Skydiver]

Why this?

await PassiveOrderDetailUseCase.ExecuteAsync(orderDetail);
orders = await ViewAllOrdersUseCase.ExecuteAsync(user);

Shouldn't it be something like below?

await _orderDetailsRepository.PassiveOrderDetailAsync(orderDetail);
orders = await _ordersRepository.GetAllOrders(user);

 

[Skydiver]

Anyway, it looks like you may have misunderstood how Includes() works. The lambda is currently only used to determine what the related entities are. Currently the lambda is not used for filtering the related entities:

Note

It is not currently possible to filter which related entities are loaded. Include will always bring in all related entities.

From Loading Related Entities

 

[raysefo]

But at this method, while the page loads, the query gets only the IsActive = 1. By the way, I am using EF Core 6.

protected override async Task OnInitializedAsync()
    {
        user = (await _authenticationStateProvider.GetAuthenticationStateAsync()).User;

        //userName = user.Identity.Name;
        if (!user.Identity.IsAuthenticated)
        {
            NavigationManager.NavigateTo("/Identity/Account/Login", false);
        }
        _orders = await ViewAllOrdersUseCase.ExecuteAsync(user);
        SelectedOrders = new List<Order?> { _orders.FirstOrDefault() };
        _vendors = await ViewAllVendorsUseCase.ExecuteAsync();
        _customers = await ViewAllCustomersUseCase.ExecuteAsync();
    }

 

[raysefo]

Seems like the loading of related data.

Here is my entities;

public class Order
    {
        public int Id { get; set; }
        
        [Required]
        public DateTime OrderDateTime { get; set; }
        [Required]
        [MaxLength(250)]
        public int CustomerId { get; set; }
        public string Status { get; set; }
        [MaxLength(50)]
        public string DoneBy { get; set; }
        public List<OrderDetail> OrderDetails { get; set; }
        public Customer Customer { get; set; }


    }
public class OrderDetail
    {
        public int Id { get; set; }
        
        [Required]
        [MaxLength(100)]
        public string ProductCode { get; set; }
        [Required]
        [MaxLength(250)]
        public string ProductName { get; set; }
        [Required]
        public int Quantity { get; set; }
        [Required]
        public double BuyUnitPrice { get; set; }
        public double CostRatio { get; set; }
        public double UnitCost { get; set; }
        public double TotalBuyPrice { get; set; }
        public double? SellUnitPrice { get; set; }
        public double? TotalSellPrice { get; set; }
        [MaxLength(150)]
        public string? ShippingNumber { get; set; }
        public string? Status { get; set; }
        [MaxLength(150)]
        public string? TrackingNumber { get; set; }
        [MaxLength(400)]
        public string? Description { get; set; }
        public string? Currency { get; set; }
        public string? CustomerStockCode { get; set; }
        public string? CustomerOrderNumber { get; set; }
        public int IsActive { get; set; }
        public double? TotalUnitCost { get; set; }
        public int OrderId { get; set; }
        public int VendorId { get; set; }
        public Order Order { get; set; }
        public Vendor Vendor { get; set; }
      
    }
public class Customer
    {
        public int Id { get; set; }
        [Required]
        public long TaxNumber { get; set; }
        [Required]
        public string TaxAdministration { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public string Address { get; set; }
        [Required]
        public string DeliveryAddress { get; set; }
        [Required]
        [RegularExpression(@"^((?!\.)[\w-_.]*[^.])(@\w+)(\.\w+(\.\w+)?[^.\W])$", ErrorMessage = "Invalid email address.")]
        public string Email { get; set; }
        [Required]
        public string PhoneNumber { get; set; }
        [Required]
        public string MainResponsibleName { get; set; }
        public string AssistantResponsibleName { get; set; }
    }
public class Vendor
    {
        public int Id { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public string Address { get; set; }
        [Required]
        [RegularExpression(@"^((?!\.)[\w-_.]*[^.])(@\w+)(\.\w+(\.\w+)?[^.\W])$", ErrorMessage = "Invalid email address.")]
        public string Email { get; set; }
        [Required]
        public string PhoneNumber { get; set; }
        [Required]
        public string MainResponsibleName { get; set; }
        public string AssistantResponsibleName { get; set; }
       public List<OrderDetail> OrderDetails { get; set; }

    }

How to add Customer to this linq? Couldn't manage.

return await _db.Orders
                    .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                    .ThenInclude(v => v.Vendor)
                    .AsNoTracking()
                    .ToListAsync();

 

[Skydiver]

I think you should use the Include() to bring in the related entites, then use Where() after the includes.

Recall what you were doing to use includes in your other question about dynamic queries?

 

[raysefo]

When I add AsNoTracking() the query in my previous post gets only IsActive = 1 that is what I want but somehow doesn't get the Customer which is why I want to include it. Order have 2 children, Customer and OrderDetail. OrderDetail has one, Vendor. Couldn't manage to include Customer tough to the query.

 

[jmcilhinney]

If you want to include two children then you call Include twice.

You cannot filter children. If you want to be able to filter OrderDetails then that needs to be the subject of the query and then you can include the Order related to each one.

 

[raysefo]

Can you elaborate @jmcilhinney, please? Frankly, I did not fully understand.

 

[jmcilhinney]

You are calling Include once, so obviously you will only be able to include one child. If you want to include two children, you need to call Include twice. That's two times, once for each child.

The root of your query is currently Orders, meaning that you cannot filter OrderDetails. If you want to filter OrderDetails then make that the root of your query, so you can filter that entity. You can then include Order if you want to.

 

[raysefo]

Thank you, I think this also works

Orders
                    .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                    .ThenInclude(v => v.Vendor)
                    .Include(c => c.Customer)                   
                    .AsNoTracking()
                    .ToListAsync();

 

[jmcilhinney]

Just did a quick search and apparently filtering within Include was added in EF Core 5. Sorry for the misleading information.

One small thing you should note is that you are using misleading names for your lambda parameters. The only place you're using a good name is in Where(od => od.IsActive == 1), where od is type OrderDetails. In Include(d => d.OrderDetails, d is type Order so it should be o. In ThenInclude(v => v.Vendor), v is type OrderDetails so it should be od. In Include(c => c.Customer), c is type Order so it should be o. Use the initials of the type of the parameter, not the initials of the property of that type you want to access. The whole thing should be:

return await _db.Orders.Include(o => o.OrderDetails.Where(od => od.IsActive == 1))
                       .ThenInclude(od => od.Vendor)
                       .Include(o => o.Customer)                   
                       .AsNoTracking()
                       .ToListAsync();

Also, you shouldn't really be using od.IsActive == 1. That property should be type bool, so it should be true or false, not 1. If you're using SQL Server then it will be type bit in the database and store 1 or 0 but that maps to bool in your C# code. That means the code should really be this:

return await _db.Orders.Include(o => o.OrderDetails.Where(od => od.IsActive))
                       .ThenInclude(od => od.Vendor)
                       .Include(o => o.Customer)                   
                       .AsNoTracking()
                       .ToListAsync();

 

[Skydiver]

So if filtering is supported, why was the order detail that should have been filtered out included in the results?

I feel that using AsNoTracking() is just a way to force EF to do a fresh query and allocate brand new objects. I feel that the point of having EF was for it to keep track of objects in memory so that they can correctly map back into the relational database -- basically make an object oriented world fit into the relational world.

 

[raysefo]

I will try with Raw SQL queries.

 

[Skydiver]

Why? Seems to be even worse than using not using tracking. Now you'll have memory eaten up by objects tracked by EF, and then you'll have rows of data representing what EF is already tracking.