protected void EsDataSource1_esCreateEntity(object sender, EntitySpaces.Web.esDataSourceCreateEntityEventArgs e)
{
ProductsQuery p = new ProductsQuery("p");
CategoriesQuery c = new CategoriesQuery("c");
p.Select(p, c.CategoryName);
p.InnerJoin(c).On(p.CategoryID == c.CategoryID);
if (e.PrimaryKeys != null)
{
p.Where(p.ProductID == (int)e.PrimaryKeys[0]);
}
else
{
// They want to add a new one, lets do a select that brings back
// no records so that our CategoryName column (virutal) will be
// present in the underlying record format
p.Where(1 == 0);
}
Products prd = new Products();
prd.Load(p); // load the data (if any)
// Assign the Entity
e.Entity = prd;
}
public async Task <IEnumerable <ProductServiceResult> > GetProductsAsync(ProductsQuery qp)
{
try
{
// Here I list the query result from cache if they exist, but now the data can vary according to the category ID, page and amount of
// items per page. I have to compose a cache to avoid returning wrong data.
string cacheKey = GetCacheKeyForProductsQuery(qp);
var entities = await _cache.GetOrCreateAsync(cacheKey, (entry) => {
entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(1);
return(_productsRepository.ListPaginationAsync(qp));
});
var models = entities != null && entities.Any()
? _mapper.Map(entities, new List <ProductServiceResult>())
: new List <ProductServiceResult>();
return(models);
}
catch (Exception ex)
{
_logger?.LogError(ex.ToString());
throw;
}
}
public async Task <ActionResult <IList <ProductOutputModel> > > Search(
[FromQuery] ProductsQuery query)
{
var products = await this.productService.SearchAsync(query);
return(Ok(products));
}
public ICollection <ProductModel> GetAvailableProducts()
{
var query = new ProductsQuery(base.UnitOfWorkFactory);
query.AddSortCriteria(x => x.AmountLeft > 0);
return(query.Execute());
}
public int Products_GetCount(string serializedQuery)
{
ProductsQuery query = ProductsQuery.SerializeHelper.FromXml(
serializedQuery, typeof(ProductsQuery), AllKnownTypes) as ProductsQuery;
return(query.ExecuteScalar <int>());
}
public async Task <ExportResponse> ExportExcel(ProductsQuery productsQuery)
{
try
{
var products = await ListAsync(productsQuery);
if (products.Items == null || products.Items.Count == 0)
{
return(new ExportResponse("No products found"));
}
var memoryStream = _fileExportService.ExportExcel(products.Items.Select(p =>
new ProductExportDto
{
Id = p.Id,
Name = p.Name,
Photo = $"{GetHostURL()}/{p.Photo}",
Price = p.Price,
LastUpdated = p.LastUpdated.ToString("dd/MM/yyyy HH:mm:ss")
}), "Products");
return(new ExportResponse(memoryStream));
}
catch (Exception ex)
{
return(new ExportResponse($"An error occurred when exporting products: {ex.Message}"));
}
}
public async Task <QueryResult <Product> > ListAsync(ProductsQuery query)
{
IQueryable <Product> queryable = _context.Products
.Include(p => p.Category)
.AsNoTracking();
// AsNoTracking tells EF Core it doesn't need to track changes on listed entities. Disabling entity
// tracking makes the code a little faster
if (query.CategoryId.HasValue && query.CategoryId > 0)
{
queryable = queryable.Where(p => p.CategoryId == query.CategoryId);
}
// Here I count all items present in the database for the given query, to return as part of the pagination data.
int totalItems = await queryable.CountAsync();
// Here I apply a simple calculation to skip a given number of items, according to the current page and amount of items per page,
// and them I return only the amount of desired items. The methods "Skip" and "Take" do the trick here.
List <Product> products = await queryable.Skip((query.Page - 1) *query.ItemsPerPage)
.Take(query.ItemsPerPage)
.ToListAsync();
// Finally I return a query result, containing all items and the amount of items in the database (necessary for client-side calculations ).
return(new QueryResult <Product>
{
Items = products,
TotalItems = totalItems,
});
}
public async Task <IList <ProductOutputModel> > SearchAsync(ProductsQuery query)
=> await this.mapper
.ProjectTo <ProductOutputModel>(this.db.Products
.AsQueryable()
.Where(p => !query.CategoryId.HasValue ||
p.CategoryId == query.CategoryId.Value)
)
.ToListAsync();
public async Task <ActionResult <PagingResult <Product> > > GetProducts([FromQuery] ProductsQuery query)
{
try {
return(await query.ExecuteAsync(_context.Products.Include(p => p.Category), "ProductName"));
} catch (System.Linq.Dynamic.Core.Exceptions.ParseException) {
return(BadRequest());
}
}
public async Task <List <ProductModel> > Handle(ProductsQuery request, CancellationToken cancellationToken)
{
var data = await _context
.Products
.Include(x => x.ProductCategory)
.ToListAsync(cancellationToken);
return(_mapper.Map <List <ProductModel> >(data));
}
public async Task <QueryResult <Product> > List(ProductsQuery query)
{
string cacheKey = GetCacheKeyForProductsQuery(query);
var products = await _cache.GetOrCreateAsync(cacheKey, (entry) => {
entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(1);
return(_productRepository.List(query));
});
return(products);
}
public async Task <IEnumerable <Product> > ListPaginationAsync(ProductsQuery query)
{
IQueryable <Product> queryable = _products
.Include(p => p.Category)
.AsNoTracking();
// AsNoTracking tells EF Core it doesn't need to track changes on listed entities. Disabling entity
// tracking makes the code a little faster
if (query.CategoryId.HasValue && query.CategoryId > 0)
{
queryable = queryable.Where(p => p.CategoryId == query.CategoryId);
}
if (query.Price.HasValue && query.Price > 0)
{
queryable = queryable.Where(p => p.Price == query.Price);
}
if (query.Inventory.HasValue && query.Inventory > 0)
{
queryable = queryable.Where(p => p.Inventory == query.Inventory);
}
if (query.CreatedAtFrom.HasValue || query.CreatedAtTo.HasValue)
{
if (query.CreatedAtFrom.HasValue && query.CreatedAtTo.HasValue)
{
queryable = queryable.Where(p => p.CreatedAt >= query.CreatedAtFrom.Value && p.CreatedAt <= query.CreatedAtTo.Value);
}
else if (query.CreatedAtFrom.HasValue)
{
queryable = queryable.Where(p => p.CreatedAt >= query.CreatedAtFrom.Value);
}
else if (query.CreatedAtTo.HasValue)
{
queryable = queryable.Where(p => p.CreatedAt <= query.CreatedAtTo.Value);
}
}
if (!string.IsNullOrEmpty(query.Search))
{
queryable = queryable.Where(p => (p.Name + " " + p.Category.Name).ToLower().Contains(query.Search.ToLower()));
}
// Here I apply a simple calculation to skip a given number of items, according to the current page and amount of items per page,
// and them I return only the amount of desired items. The methods "Skip" and "Take" do the trick here.
if (query.Limit.HasValue && query.Offset.HasValue)
{
queryable = queryable.Skip(query.Offset.Value)
.Take(query.Limit.Value);
}
return(await queryable.ToListAsync());
}
public ProductsCollection Products_LoadByDynamic(string serializedQuery)
{
ProductsQuery query = ProductsQuery.SerializeHelper.FromXml(
serializedQuery, typeof(ProductsQuery), AllKnownTypes) as ProductsQuery;
ProductsCollection coll = new ProductsCollection();
coll.es.IsLazyLoadDisabled = true;
coll.Load(query);
return(coll);
}
public async Task <IList <Product> > Get([FromQuery] FilterDto filterOptions)
{
var query = new ProductsQuery();
if (filterOptions.Brands != null || filterOptions.Descriptions != null || filterOptions.Models != null)
{
query.Filter = filterOptions;
}
return(await _mediator.Send(query));
}
public IHttpActionResult GetProduct(Guid id)
{
ProductsQuery query = new ProductsQuery();
Product product = query.GetProduct(id);
if (product == null)
{
return(NotFound());
}
return(Ok(product));
}
private string GetCacheKeyForProductsQuery(ProductsQuery query)
{
string key = CacheKeys.ProductsList.ToString();
if (query.CategoryId.HasValue && query.CategoryId > 0)
{
key = string.Concat(key, "_", query.CategoryId.Value);
}
key = string.Concat(key, "_", query.Page, "_", query.ItemsPerPage);
return(key);
}
public async Task <QueryResult <Product> > ListAsync(ProductsQuery query)
{
// Here I list the query result from cache if they exist, but now the data can vary according to the category ID, page and amount of
// items per page. I have to compose a cache to avoid returning wrong data.
string cacheKey = GetCacheKeyForProductsQuery(query);
var products = await _cache.GetOrCreateAsync(cacheKey, (entry) => {
entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(1);
return(_productRepository.ListAsync(query));
});
return(products);
}
public IHttpActionResult Delete(Guid id)
{
ProductsQuery query = new ProductsQuery();
Product product = query.GetProduct(id);
if (product == null)
{
return(NotFound());
}
new DeleteProduct(product).Call();
return(Ok());
}
public ProductsProxyStub Products_QueryForEntity(string serializedQuery)
{
ProductsQuery query = ProductsQuery.SerializeHelper.FromXml(
serializedQuery, typeof(ProductsQuery), AllKnownTypes) as ProductsQuery;
Products obj = new Products();
if (obj.Load(query))
{
return(obj);
}
return(null);
}
public ProductsCollectionProxyStub Products_QueryForCollection(string serializedQuery)
{
ProductsQuery query = ProductsQuery.SerializeHelper.FromXml(
serializedQuery, typeof(ProductsQuery), AllKnownTypes) as ProductsQuery;
ProductsCollection coll = new ProductsCollection();
if (coll.Load(query))
{
return(coll);
}
return(null);
}
public ProductsCollection Products_LoadWithExtraColumn()
{
ProductsQuery p = new ProductsQuery("p");
SuppliersQuery s = new SuppliersQuery("s");
// Bring back the suppliers name for the Product from the Supplier table
p.Select(p, s.CompanyName.As("SupplierName"));
p.InnerJoin(s).On(p.SupplierID == s.SupplierID);
ProductsCollection coll = new ProductsCollection();
coll.Load(p);
return(coll);
}
public IActionResult GetProducts([FromBody] ProductSearchCriteriaModel criteria)
{
try
{
var criteriaMp = _mapper.Map <ProductSearchCriteriaDto>(criteria);
var query = new ProductsQuery(criteriaMp);
var products = _queryProcessor.Process(query);
return(new OkObjectResult(products));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
private string GetCacheKeyForProductsQuery(ProductsQuery query)
{
string key = CacheKeys.ProductsList.ToString();
if (query.CategoryId.HasValue && query.CategoryId > 0)
{
key = string.Concat(key, "_", query.CategoryId.Value);
}
if (query.Price.HasValue && query.Price > 0)
{
key = string.Concat(key, "_", query.Price.Value);
}
if (query.Inventory.HasValue && query.Inventory > 0)
{
key = string.Concat(key, "_", query.Inventory.Value);
}
if (query.CreatedAtFrom.HasValue || query.CreatedAtTo.HasValue)
{
if (query.CreatedAtFrom.HasValue && query.CreatedAtTo.HasValue)
{
key = string.Concat(key, "_", query.CreatedAtFrom.Value, "_", query.CreatedAtTo.Value);
}
else if (query.CreatedAtFrom.HasValue)
{
key = string.Concat(key, "_", query.CreatedAtFrom.Value);
}
else if (query.CreatedAtTo.HasValue)
{
key = string.Concat(key, "_", query.CreatedAtTo.Value);
}
}
if (!string.IsNullOrEmpty(query.Search))
{
key = string.Concat(key, "_", query.Search);
}
if (query.Limit.HasValue && query.Offset.HasValue)
{
key = string.Concat(key, "_", query.Limit, "_", query.Offset);
}
return(key);
}
protected void EsDataSource1_esSelect(object sender, EntitySpaces.Web.esDataSourceSelectEventArgs e)
{
ProductsQuery p = new ProductsQuery("P");
CategoriesQuery c = new CategoriesQuery("c");
// All columns from our products table and the CategoryName from the Category table, we really do not
// display all of the product fields so I could have selected individual fields out of the products
// table
p.Select(p, c.CategoryName);
p.InnerJoin(c).On(p.CategoryID == c.CategoryID);
// We supply the Collection and the Query, because we're letting the grid to auto paging and
// auto sorting. Otherwise, we could just load the collection ourselves and only supply the
// collection
e.Collection = new ProductsCollection();
e.Query = p;
}
static private void CorrelatedSubQuery()
{
OrderDetailsQuery oiq = new OrderDetailsQuery("oi");
ProductsQuery pq = new ProductsQuery("p");
oiq.Select(oiq.OrderID, (oiq.Quantity * oiq.UnitPrice).Sum().As("Total"))
.Where(oiq.ProductID
.In(
pq.Select(pq.ProductID).Where(oiq.ProductID == pq.ProductID).Distinct()
)
)
.GroupBy(oiq.OrderID);
OrderDetailsCollection coll = new OrderDetailsCollection();
if (coll.Load(oiq))
{
}
}
public IHttpActionResult Update(Guid id, [FromBody] Product product)
{
if (product == null || product.Id != id)
{
return(BadRequest());
}
ProductsQuery query = new ProductsQuery();
Product originalProduct = query.GetProduct(id);
if (originalProduct == null)
{
return(NotFound());
}
new UpdateProduct(id, product).Call();
return(Ok());
}
public async Task <List <ProductDto> > Handle(ProductsQuery request, CancellationToken cancellationToken)
{
ICollection <Product> result;
try
{
result = await _repository.GetAll()
.Include(e => e.Category)
.ToListAsync();
_logger.LogInformation($"Returning {result.Count} products.");
}
catch (Exception e)
{
_logger.LogError(e.Message);
throw;
}
var resultDto = _mapper.Map <ICollection <ProductDto> >(result);
return(resultDto.ToList());
}
public async Task <QueryResult <Product> > FindAsync(ProductsQuery productsQuery)
{
var orderBy = String.Join(", ", productsQuery.OrderBys.ToList());
var products = _context.Products.FromSqlRaw(
"EXEC dbo.FindProducts {0}, {1}, {2}, {3}",
productsQuery.Page,
productsQuery.ItemsPerPage,
orderBy,
productsQuery.Name
);
var totalCount = CallStaticFunc <int>("TotalCountFindProducts", new List <SqlParameter>()
{
new SqlParameter("@name", productsQuery.Name)
});
return(new QueryResult <Product>()
{
TotalItems = totalCount,
Items = products.ToList(),
});
}
public IEnumerable <Product> Handle(ProductsQuery query)
{
return(new[]
{
new Product {
Id = "1", Name = "Book 1"
},
new Product {
Id = "2", Name = "Book 2"
},
new Product {
Id = "3", Name = "Book 3"
},
new Product {
Id = "4", Name = "Book 4"
},
new Product {
Id = "5", Name = "Book 5"
},
new Product {
Id = "6", Name = "Book 6"
},
});
}
public ActionResult Index()
{
try
{
var user = _loggedInUser.GetLoggedInUser(System.Web.HttpContext.Current);
if (user == null)
{
throw new HttpException("Unable to find User");
}
var personAddress = new PersonQuery().GetPersonWithAddress(user.Id);
var settings = new ConnectionSettings(new Uri("http://127.0.0.1:9200"))
.DefaultIndex("usercart").DisableDirectStreaming();
var client = new ElasticClient(settings);
var searchResponse = client.Search <IndexToElasticSearch>(descriptor => descriptor.From(0).Size(100)
.Type("IndexToElasticSearch")
.Query(containerDescriptor => containerDescriptor
.Match(queryDescriptor => queryDescriptor.Field(search => search.Id).Query(user.Id.ToString()))));
var requestBodyInBytes = searchResponse.ApiCall.RequestBodyInBytes;
var requestBody = Encoding.UTF8.GetString(requestBodyInBytes);
//Write or log this request to some where
var isSuccess = searchResponse.IsValid;
if (isSuccess)
{
var checkoutModel = new FullCheckOutModel()
{
UserId = user.Id
};
var totalListOfProducts = 0M;
var hits = searchResponse.Hits.ToList();
if (hits.Count > 1)
{
throw new ElasticsearchClientException("Not sure what es brought");
}
var listOfProducts = new List <long>();
foreach (var hit in hits)
{
var productsListForUser = hit.Source.Items.Keys.ToList();
listOfProducts.AddRange(productsListForUser);
var products = new ProductsQuery().GetFromDatabase(listOfProducts);
foreach (var product in products)
{
var cost = 0M;
if (hit.Source.Items.TryGetValue(product.Id, out var quant))
{
cost = quant * product.Price;
totalListOfProducts = totalListOfProducts + cost;
}
checkoutModel.ProductsData.Add(new ProductsData()
{
Price = cost,
ProductData = new ProductData()
{
Id = product.Id, ProductName = product.ProductName, Price = product.Price
},
Quantity = quant
});
}
}
checkoutModel.AddressLine1 = personAddress.Address.AddressLine1;
checkoutModel.AddressLine2 = personAddress.Address.AddressLine2;
checkoutModel.City = personAddress.Address.City;
checkoutModel.State = personAddress.Address.State;
if (personAddress.Address.ZipCode != null)
{
checkoutModel.ZipCode = personAddress.Address.ZipCode.Value;
}
checkoutModel.Name = user.FirstName;
checkoutModel.TotalPriceOfAllProducts = totalListOfProducts;
return(View("Checkout", checkoutModel));
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(View("Checkout"));
}
