ARTICLE AD BOX
Learning EF, I am changing my mind on how to get the list of products with additional information (here symbolic code) -- see the loosely related (abandoned approach) at How to conditionally compose the EF from parts? . The basic set of properties related to the product item should be enriched by the navigation properties with details related to the specific customer (can be null), to the quantity on the stores (can be null), and to the actions (the list of action codes, possibly empty list or null).
The customer and store navigation properties can be filled only for the returned page (say 20 items from say 5000).
However, the actions may be used first to filter only subset of products, and can also be used for ordering (in the sense show products offered in some action first -- think in terms "new", "sales", "11+1free"). Therefore, it must be probably retrieved differently (than the customer or store). The filtering and ordering must be done before the .Skip(skipResults).Take(pageSize), the customer and store details can be retrieved later, after the page items were found. (I do not know what EF methods are translated to what SQL construct to make it the most efficient.)
public async Task<List<Product2>> GetAllAsync( // (0) string? customerCode, string? filterAction, int pageNumber = 1, int pageSize = 10) { DateTime now = DateTime.Now; // for determining the valid actions var products2 = dbContext.Products // (1) public DbSet<Product> Products { get; set; } ...like: ProductCode, ProductName, CatalogPrice ... customer = ... dbContext.CustomerProducts // (2) public DbSet<CustomerProduct> ...customer-related, like: CustomerPrice... storage = ... available quantity on several stores... actions = dbContext.ProductAttributes // (3) list of ProductAttribute .Where(x => x.ValidFrom <= now && now <= x.ValidTo)... ToList... ... filtering by filterAction... ... order by products in some action first, then by product code ... // Do the paging. var skipResults = (pageNumber - 1) * pageSize; return products2.Skip(skipResults).Take(pageSize).ToList(); }What is the correct approach? I am quite fluent with SQL (and C#); however, I am lost in the EF equivalents and differences. (I need to get some solid start -- tired from trials/mistakes.)
How to effectively add the actions, and how to effectively add the customer details and the storage details?
I am in early stages of development, using EF 8.0, and ASP .NET Core 8. If EF 10.0 would be better for the task, I will switch. The database is Azure SQL Server, compatibility level SQL Server 2019 (150).
