Update and retrieve data problem

raysefo

Well-known member
Joined
Feb 22, 2019
Messages
287
Programming Experience
10+
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:
GetAllOrders:
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
PassiveOrderDetailAsync:
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)
PassiveDetail:
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();       
    
        }
0.png
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
Why this?
C#:
await PassiveOrderDetailUseCase.ExecuteAsync(orderDetail);
orders = await ViewAllOrdersUseCase.ExecuteAsync(user);

Shouldn't it be something like below?
C#:
await _orderDetailsRepository.PassiveOrderDetailAsync(orderDetail);
orders = await _ordersRepository.GetAllOrders(user);
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
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

Well-known member
Joined
Feb 22, 2019
Messages
287
Programming Experience
10+
But at this method, while the page loads, the query gets only the IsActive = 1. By the way, I am using EF Core 6.

OnInitializedAsync:
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

Well-known member
Joined
Feb 22, 2019
Messages
287
Programming Experience
10+
Seems like the loading of related data.

Here is my entities;
C#:
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.
C#:
return await _db.Orders
                    .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                    .ThenInclude(v => v.Vendor)
                    .AsNoTracking()
                    .ToListAsync();
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
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

Well-known member
Joined
Feb 22, 2019
Messages
287
Programming Experience
10+
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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,643
Location
Sydney, Australia
Programming Experience
10+
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.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,643
Location
Sydney, Australia
Programming Experience
10+
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

Well-known member
Joined
Feb 22, 2019
Messages
287
Programming Experience
10+
Thank you, I think this also works :)
C#:
Orders
                    .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                    .ThenInclude(v => v.Vendor)
                    .Include(c => c.Customer)                   
                    .AsNoTracking()
                    .ToListAsync();
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,643
Location
Sydney, Australia
Programming Experience
10+
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:
C#:
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:
C#:
return await _db.Orders.Include(o => o.OrderDetails.Where(od => od.IsActive))
                       .ThenInclude(od => od.Vendor)
                       .Include(o => o.Customer)                   
                       .AsNoTracking()
                       .ToListAsync();
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
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.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
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.
 

raysefo

Well-known member
Joined
Feb 22, 2019
Messages
287
Programming Experience
10+
How else will I know if there is a problem in LINQ? My first linq was without AsNoTracking() but somehow it still got IsActive = 0.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
Writing a t raw

Your alternate test is to use the same code (without AsNoTracking()),;/ but create a brand but IMSContext instance.
 

raysefo

Well-known member
Joined
Feb 22, 2019
Messages
287
Programming Experience
10+
What do you mean by creating a brand new IMSContext instance? Can you please elaborate? By the way, I gave up using raw sql because I had to change too many things on the page.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
Your repository classes take IMSContext in their constructors. If you were using standard practices, you would have let EF use .NET Core's dependency injection to have it pass in a per request instance of a class that implements IMSContext.

The reason why a per request instance is shared among all the code running within a request is so that there is a consistent "database session" that has a consistent "view" of the database. Often in ORMs, these "sessions" maintain the in memory tracking of objects that it has handed out so that the next time you ask for the same data, you will get back the object that represents that data. Any queries will try to give you back an object that it has already given out, before trying to read from the database. Presumably, when you used the AsNoTracking() no attempt is made to search for an existing object. Instead a fresh database read is done.

My suggestion to use a different instance is so that you get a new independent "database session" and "view" of the data that has been persisted into the database.

To be able to inject a different IMSContext, you'll need to understand how your IOC container/dependency injection framework works so that you can force to to use a fresh instance, instead of using the per request instance. I don't know enough about how the .NET Core's IOC framework to be able to even begin to guide you on going about this.

But there is hope! If you truly wrote your repositories to be unit testable, you can just not use the IOC framework and do all the creation of objects to pass into the repository constructors yourself. Do all that testing within a unit test instead of an interactive web app session.
 
Top Bottom