The following example is a manually simplified part of bigger code. My appology if it contains any (unwanted) bug.

Having three physical SQL tables Products, CustomerProduct, and UserProductCategoryMap, I need to compose the Entity Framework object that returns the result based on arguments of the Web API end-point method. I am reasonably good in SQL and C#; however, I am not fluent in Web API, nor in EF.

The method roughly works when written as presented below. However, I need to modify it so, that the first .Join() is used only when the customerCode (no null). The model Product defines the usual attributes, available for anyone. However, the CustomerProduct may define a better price (and other things not shown here). The Product model extended by the extra fields is named Product2. It is clear to me, that the method must always return the List even in cases, when the extension fields are not used (set to null).

How can I modify the part marked as (1) and (2) so, that the .Join() is used only when the non-null extension fields are to be used (when the customerCode is passed). When the extra table is not to be joined, how to make the part (1) also return the List instead of the List?

There are more questions related to the example, but I want to focuse firstly on that one.

        public async Task> GetAllAsync(  // (0)
            string login,
            string? customerCode
            bool showrestrictToTheUserCategories,
            string? filterCategory = null,
            int pageNumber = 1, int pageSize = 10)
        {
            var products2 = dbContext.Products    // (1) public DbSet Products { get; set; }
                .Join(dbContext.CustomerProducts, // (2) public DbSet CustomerProducts {get; set; }
                      z => z.ProductCode,
                      zz => zz.ProductCode,
                      (z, zz) => new {            // (3)
                          z.ProductCode,          // (4) 
                          z.ProductName,
                          z.CatalogPrice,
                          z.CategoryCode,

                          zz.CustomerCode,        // (5)  
                          zz.CustmerPrice,
                      })
                .Where(zz => zz.CustomerCode == customerCode)   // (6)
                .Select(x => new Product2
                {
                    ProductCode = x.ProductCode,    // (7)
                    ProductName = x.ProductName,
                    CatalogPrice = x.CatalogPrice,
                    CategoryCode = x.CategoryCode,

                    CustomerCode = x.CustomerCode,  // (8)
                    CustmerPrice = x.CustmerPrice,
                }).AsQueryable();
                
            // Here: public DbSet Products2 { get; set; }
            // is the extended version of products with extra columns
            // for the customer-related information.

            // Some users (login) may access only a subset of products
            // defined by the mapping table with categories visible products.
            if (restrictToTheUserCategories)    // (9)
            {
                products2 = products2.Where(z => dbContext.UserProductCategoryMap
                            .Any(m => m.Login == login 
                                 && m.CategoryCode == z.CategoryCode));
            }

            // The filter may contain more categories separated by space.
            if (!string.IsNullOrWhiteSpace(filterCategory))
            {
                var lst = filterCategory.Split(' ');
                foreach (string s in lst)
                {
                    products2 = products2.Where(x => x.CategoryCode.Contains(s));
                }
            }

            // Do the paging.
            var skipResults = (pageNumber - 1) * pageSize;
            var productsPage = products2.Skip(skipResults).Take(pageSize).ToList();

            // If the specific user cannot see the customer's price, null it.
            if (...user is in restricted mode...)
            {
                foreach (var p in productsPage)
                {
                    p.CustmerPrice = null;
                }
            }

            return productsPage;
        }