static private void Subquery()
{
OrdersQuery orders = new OrdersQuery("o");
OrderDetailsQuery details = new OrderDetailsQuery("oi");
orders.Select
(
orders.OrderID,
orders.OrderDate,
details.Select
(
details.UnitPrice.Max()
)
.Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
);
OrdersCollection coll = new OrdersCollection();
if (coll.Load(orders))
{
foreach (Orders order in coll)
{
}
}
}
public int OrderDetails_GetCount(string serializedQuery)
{
OrderDetailsQuery query = OrderDetailsQuery.SerializeHelper.FromXml(
serializedQuery, typeof(OrderDetailsQuery), AllKnownTypes) as OrderDetailsQuery;
return(query.ExecuteScalar <int>());
}
public List <CanelModel> PostOrderDetail(OrderDetailsQuery odq)
{
var list = new List <Expression <Func <Order, bool> > >();
if (!string.IsNullOrEmpty(odq.OrderCode))
{
list.Add(o => o.OrderCode == odq.OrderCode);
}
if (!string.IsNullOrEmpty(odq.PhoneCode))
{
list.Add(o => o.Customer.CPhone == odq.PhoneCode);
}
if (odq.OrderStartDate != DateTime.MinValue && odq.OrderEndDate.Date >= odq.OrderStartDate.Date)
{
list.Add(o => o.OrderDate > odq.OrderStartDate.Date);
}
if (odq.OrderEndDate != DateTime.MinValue && odq.OrderEndDate.Date >= odq.OrderStartDate.Date)
{
list.Add(o => o.OrderDate < odq.OrderEndDate.Date.AddHours(23).AddMinutes(59));
}
if (!string.IsNullOrEmpty(odq.CertCode))
{
var id = db.OrderItems.FirstOrDefault(oi => oi.CertificateNum == odq.CertCode).OrderId;
var orders = db.Orders.Include("Customer").Where(o => o.Id == id).Select(c => new CanelModel
{
Id = c.Id,
OrderCode = c.OrderCode,
OrderDate = c.OrderDate,
OrderStatus = c.OrderStatus,
CName = c.Customer.CName,
CPhone = c.Customer.CPhone
}).ToList();
return(orders);
}
else
{
Expression <Func <Order, bool> > express = PredicateBuilder.True <Order>();
foreach (var item in list)
{
express = express.And(item);
}
var orders = db.Orders.Include("Customer").Where(express).OrderByDescending(o => o.OrderDate).Select(c => new CanelModel
{
Id = c.Id,
OrderCode = c.OrderCode,
OrderDate = c.OrderDate,
OrderStatus = c.OrderStatus,
CName = c.Customer.CName,
CPhone = c.Customer.CPhone
}).ToList();
return(orders);
}
}
public OrderDetailsCollection OrderDetails_LoadByDynamic(string serializedQuery)
{
OrderDetailsQuery query = OrderDetailsQuery.SerializeHelper.FromXml(
serializedQuery, typeof(OrderDetailsQuery), AllKnownTypes) as OrderDetailsQuery;
OrderDetailsCollection coll = new OrderDetailsCollection();
coll.es.IsLazyLoadDisabled = true;
coll.Load(query);
return(coll);
}
public OrderDetailsProxyStub OrderDetails_QueryForEntity(string serializedQuery)
{
OrderDetailsQuery query = OrderDetailsQuery.SerializeHelper.FromXml(
serializedQuery, typeof(OrderDetailsQuery), AllKnownTypes) as OrderDetailsQuery;
OrderDetails obj = new OrderDetails();
if (obj.Load(query))
{
return(obj);
}
return(null);
}
public OrderDetailsCollectionProxyStub OrderDetails_QueryForCollection(string serializedQuery)
{
OrderDetailsQuery query = OrderDetailsQuery.SerializeHelper.FromXml(
serializedQuery, typeof(OrderDetailsQuery), AllKnownTypes) as OrderDetailsQuery;
OrderDetailsCollection coll = new OrderDetailsCollection();
if (coll.Load(query))
{
return(coll);
}
return(null);
}
public async Task <CustomerDetailsDto> GetCustomerDetails(OrderDetailsQuery request)
{
var baseUrl = _appSettings.CustomerDetailsServiceBaseUrl;
var code = _appSettings.CustomerDetailsServiceCode;
var email = request.User;
var endpoint = $"/api/GetUserDetails?code={code}&&email={email}";
var client = new CustomerDetailsClient(baseUrl);
var customerDetailsDto = await client.Process(endpoint);
return(customerDetailsDto);
}
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 async Task It_Fetches_Most_Recent_Order_With_Gifts_As_OrderItems()
{
var request = new OrderDetailsQuery()
{
CustomerId = "1",
User = "******"
};
var customerDetailsDto = new CustomerDetailsDto()
{
CustomerId = "1",
FirstName = "FirstName",
LastName = "LastName",
HouseNumber = "1A",
Postcode = "PostCode",
Street = "Street",
Town = "Town"
};
_customerDetailsService.Setup(s => s.GetCustomerDetails(It.IsAny <OrderDetailsQuery>()))
.ReturnsAsync(customerDetailsDto);
var order3 = CreateOrder(containsGift: true, customerId: "1", orderDate: new System.DateTime(2020, 10, 20), orderId: 3);
var order4 = CreateOrder(containsGift: false, customerId: "1", orderDate: new System.DateTime(2020, 10, 19), orderId: 4);
var orders = new List <Order> {
order3, order4
};
SeedContext(orders: orders);
_SUT = new OrderDetailsQueryHandler(_context.Object, _customerDetailsService.Object);
var orderDetailsDto = await _SUT.Handle(request, new System.Threading.CancellationToken());
orderDetailsDto.Order.OrderNumber.Should().Be(3);
orderDetailsDto.Order.DeliveryAddress.Should().Be("1A Street, Town, PostCode");
orderDetailsDto.Order.OrderDate.Should().Be("20-October-2020");
orderDetailsDto.Order.OrderItems.Count.Should().Be(2);
orderDetailsDto.Order.OrderItems[0].Product.Should().Be("Gift");
orderDetailsDto.Order.OrderItems[1].Product.Should().Be("Gift");
}
public jsResponse <EmployeesCollection, Employees> Employees_LoadHierarchical()
{
jsResponse <EmployeesCollection, Employees> response = new jsResponse <EmployeesCollection, Employees>();
try
{
// The Main Query
EmployeesQuery q = new EmployeesQuery("e");
q.Select(q.EmployeeID, q.FirstName, q.LastName, q.City, q.Country, q.HomePhone, q.Region, q.PostalCode, q.Title);
q.Where(q.EmployeeID < 7);
// The OrdersCollection
OrdersQuery o1 = q.Prefetch <OrdersQuery>(Employees.Prefetch_OrdersCollectionByEmployeeID);
EmployeesQuery emp1 = o1.GetQuery <EmployeesQuery>();
o1.Where(emp1.EmployeeID < 7);
// The OrdersDetailsCollection
OrderDetailsQuery od = q.Prefetch <OrderDetailsQuery>(Employees.Prefetch_OrdersCollectionByEmployeeID, Orders.Prefetch_OrderDetailsCollectionByOrderID);
EmployeesQuery emp2 = od.GetQuery <EmployeesQuery>();
OrdersQuery o2 = od.GetQuery <OrdersQuery>();
od.Where(emp2.EmployeeID < 7);
// Load It
EmployeesCollection coll = new EmployeesCollection();
if (coll.Load(q))
{
response.collection = coll;
response.columnCollection["Employees"] = jsColumn.PopulateColumns(coll[0]);
response.columnCollection["Orders"] = jsColumn.PopulateColumns(coll[0].OrdersCollectionByEmployeeID[0]);
response.columnCollection["OrderDetails"] = jsColumn.PopulateColumns(coll[0].OrdersCollectionByEmployeeID[0].OrderDetailsCollectionByOrderID[0]);
}
}
catch (Exception ex)
{
response.exception = ex.Message;
}
return(response);
}
public async Task <ActionResult> OrderDetails([FromBody] OrderDetailsQuery request)
{
try
{
var dto = await Mediator.Send(request);
return(Ok(dto));
}
catch (CustomerDetailsHttpResponseException)
{
return(ActionResultHelper.ToBadRequestActionResult(message: "Error While Fetching Customer Details", title: "Error", statusCode: StatusCodes.Status500InternalServerError));
}
catch (InvalidUser ex)
{
return(ActionResultHelper.ToBadRequestActionResult(message: ex.Message, title: "User Not Found", statusCode: StatusCodes.Status404NotFound));
}
catch (Exception ex)
{
return(ActionResultHelper.ToBadRequestActionResult(message: ex.Message, title: "Error", statusCode: StatusCodes.Status500InternalServerError));
}
}
public EmployeesCollection Employees_PrefetchSophisticated()
{
// EmployeeID = "1"
EmployeesCollection coll = new EmployeesCollection();
coll.Query.Where(coll.Query.EmployeeID == 1);
// Orders Query (nothing fancy, just ensure we're only getting Orders for EmployeeID = 1
OrdersQuery o = coll.Query.Prefetch <OrdersQuery>(Employees.Prefetch_OrdersCollectionByEmployeeID);
EmployeesQuery e1 = o.GetQuery <EmployeesQuery>();
o.Where(e1.EmployeeID == 1);
// OrderDetailsQuery (here we even limit the Select in addition to EmployeeID = 1) notice the "false"
OrderDetailsQuery od = coll.Query.Prefetch <OrderDetailsQuery>(false, Employees.Prefetch_OrdersCollectionByEmployeeID, Orders.Prefetch_OrderDetailsCollectionByOrderID);
EmployeesQuery e2 = od.GetQuery <EmployeesQuery>();
od.Where(e2.EmployeeID == 1);
od.Select(od.OrderID, od.ProductID, od.UnitPrice);
coll.Query.Load();
return(coll);
}
static private void CaseWhenThenEnd()
{
OrderDetailsQuery oq = new OrderDetailsQuery();
oq.Select
(
oq.Quantity,
oq.UnitPrice,
oq.UnitPrice
.Case()
.When(oq.Quantity < 50).Then(oq.UnitPrice)
.When(oq.Quantity >= 50 && oq.Quantity < 70).Then(oq.UnitPrice * .90)
.When(oq.Quantity >= 70 && oq.Quantity < 99).Then(oq.UnitPrice * .80)
.Else(oq.UnitPrice * .70)
.End().As("Adjusted Unit Price")
).OrderBy(oq.Quantity.Descending);
OrderDetailsCollection coll = new OrderDetailsCollection();
if (coll.Load(oq))
{
}
}