public Tax GetWithTaxRate(int taxId, int countryId, int stateProvinceId, string zipOrPostalCode) { Expression <Func <TaxRate, bool> > where = rate => rate.CountryId == countryId; var tax = Repository.Where(x => x.Id == taxId) .Join <TaxRate>("Id", "TaxId", joinType: JoinType.LeftOuter) .Where(where) .Relate(RelationTypes.OneToMany <Tax, TaxRate>()) .SelectNested() .FirstOrDefault(); if (tax == null) { return(null); } //filter by state and zip var taxRates = tax.TaxRates.Where(x => x.StateOrProvinceId == stateProvinceId && x.ZipOrPostalCode == zipOrPostalCode) .ToList(); if (taxRates.Any()) { tax.TaxRates = taxRates; return(tax); } //filter by state only taxRates = tax.TaxRates.Where(x => x.StateOrProvinceId == stateProvinceId).ToList(); if (taxRates.Any()) { tax.TaxRates = taxRates; return(tax); } return(tax); }
public override ProductSpecification Get(int id) { return(Repository.Where(x => x.Id == id) .Join <ProductSpecificationValue>("Id", "ProductSpecificationId", joinType: JoinType.LeftOuter) .Join <AvailableAttributeValue>("AvailableAttributeValueId", "Id", joinType: JoinType.LeftOuter) .Join <AvailableAttribute>("AvailableAttributeId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <ProductSpecification, ProductSpecificationValue>()) .Relate <AvailableAttributeValue>((productSpecification, availableAttributeValue) => { if (productSpecification.Tag == null) { productSpecification.Tag = new List <AvailableAttributeValue>(); } var attributeList = (List <AvailableAttributeValue>)productSpecification.Tag; if (!attributeList.Contains(availableAttributeValue)) { attributeList.Add(availableAttributeValue); } var pav = productSpecification.ProductSpecificationValues.FirstOrDefault( x => x.AvailableAttributeValueId == availableAttributeValue.Id); if (pav != null) { pav.AvailableAttributeValue = availableAttributeValue; } }) .Relate <AvailableAttribute>((productSpecification, availableAttribute) => { availableAttribute.AvailableAttributeValues = (List <AvailableAttributeValue>)productSpecification.Tag; productSpecification.AvailableAttribute = availableAttribute; }) .SelectNested() .FirstOrDefault()); }
public override AvailableAttribute Get(int id) { return(Repository.Join <AvailableAttributeValue>("Id", "AvailableAttributeId", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <AvailableAttribute, AvailableAttributeValue>()) .Where(x => x.Id == id) .SelectNested() .FirstOrDefault()); }
public override Vendor Get(int id) { return(Repository.Where(x => x.Id == id) .Join <VendorUser>("Id", "VendorId", joinType: JoinType.LeftOuter) .Join <User>("UserId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <Vendor, User>()) .SelectNested() .FirstOrDefault()); }
public override Role Get(int id) { return(Repository.Where(x => x.Id == id) .Join <RoleCapability>("Id", "RoleId", joinType: JoinType.LeftOuter) .Join <Capability>("CapabilityId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <Role, Capability>()) .SelectNested() .FirstOrDefault()); }
public ConsentGroup GetWithConsents(int consentGroupId) { Expression <Func <Consent, object> > orderBy = consent => consent.DisplayOrder; var consentGroup = Repository.Join <Consent>("Id", "ConsentGroupId") .Relate(RelationTypes.OneToMany <ConsentGroup, Consent>()) .Where(x => x.Id == consentGroupId) .OrderBy(orderBy, RowOrder.Ascending) .SelectNested() .FirstOrDefault(); return(consentGroup); }
public IList <ProductAttribute> GetByProductId(int productId, bool onlyVariantSpecific = false) { var q = Repository.Where(x => x.ProductId == productId) .Join <AvailableAttribute>("AvailableAttributeId", "Id", joinType: JoinType.LeftOuter) //.Join<Product>("ProductId", "Id") .Join <ProductAttributeValue>("Id", "ProductAttributeId", SourceColumn.Parent, joinType: JoinType.LeftOuter) .Join <AvailableAttributeValue>("AvailableAttributeValueId", "Id", joinType: JoinType.LeftOuter); //should we restrict to only variant specific attributes if (onlyVariantSpecific) { var allowedInputTypes = VariantHelper.GetVariantSpecificFieldTypes(); Expression <Func <ProductAttribute, bool> > variantWhere = attribute => allowedInputTypes.Contains(attribute.InputFieldType); q.Where(variantWhere); } return(q .Relate(RelationTypes.OneToMany <ProductAttribute, ProductAttributeValue>()) .Relate <AvailableAttributeValue>((productAttribute, availableAttributeValue) => { if (productAttribute.Tag == null) { productAttribute.Tag = new List <AvailableAttributeValue>(); } var attributeList = (List <AvailableAttributeValue>)productAttribute.Tag; if (!attributeList.Contains(availableAttributeValue)) { attributeList.Add(availableAttributeValue); } var pav = productAttribute.ProductAttributeValues.FirstOrDefault( x => x.AvailableAttributeValueId == availableAttributeValue.Id); if (pav != null) { pav.AvailableAttributeValue = availableAttributeValue; } }) .Relate <AvailableAttribute>((productAttribute, availableAttribute) => { availableAttribute.AvailableAttributeValues = (List <AvailableAttributeValue>)productAttribute.Tag; productAttribute.AvailableAttribute = availableAttribute; }) .OrderBy(x => x.DisplayOrder) .SelectNested() .ToList()); }
public IEnumerable <AvailableAttribute> GetAvailableAttributes(out int totalResults, string searchText = null, int page = 1, int count = Int32.MaxValue) { var query = Repository; if (!searchText.IsNullEmptyOrWhiteSpace()) { query = query.Where(x => x.Name.Contains(searchText)); } return(query.Join <AvailableAttributeValue>("Id", "AvailableAttributeId", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <AvailableAttribute, AvailableAttributeValue>()) .OrderBy(x => x.Name) .SelectNestedWithTotalMatches(out totalResults, page, count)); }
public ProductVariant GetByAttributeValueIds(IList <int> productAttributeValueIds) { //let's create a custom query here var providerSpecificSelector = DatabaseManager.IsSqlServerProvider() ? "top 1" : ""; var variantIdFetcher = $"select {providerSpecificSelector} productvariantid from productvariantattribute where productattributevalueid in ({string.Join(",", productAttributeValueIds)}) group by productvariantid HAVING COUNT(*) = {productAttributeValueIds.Count} order by count(*) desc"; string sql = ""; if (DatabaseManager.IsSqlServerProvider()) { sql = $"SELECT t2.[ProductVariantId] AS ProductVariantAttribute_ProductVariantId,t2.[ProductAttributeId] AS ProductVariantAttribute_ProductAttributeId,t2.[ProductAttributeValueId] AS ProductVariantAttribute_ProductAttributeValueId,t2.[Id] AS ProductVariantAttribute_Id,t3.[ProductId] AS ProductAttribute_ProductId,t3.[AvailableAttributeId] AS ProductAttribute_AvailableAttributeId,t3.[InputFieldType] AS ProductAttribute_InputFieldType,t3.[Label] AS ProductAttribute_Label,t3.[DisplayOrder] AS ProductAttribute_DisplayOrder,t3.[IsRequired] AS ProductAttribute_IsRequired,t3.[Id] AS ProductAttribute_Id,t4.[ProductAttributeId] AS ProductAttributeValue_ProductAttributeId,t4.[AvailableAttributeValueId] AS ProductAttributeValue_AvailableAttributeValueId,t4.[Label] AS ProductAttributeValue_Label,t4.[Id] AS ProductAttributeValue_Id,t5.[ProductId] AS WarehouseInventory_ProductId,t5.[ProductVariantId] AS WarehouseInventory_ProductVariantId,t5.[WarehouseId] AS WarehouseInventory_WarehouseId,t5.[TotalQuantity] AS WarehouseInventory_TotalQuantity,t5.[ReservedQuantity] AS WarehouseInventory_ReservedQuantity,t5.[Id] AS WarehouseInventory_Id,t1.[ProductId] AS ProductVariant_ProductId,t1.[Sku] AS ProductVariant_Sku,t1.[Gtin] AS ProductVariant_Gtin,t1.[Mpn] AS ProductVariant_Mpn,t1.[Price] AS ProductVariant_Price,t1.[ComparePrice] AS ProductVariant_ComparePrice,t1.[TrackInventory] AS ProductVariant_TrackInventory,t1.[CanOrderWhenOutOfStock] AS ProductVariant_CanOrderWhenOutOfStock,t1.[MediaId] AS ProductVariant_MediaId,t1.[Id] AS ProductVariant_Id FROM (SELECT [ProductId],[Sku],[Gtin],[Mpn],[Price],[ComparePrice],[TrackInventory],[CanOrderWhenOutOfStock],[MediaId],[Id] FROM [ProductVariant] t1) AS t1 LEFT OUTER JOIN [ProductVariantAttribute] t2 ON t1.[Id] = t2.[ProductVariantId] LEFT OUTER JOIN [ProductAttribute] t3 ON t2.[ProductAttributeId] = t3.[Id] LEFT OUTER JOIN [ProductAttributeValue] t4 ON t3.[Id] = t4.[ProductAttributeId] LEFT OUTER JOIN [WarehouseInventory] t5 ON t1.[Id] = t5.[ProductVariantId] WHERE t1.[Id] IN ({variantIdFetcher})"; } else { sql = $"SELECT t2.`ProductVariantId` AS ProductVariantAttribute_ProductVariantId,t2.`ProductAttributeId` AS ProductVariantAttribute_ProductAttributeId,t2.`ProductAttributeValueId` AS ProductVariantAttribute_ProductAttributeValueId,t2.`Id` AS ProductVariantAttribute_Id,t3.`ProductId` AS ProductAttribute_ProductId,t3.`AvailableAttributeId` AS ProductAttribute_AvailableAttributeId,t3.`InputFieldType` AS ProductAttribute_InputFieldType,t3.`Label` AS ProductAttribute_Label,t3.`DisplayOrder` AS ProductAttribute_DisplayOrder,t3.`IsRequired` AS ProductAttribute_IsRequired,t3.`Id` AS ProductAttribute_Id,t4.`ProductAttributeId` AS ProductAttributeValue_ProductAttributeId,t4.`AvailableAttributeValueId` AS ProductAttributeValue_AvailableAttributeValueId,t4.`Label` AS ProductAttributeValue_Label,t4.`Id` AS ProductAttributeValue_Id,t5.`ProductId` AS WarehouseInventory_ProductId,t5.`ProductVariantId` AS WarehouseInventory_ProductVariantId,t5.`WarehouseId` AS WarehouseInventory_WarehouseId,t5.`TotalQuantity` AS WarehouseInventory_TotalQuantity,t5.`ReservedQuantity` AS WarehouseInventory_ReservedQuantity,t5.`Id` AS WarehouseInventory_Id,t1.`ProductId` AS ProductVariant_ProductId,t1.`Sku` AS ProductVariant_Sku,t1.`Gtin` AS ProductVariant_Gtin,t1.`Mpn` AS ProductVariant_Mpn,t1.`Price` AS ProductVariant_Price,t1.`ComparePrice` AS ProductVariant_ComparePrice,t1.`TrackInventory` AS ProductVariant_TrackInventory,t1.`CanOrderWhenOutOfStock` AS ProductVariant_CanOrderWhenOutOfStock,t1.`MediaId` AS ProductVariant_MediaId,t1.`Id` AS ProductVariant_Id FROM (SELECT `ProductId`,`Sku`,`Gtin`,`Mpn`,`Price`,`ComparePrice`,`TrackInventory`,`CanOrderWhenOutOfStock`,`MediaId`,`Id` FROM `ProductVariant` t1) AS t1 LEFT OUTER JOIN `ProductVariantAttribute` t2 ON t1.`Id` = t2.`ProductVariantId` LEFT OUTER JOIN `ProductAttribute` t3 ON t2.`ProductAttributeId` = t3.`Id` LEFT OUTER JOIN `ProductAttributeValue` t4 ON t3.`Id` = t4.`ProductAttributeId` LEFT OUTER JOIN `WarehouseInventory` t5 ON t1.`Id` = t5.`ProductVariantId` WHERE t1.`Id` IN ({variantIdFetcher})"; } var variant = Repository .Join <ProductVariantAttribute>("Id", "ProductVariantId", joinType: JoinType.LeftOuter) .Join <ProductAttribute>("ProductAttributeId", "Id", joinType: JoinType.LeftOuter) .Join <ProductAttributeValue>("Id", "ProductAttributeId", joinType: JoinType.LeftOuter) .Join <WarehouseInventory>("Id", "ProductVariantId", SourceColumn.Parent, joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <ProductVariant, ProductVariantAttribute>()) .Relate <ProductAttribute>((productVariant, attribute) => { var pva = productVariant.ProductVariantAttributes.FirstOrDefault(x => x.ProductAttributeId == attribute.Id); if (pva != null) { pva.ProductAttribute = attribute; } }) .Relate <ProductAttributeValue>((productVariant, value) => { var pva = productVariant.ProductVariantAttributes.FirstOrDefault(x => x.ProductAttributeValueId == value.Id); if (pva != null) { pva.ProductAttributeValue = value; } }) .Relate(RelationTypes.OneToMany <ProductVariant, WarehouseInventory>()) .QueryNested(sql) .FirstOrDefault(); return(variant); }
public IList <Shipment> GetShipmentsByOrderId(int orderId) { Expression <Func <OrderItem, bool> > whereOrderIdMatches = item => item.OrderId == orderId; return(Repository.Join <ShipmentItem>("Id", "ShipmentId", joinType: JoinType.LeftOuter) .Join <OrderItem>("OrderItemId", "Id", joinType: JoinType.LeftOuter) .Join <Product>("ProductId", "Id", joinType: JoinType.LeftOuter) .Join <ShipmentHistory>("Id", "ShipmentId", SourceColumn.Parent, JoinType.LeftOuter) .Join <Warehouse>("WarehouseId", "Id", SourceColumn.Parent, JoinType.LeftOuter) .Join <Address>("AddressId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <Shipment, ShipmentItem>()) .Relate(RelationTypes.OneToMany <Shipment, ShipmentHistory>()) .Relate <OrderItem>((shipment, item) => { if (shipment.ShipmentItems == null) { return; } var shipmentItem = shipment.ShipmentItems.FirstOrDefault(x => x.OrderItemId == item.Id); if (shipmentItem != null) { shipmentItem.OrderItem = item; } }) .Relate <Product>((shipment, product) => { if (shipment.ShipmentItems == null) { return; } var shipmentItem = shipment.ShipmentItems.FirstOrDefault( x => x.OrderItem != null && x.OrderItem.ProductId == product.Id); if (shipmentItem != null) { shipmentItem.OrderItem.Product = product; } }) .Relate(RelationTypes.OneToOne <Shipment, Warehouse>()) .Relate <Address>((shipment, address) => { shipment.Warehouse.Address = address; }) .Where(whereOrderIdMatches) .SelectNested() .ToList()); }
public IList <ProductSpecification> GetByProductId(int productId, int?groupId = null) { var query = Repository .Join <ProductSpecificationGroup>("ProductSpecificationGroupId", "Id", joinType: JoinType.LeftOuter) .Join <ProductSpecificationValue>("Id", "ProductSpecificationId", SourceColumn.Parent, JoinType.LeftOuter) .Join <AvailableAttributeValue>("AvailableAttributeValueId", "Id", joinType: JoinType.LeftOuter) .Join <AvailableAttribute>("AvailableAttributeId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToOne <ProductSpecification, ProductSpecificationGroup>()) .Relate(RelationTypes.OneToMany <ProductSpecification, ProductSpecificationValue>()) .Relate <AvailableAttributeValue>((specification, value) => { if (specification.Tag == null) { specification.Tag = new List <AvailableAttributeValue>(); } var attributeList = (List <AvailableAttributeValue>)specification.Tag; if (!attributeList.Contains(value)) { attributeList.Add(value); } var pav = specification.ProductSpecificationValues.FirstOrDefault( x => x.AvailableAttributeValueId == value.Id); if (pav != null) { pav.AvailableAttributeValue = value; } }) .Relate <AvailableAttribute>((specification, availableAttribute) => { availableAttribute.AvailableAttributeValues = (List <AvailableAttributeValue>)specification.Tag; specification.AvailableAttribute = availableAttribute; }) .Where(x => x.ProductId == productId) .OrderBy(x => x.DisplayOrder); if (groupId.HasValue) { query = query.Where(x => x.ProductSpecificationGroupId == groupId); } return(query.SelectNested().ToList()); }
public IList <User> GetUsers(string searchText, int[] restrictToRoles, Expression <Func <User, object> > orderBy, SortOrder sortOrder, int page, int count, out int totalMatches, bool negateRoleRestriction = false, Expression <Func <User, bool> > where = null) { var query = Repository .Where(x => !x.Deleted) .Join <UserRole>("Id", "UserId", joinType: JoinType.LeftOuter) .Join <Role>("RoleId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <User, Role>()); if (!searchText.IsNullEmptyOrWhiteSpace()) { searchText = searchText.Trim(); query = query.Where(x => x.FirstName.Contains(searchText) || x.LastName.Contains(searchText) || x.LastLoginIpAddress.Contains(searchText) || x.Email.Contains(searchText)); } if (restrictToRoles != null && restrictToRoles.Any()) { var roleIds = restrictToRoles.ToList(); Expression <Func <Role, bool> > roleWhere; if (negateRoleRestriction) { roleWhere = role => !roleIds.Contains(role.Id); } else { roleWhere = role => roleIds.Contains(role.Id); } query = query.Where(roleWhere); } if (orderBy == null) { orderBy = x => x.Name; } if (where != null) { query = query.Where(where); } query = query.OrderBy(orderBy, sortOrder == SortOrder.Ascending ? RowOrder.Ascending : RowOrder.Descending); return(query.SelectNestedWithTotalMatches(out totalMatches, page, count).ToList()); }
private IEntitySet <Menu> GetByWhere(Expression <Func <Menu, bool> > @where) { Expression <Func <MenuItem, object> > orderBy = x => x.DisplayOrder; return(_eventPublisherService.Filter(Repository .Join <MenuItem>("Id", "MenuId", joinType: JoinType.LeftOuter) .Join <SeoMeta>("SeoMetaId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <Menu, MenuItem>()) .Relate <SeoMeta>((menu, meta) => { var menuItems = menu.MenuItems.Where(x => x.SeoMetaId == meta.Id); foreach (var menuItem in menuItems) { menuItem.SeoMeta = meta; } }) .Where(where) .OrderBy(orderBy, RowOrder.Ascending))); }
public IEntitySet <T> Filter(IEntitySet <T> entity) { var entityName = typeof(T).Name; Expression <Func <EntityStore, bool> > storeWhere = x => true; if (!_alwaysAllTypes.Contains(typeof(T))) { //restrict to current store var storeId = ApplicationEngine.CurrentStore?.Id ?? 0; var allowedStoreIds = new List <int> { storeId, 0 }; storeWhere = store => store.StoreId == null || allowedStoreIds.Contains(store.StoreId); } entity.Join <EntityStore>("Id", "EntityId", SourceColumn.Parent, JoinType.LeftOuter, (storeEntity, store) => store.EntityName == entityName) .Join <Store>("StoreId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <T, Store>()) .Where(storeWhere); return(entity); }
private User GetByWhere(Expression <Func <User, bool> > where) { var query = Repository.Where(where) .Join <UserRole>("Id", "UserId", joinType: JoinType.LeftOuter) .Join <Role>("RoleId", "Id", joinType: JoinType.LeftOuter) .Join <RoleCapability>("Id", "RoleId", joinType: JoinType.LeftOuter) .Join <Capability>("CapabilityId", "Id", joinType: JoinType.LeftOuter) .Join <UserCapability>("Id", "UserId", SourceColumn.Parent, JoinType.LeftOuter) .Join <Capability>("CapabilityId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <User, Role>()) .Relate <Capability>((user, capability) => { user.Capabilities = user.Capabilities ?? new List <Capability>(); if (user.Capabilities.All(x => x.Id != capability.Id)) { user.Capabilities.Add(capability); } }); var userObject = query.SelectNested().FirstOrDefault(); return(userObject); }
private IEntitySet <ProductVariant> GetQuery(Expression <Func <ProductVariant, bool> > where) { return(Repository.Where(where) .Join <ProductVariantAttribute>("Id", "ProductVariantId", joinType: JoinType.LeftOuter) .Join <ProductAttributeValue>("ProductAttributeValueId", "Id", joinType: JoinType.LeftOuter) .Join <ProductAttribute>("ProductAttributeId", "Id", joinType: JoinType.LeftOuter) .Join <AvailableAttribute>("AvailableAttributeId", "Id", joinType: JoinType.LeftOuter) .Join <AvailableAttributeValue>("Id", "AvailableAttributeId", joinType: JoinType.LeftOuter) .Join <WarehouseInventory>("Id", "ProductVariantId", SourceColumn.Parent, joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <ProductVariant, ProductVariantAttribute>()) .Relate <ProductAttributeValue>((variant, value) => { var productVariantAttribute = variant.ProductVariantAttributes.FirstOrDefault(x => x.ProductAttributeValueId == value.Id); if (productVariantAttribute != null) { productVariantAttribute.ProductAttributeValue = value; } }) .Relate <ProductAttribute>((variant, attribute) => { var productVariantAttribute = variant.ProductVariantAttributes.FirstOrDefault(x => x.ProductAttributeId == attribute.Id); if (productVariantAttribute != null && productVariantAttribute.ProductAttribute == null) { productVariantAttribute.ProductAttribute = attribute; } }) .Relate <AvailableAttribute>((variant, attribute) => { var productVariantAttribute = variant.ProductVariantAttributes.FirstOrDefault( x => x.ProductAttribute.AvailableAttribute == null && x.ProductAttribute.AvailableAttributeId == attribute.Id); if (productVariantAttribute != null) { productVariantAttribute.ProductAttribute.AvailableAttribute = attribute; if (productVariantAttribute.ProductAttribute.Label.IsNullEmptyOrWhiteSpace()) { productVariantAttribute.ProductAttribute.Label = attribute.Name; } } }) .Relate <AvailableAttributeValue>((variant, attributeValue) => { var pav = variant.ProductVariantAttributes.Select(x => x.ProductAttributeValue) .FirstOrDefault(x => x.AvailableAttributeValueId == attributeValue.Id); if (pav != null) { if (pav.AvailableAttributeValue == null) { pav.AvailableAttributeValue = attributeValue; } if (pav.Label.IsNullEmptyOrWhiteSpace()) { pav.Label = attributeValue?.Value; } } }) .Relate(RelationTypes.OneToMany <ProductVariant, WarehouseInventory>())); }
private IEntitySet <DiscountCoupon> GetByWhere(Expression <Func <DiscountCoupon, bool> > where) { return(Repository.Where(where) .Join <RestrictionValue>("Id", "DiscountCouponId", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <DiscountCoupon, RestrictionValue>())); }
public IEnumerable <Order> GetOrders(out int totalResults, string productName = null, int?userId = null, int?storeId = null, IList <int> orderIds = null, IList <int> productIds = null, IList <OrderStatus> orderStatus = null, IList <PaymentStatus> paymentStatus = null, IList <int> vendorIds = null, DateTime?startDate = null, DateTime?endDate = null, int page = 1, int count = int.MaxValue) { var query = Repository; //check one by one for each filter if (userId.HasValue) { query = query.Where(x => x.UserId == userId); } if (storeId.HasValue) { query = query.Where(x => x.StoreId == storeId); } if (orderIds != null && orderIds.Any()) { query = query.Where(x => orderIds.Contains(x.Id)); } //date filters if (startDate.HasValue) { query = query.Where(x => x.CreatedOn >= startDate); } if (endDate.HasValue) { query = query.Where(x => x.CreatedOn <= endDate); } query = query.Join <OrderItem>("Id", "OrderId", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <Order, OrderItem>()); query = query.Join <Product>("ProductId", "Id", joinType: JoinType.LeftOuter) .Relate <Product>((order, product) => { foreach (var orderItem in order.OrderItems) { if (orderItem.ProductId == product.Id) { orderItem.Product = product; } } }); if (productIds?.Any() ?? false) { Expression <Func <OrderItem, bool> > whereProductIdMatches = (orderItem) => productIds.Contains(orderItem.ProductId); query = query.Where(whereProductIdMatches); } if (!productName.IsNullEmptyOrWhiteSpace() || (vendorIds?.Any() ?? false)) { if (vendorIds?.Any() ?? false) { Expression <Func <ProductVendor, bool> > whereVendorIdMatches = (vendorItem) => vendorIds.Contains(vendorItem.VendorId); query = query.Join <ProductVendor>("Id", "ProductId", joinType: JoinType.LeftOuter) .Where(whereVendorIdMatches); } if (!productName.IsNullEmptyOrWhiteSpace()) { Expression <Func <Product, bool> > whereProductName = product => product.Name.Contains(productName); query = query.Where(whereProductName); } } if (orderStatus != null && orderStatus.Any()) { query = query.Where(x => orderStatus.Contains(x.OrderStatus)); } if (paymentStatus != null && paymentStatus.Any()) { query = query.Where(x => paymentStatus.Contains(x.PaymentStatus)); } query = query.Join <User>("UserId", "Id", SourceColumn.Parent, JoinType.LeftOuter).Relate(RelationTypes.OneToOne <Order, User>()); ; query = query.OrderBy(x => x.CreatedOn, RowOrder.Descending); query.SkipColumns <Order>(nameof(Order.BillingAddressSerialized), nameof(Order.ShippingAddressSerialized), nameof(Order.Remarks)); query.SkipColumns <User>(nameof(User.Password), nameof(User.PasswordSalt), nameof(User.PasswordFormat)); var orders = query.SelectNestedWithTotalMatches(out totalResults, page, count); return(orders); }
public override Shipment Get(int id) { Expression <Func <SeoMeta, bool> > where = meta => meta.EntityName == nameof(Product); return(Repository.Where(x => x.Id == id) .Join <ShipmentItem>("Id", "ShipmentId", joinType: JoinType.LeftOuter) .Join <OrderItem>("OrderItemId", "Id", joinType: JoinType.LeftOuter) .Join <Order>("OrderId", "Id", joinType: JoinType.LeftOuter) .Join <ShipmentHistory>("Id", "ShipmentId", SourceColumn.Parent, JoinType.LeftOuter) .Join <User>("UserId", "Id", typeof(Order), JoinType.LeftOuter) .Join <Warehouse>("WarehouseId", "Id", SourceColumn.Parent, JoinType.LeftOuter) .Join <Address>("AddressId", "Id", joinType: JoinType.LeftOuter) .Join <Product>("ProductId", "Id", typeof(OrderItem), JoinType.LeftOuter) .Join <SeoMeta>("Id", "EntityId", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <Shipment, ShipmentItem>()) .Relate(RelationTypes.OneToMany <Shipment, ShipmentHistory>()) .Relate <OrderItem>((shipment, item) => { var shipmentItem = shipment.ShipmentItems.FirstOrDefault(x => x.OrderItemId == item.Id); if (shipmentItem != null) { shipmentItem.OrderItem = item; } }) .Relate <Order>((shipment, order) => { var shipmentItem = shipment.ShipmentItems.FirstOrDefault(x => x.OrderItem.OrderId == order.Id); if (shipmentItem == null) { return; } shipmentItem.OrderItem.Order = order; order.OrderItems = order.OrderItems ?? new List <OrderItem>(); if (!order.OrderItems.Contains(shipmentItem.OrderItem)) { order.OrderItems.Add(shipmentItem.OrderItem); } order.Shipments = order.Shipments ?? new List <Shipment>(); if (!order.Shipments.Contains(shipment)) { order.Shipments.Add(shipment); } }) .Relate <User>((shipment, user) => { shipment.User = user; }) .Relate(RelationTypes.OneToOne <Shipment, Warehouse>()) .Relate <Address>((shipment, address) => { shipment.Warehouse.Address = address; }) .Relate <Product>((shipment, product) => { foreach (var shipmentItem in shipment.ShipmentItems) { if (shipmentItem.OrderItem.ProductId == product.Id) { shipmentItem.OrderItem.Product = product; } } }) .Relate <SeoMeta>((shipment, meta) => { foreach (var shipmentItem in shipment.ShipmentItems) { if (shipmentItem.OrderItem.ProductId == meta.EntityId) { shipmentItem.OrderItem.Product.SeoMeta = meta; } } }) .SelectNested() .FirstOrDefault()); }
private Cart GetCart(int userId, bool isWishlist) { Expression <Func <SeoMeta, bool> > seoMetaWhere = meta => meta.EntityName == "Product"; Expression <Func <Product, bool> > productWhere = product => product.Published; Expression <Func <Address, bool> > addressWhere = address => address.EntityName == null || address.EntityName == nameof(User); var userCart = Repository.Where(x => x.UserId == userId && x.IsWishlist == isWishlist) .Join <CartItem>("Id", "CartId", joinType: JoinType.LeftOuter) .Join <Product>("ProductId", "Id", joinType: JoinType.LeftOuter) .Join <SeoMeta>("Id", "EntityId", joinType: JoinType.LeftOuter) .Join <ProductMedia>("EntityId", "ProductId", joinType: JoinType.LeftOuter) .Join <Media>("MediaId", "Id", joinType: JoinType.LeftOuter) .Join <DiscountCoupon>("DiscountCouponId", "Id", SourceColumn.Parent, JoinType.LeftOuter) .Join <RestrictionValue>("Id", "DiscountCouponId", joinType: JoinType.LeftOuter) .Join <User>("UserId", "Id", SourceColumn.Parent, JoinType.LeftOuter) .Join <UserRole>("Id", "UserId", joinType: JoinType.LeftOuter) .Join <Role>("RoleId", "Id", joinType: JoinType.LeftOuter) .Join <Address>("BillingAddressId", "Id", SourceColumn.Parent, JoinType.LeftOuter) .Join <Country>("CountryId", "Id", joinType: JoinType.LeftOuter) .Join <Address>("ShippingAddressId", "Id", SourceColumn.Parent, JoinType.LeftOuter) .Join <Country>("CountryId", "Id", joinType: JoinType.LeftOuter) .Relate(RelationTypes.OneToMany <Cart, CartItem>()) .Relate <Product>((cart, product) => { var cartItem = cart.CartItems.FirstOrDefault(x => x.ProductId == product.Id && x.Product == null); if (cartItem != null) { cartItem.Product = product; } }) .Relate <SeoMeta>((cart, meta) => { var cartItem = cart.CartItems.FirstOrDefault(x => x.ProductId == meta.EntityId); if (cartItem != null) { cartItem.Product.SeoMeta = meta; } }) .Relate <ProductMedia>((cart, media) => { var cartItem = cart.CartItems.FirstOrDefault(x => x.ProductId == media.ProductId); if (cartItem?.Product != null) { //temporary storage for media ids cartItem.Tag = cartItem.Tag ?? new List <int>(); cartItem.Product.MediaItems = cartItem.Product.MediaItems ?? new List <Media>(); (cartItem.Tag as List <int>).Add(media.MediaId); } }) .Relate <Media>((cart, media) => { var cartItem = cart.CartItems.FirstOrDefault( x => x.Tag != null && (x.Tag as List <int>).Contains(media.Id)); if (cartItem != null) { if (cartItem.Product.MediaItems.All(x => x.Id != media.Id)) { cartItem.Product.MediaItems.Add(media); } } }) .Relate(RelationTypes.OneToOne <Cart, DiscountCoupon>()) .Relate <RestrictionValue>((cart, value) => { cart.DiscountCoupon.RestrictionValues = cart.DiscountCoupon.RestrictionValues ?? new List <RestrictionValue>(); cart.DiscountCoupon.RestrictionValues.Add(value); }) .Relate(RelationTypes.OneToOne <Cart, User>()) .Relate <Role>((cart, role) => { cart.User.Roles = cart.User.Roles ?? new List <Role>(); cart.User.Roles.Add(role); }) .Relate <Address>((cart, address) => { if (cart.BillingAddressId == address.Id) { cart.BillingAddress = address; } if (cart.ShippingAddressId == address.Id) { cart.ShippingAddress = address; } }) .Relate <Country>((cart, country) => { if (cart.BillingAddress?.CountryId == country.Id) { cart.BillingAddress.Country = country; } if (cart.ShippingAddress != null && cart.ShippingAddress?.CountryId == country.Id) { cart.ShippingAddress.Country = country; } }) .Where(seoMetaWhere) .Where(productWhere) .Where(addressWhere) .SelectNested() .FirstOrDefault(); //do we have an empty cart userCart = userCart ?? Repository.Where(x => x.UserId == userId && x.IsWishlist == isWishlist).SelectSingle() ?? new Cart(); if (userCart.Id == 0) { //save a new cart for user userCart.UserId = userId; userCart.IsWishlist = isWishlist; Insert(userCart); } return(userCart); }
private IEntitySet <Order> GetByWhere(Expression <Func <Order, bool> > where) { Expression <Func <SeoMeta, bool> > meteWhere = x => x.EntityName == "Product"; return(Repository.Where(where) .Join <OrderItem>("Id", "OrderId", joinType: JoinType.LeftOuter) .Join <ShipmentItem>("Id", "OrderItemId", joinType: JoinType.LeftOuter) .Join <Shipment>("ShipmentId", "Id", joinType: JoinType.LeftOuter) .Join <User>("UserId", "Id", SourceColumn.Parent, JoinType.LeftOuter) .Join <Product>("ProductId", "Id", typeof(OrderItem)) .Join <ProductMedia>("Id", "ProductId", joinType: JoinType.LeftOuter) .Join <Media>("MediaId", "Id", joinType: JoinType.LeftOuter) .Join <SeoMeta>("Id", "EntityId", typeof(Product)) .Relate(RelationTypes.OneToMany <Order, OrderItem>()) .Relate(RelationTypes.OneToMany <Order, Shipment>()) .Relate(RelationTypes.OneToOne <Order, User>()) .Relate <ShipmentItem>((order, shipmentItem) => { var orderItem = order.OrderItems.First(x => x.Id == shipmentItem.OrderItemId); orderItem.Shipment = order.Shipments.First(x => x.Id == shipmentItem.ShipmentId); orderItem.Shipment.ShipmentItems = orderItem.Shipment.ShipmentItems ?? new List <ShipmentItem>(); if (!orderItem.Shipment.ShipmentItems.Contains(shipmentItem)) { orderItem.Shipment.ShipmentItems.Add(shipmentItem); shipmentItem.OrderItem = orderItem; } }) .Relate <Product>((order, product) => { foreach (var orderItem in order.OrderItems) { if (orderItem.ProductId == product.Id) { orderItem.Product = product; } } }) .Relate <ProductMedia>((order, media) => { foreach (var orderItem in order.OrderItems) { if (orderItem.ProductId == media.ProductId) { orderItem.Product.Tag = media.MediaId; } } }) .Relate <Media>((order, media) => { foreach (var orderItem in order.OrderItems) { if ((int)orderItem.Product.Tag == media.Id) { orderItem.Product.MediaItems = orderItem.Product.MediaItems ?? new List <Media>(); if (!orderItem.Product.MediaItems.Contains(media)) { orderItem.Product.MediaItems.Add(media); } } } }) .Relate <SeoMeta>((order, meta) => { var orderItem = order.OrderItems.FirstOrDefault(x => x.ProductId == meta.EntityId); if (orderItem != null) { orderItem.Product.SeoMeta = meta; } }) .Where(meteWhere)); }