score:0
i wrote a couple of classes to handle this optimization problem, however, it's not finished yet (i'm off from my PC now and will be back tmr - you may take a look at this first and see whether it's useful)...
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TestCSharp.Optimizer
{
public interface IUnit
{
int Id { get; set; }
float UnitPrice { get; set; }
}
public interface IOrder
{
int Quantity { get; set; }
float GetTotalPrice();
}
public interface IOrder<T> : IOrder where T : IUnit
{
T Unit { get; }
}
public abstract class AbstractUnit : IUnit
{
public int Id { get; set; }
public float UnitPrice { get; set; }
}
public class Item : AbstractUnit
{
public string Name { get; set; }
}
public class Package : AbstractUnit
{
private List<Order<Item>> _items = new List<Order<Item>>();
public IEnumerable<Order<Item>> Items
{
get { return this._items; }
}
}
public class Order<T> : IOrder<T> where T : IUnit
{
T _unit;
public Order(T unit)
{
_unit = unit;
}
public T Unit { get { return _unit; } }
public int Quantity { get; set; }
public float GetTotalPrice()
{
return _unit.UnitPrice * Quantity;
}
}
public class Combination
{
private List<IOrder> _orders = new List<IOrder>();
private Combination()
{
// Private constructor
}
public List<IOrder> Orders { get { return _orders; } }
public float GetTotalPrice()
{
if (_orders.Any())
{
return _orders.Select(m => m.GetTotalPrice()).Sum();
}
return 0;
}
public static Combination GetBestCombination(IEnumerable<Order<Item>> intendedItems, IEnumerable<Package> allPacksSetupFromDB)
{
var potentialCombinations = new List<Combination>();
// First comb: treat each item as a standalone IOrder:
var com = new Combination();
foreach (var ele in intendedItems)
{
com.Orders.Add(ele);
}
potentialCombinations.Add(com);
// check each possible pack (loaded from db) and find all possible combinations
var possiblePacks = allPacksSetupFromDB.Where(m => m.Items.All(n => (intendedItems.Any(t => t.Unit.Id == n.Unit.Id && t.Quantity >= n.Quantity)))).ToArray();
//ToDo: in the possible packages to use, find out all possible combinations
// This is the tricky part...
// The one with lowest price is desired!
return potentialCombinations.OrderBy(m => m.GetTotalPrice()).FirstOrDefault();
}
}
}
EDIT:
I thought you would have continued with this, anyway, i just came out an untested version:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TestCSharp.Optimizer
{
public interface IUnit
{
int Id { get; set; }
float UnitPrice { get; set; }
}
public interface IOrder
{
int Quantity { get; set; }
float GetTotalPrice();
}
public interface IOrder<T> : IOrder where T : IUnit
{
T Unit { get; }
}
public abstract class AbstractUnit : IUnit
{
public int Id { get; set; }
public float UnitPrice { get; set; }
}
public class Item : AbstractUnit
{
public string Name { get; set; }
}
public class Package : AbstractUnit
{
private List<Order<Item>> _items = new List<Order<Item>>();
// Items here are required by the package - number of items is specified by the quantity.
public IEnumerable<Order<Item>> Items
{
get { return this._items; }
}
}
public class Order<T> : IOrder<T> where T : IUnit
{
T _unit;
public Order(T unit)
{
_unit = unit;
}
public T Unit { get { return _unit; } }
public int Quantity { get; set; }
public float GetTotalPrice()
{
return _unit.UnitPrice * Quantity;
}
}
public class Combination
{
private List<IOrder> _orders = new List<IOrder>();
private Combination()
{
// Private constructor
}
public List<IOrder> Orders { get { return _orders; } }
/// <summary>
/// Get the total number of items ordered (combine package as well)
/// </summary>
/// <returns></returns>
public Dictionary<int, int> GetItemIdAndQuantity()
{
var output = new Dictionary<int, int>();
foreach (var order in this._orders)
{
if (order is Order<Item>)
{
var orderedItem = (Order<Item>)order;
if (!output.Keys.Contains(orderedItem.Unit.Id))
{
output.Add(orderedItem.Unit.Id, 0);
}
output[orderedItem.Unit.Id] = output[orderedItem.Unit.Id] + ((Order<Item>)order).Quantity;
}
else
{
var orderedPackage = (Order<Package>)order;
foreach (var item in orderedPackage.Unit.Items)
{
var itemId = item.Unit.Id;
if (!output.Keys.Contains(itemId))
{
output.Add(itemId, 0);
}
output[itemId] = output[itemId] + item.Quantity * orderedPackage.Quantity;
}
}
}
return output;
}
public float GetTotalPrice()
{
if (_orders.Any())
{
return _orders.Select(m => m.GetTotalPrice()).Sum();
}
return 0;
}
public static Combination GetBestCombination(IEnumerable<Order<Item>> intendedItems, IEnumerable<Package> allPacksSetupFromDB)
{
var potentialCombinations = new List<Combination>();
// check each possible pack (loaded from db) and find all possible combinations
// Step 1: for each package, if it could be used potentially, collect it into a list
var possibleRanges = new List<PackageUseRange>();
foreach (var p in allPacksSetupFromDB)
{
// for each required item in a package, intended items have to fulfill at least one occurrence (with given quantity)
if (p.Items.All(n => (intendedItems.Any(t => t.Unit.Id == n.Unit.Id && t.Quantity >= n.Quantity))))
{
var rng = new PackageUseRange(p);
rng.Min = 0;
// Find the possible max occurrence of the package
var matchedOrders = intendedItems.Where(x => p.Items.Any(m => m.Unit.Id == x.Unit.Id));
rng.Max = matchedOrders.Select(m => m.Quantity / p.Items.First(t => t.Unit.Id == m.Unit.Id).Quantity).Min();
possibleRanges.Add(rng);
}
}
// By now we should have something like:
// package 1: min 0, max 2
// package 2: min 0, max 1
// package 3: min 0, max 3
// ...
// Step 2: find all possible combinations:
if (possibleRanges.Any())
{
// define a collection to collect combinations
// also define a method to clear unwanted combinations.
var combinations = new List<Combination>();
Action invalidOrderRemover = delegate()
{
for (int j = combinations.Count - 1; j >= 0; j++)
{
var theCom = combinations[j];
var orderedQuantities = theCom.GetItemIdAndQuantity();
foreach (var itemId in orderedQuantities.Keys)
{
var intended = intendedItems.First(m => m.Unit.Id == itemId).Quantity;
if (orderedQuantities[itemId] > intended)
{
combinations.Remove(theCom);
break;
}
}
}
};
// For first package, let's create orders with different quantities
var firstPack = possibleRanges[0];
for (int i = firstPack.Min; i <= firstPack.Max; i++)
{
var order = new Order<Package>(firstPack.Package);
order.Quantity = i;
var com = new Combination();
com.Orders.Add(order);
combinations.Add(com);
}
// From second onwards:
// 1. we expand the orders created earlier and collect current pack (with different quantity)
// 2. we also take out those impossible combinations so far (total quantity exceeds wanted)
for (int i = 1; i < possibleRanges.Count - 1; i++)
{
invalidOrderRemover.Invoke(); // to avoid the list/array unreasonably large
// expand orders based on current pack's range:
var currPack = possibleRanges[i];
var expanded = new List<Combination>();
foreach (var oldCom in combinations)
{
for (int j = currPack.Min; j <= currPack.Max; j++)
{
// Clone from previous and pick up new ones from existing package.
var newCom = new Combination();
newCom.Orders.AddRange(oldCom.Orders);
var newOrder = new Order<Package>(currPack.Package);
newOrder.Quantity = j;
newCom.Orders.Add(newOrder);
}
}
// Clear old and add back the expanded:
combinations.Clear();
combinations.AddRange(expanded);
}
// Clear unwanted again:
invalidOrderRemover.Invoke();
// Add back balanced items:
foreach (var cb in combinations)
{
var fulfilled = cb.GetItemIdAndQuantity();
foreach (var item in intendedItems)
{
if (!fulfilled.Keys.Contains(item.Unit.Id))
{
// no such item in any package
// thus just add new Item based order
var newOrder = new Order<Item>(item.Unit);
newOrder.Quantity = item.Quantity;
cb.Orders.Add(newOrder);
}
else
{
// check balance:
if (fulfilled[item.Unit.Id] < item.Quantity)
{
var newOrder = new Order<Item>(item.Unit);
newOrder.Quantity = item.Quantity - fulfilled[item.Unit.Id];
cb.Orders.Add(newOrder);
}
}
}
}
// Add to the final combination collection
potentialCombinations.AddRange(combinations);
}
// Step 3: If there is no package used at all, treat each item as a standalone IOrder:
if (!potentialCombinations.Any())
{
var com = new Combination();
foreach (var ele in intendedItems)
{
com.Orders.Add(ele);
}
potentialCombinations.Add(com);
}
// The one with lowest price is desired!
return potentialCombinations.OrderBy(m => m.GetTotalPrice()).FirstOrDefault();
}
private class PackageUseRange
{
public PackageUseRange(Package p)
{
this.Package = p;
this.Min = 0;
this.Max = 0;
}
public Package Package { get; private set; }
public int Min { get; set; }
public int Max { get; set; }
}
}
}
The idea is quite simple: find all possible combinations and then get the one with lowest price.
To achieve that, you have to find all applicable packages, and then find the min (literally 0) and maximum possible.
From the min and max of each pack, find all combinations and then take out incorrect combinations (those exceeds the buying quantity).
hope it helps, though I'm not sure about the performance - it should be fast for non complex packages, but could be slow for complex situations. you may write some test cases for this...
score:-1
In sql just cuse i wanted to practice btw it works with 3 but if u have more then 3 items its a lot more complicated
declare @ck1 varchar (20),
@ck2 varchar (20),
@ck3 varchar (20),
@out varchar (100),
@flag bit,
@Itemck int
--if 1 then only @ck1 use, if 2 it used @ck1+@ck2 if 3 @ck1+@ck3 if 4 then @ck1+@ck2+@ck3
--usage manu
--1 @ck1
--2 @ck1+@ck2
--3 @ck1+@ck3
--4 @ck1+@ck2+@ck3
--5 @ck2+@ck3
set @ck1='Item1'
set @ck2='Item2'
set @ck3='Item2'
if @ck1='Item1'
BEGIN
if @ck2 = 'Item2' and @ck3='Item2'
begin
set @out='ItemPackge 3'
set @flag=1
set @Itemck=4
end
else
begin
if @ck2 = 'Item2'
begin
set @out='ItemPackge 1'
set @flag=1
set @Itemck=2
end
else if @ck3='Item2'
begin
set @out='ItemPackge 1'
set @flag=1
set @Itemck=3
end
else
begin
set @out='Item1'
set @flag=1
set @Itemck=1
end
end
END
else
if @ck1='Item2'
BEGIN
set @out='Item2'
set @flag=1
END
if @ck1='Item3'
BEGIN
if @ck2 = 'Item4' or @ck3='Item4'
if @flag=0
set @out='ItemPackge 2'
else
set @out=@out +' + ItemPackge 2'
else
if @flag=0
set @out='Item3'
else set @out=@out +' + Item 3'
set @flag=1
END
else
if @ck1='Item4'
BEGIN
if @flag=0 set @out='Item4'
else set @out=@out +' + Item 4'
set @flag=1
END
if @Itemck=0 or @Itemck=1 or @Itemck=3
BEGIN
if @ck2 ='Item1'
BEGIN
if @ck3='Item2'
BEGIN
if @flag=0
set @out='ItemPackge 2'
else
set @out=@out +' + ItemPackge 2'
set @Itemck=5
END
Else
BEGIN
if @flag=0
set @out='Item1'
else
set @out=@out +' + Item1'
END
end
else if @ck2='Item2'
BEGIN
if @flag=0
set @out='Item2'
else
set @out=@out +' + Item2'
END
if @ck2 ='Item3'
BEGIN
if @ck3='Item4'
BEGIN
if @flag=0
set @out='ItemPackge 2'
else
set @out=@out +' + ItemPackge 2'
set @Itemck=5
END
else
BEGIN
if @flag=0
set @out='Item3'
else
set @out=@out +' + Item3'
END
END
ELSE if @ck2='Item4'
BEGIN
if @flag=0
set @out='Item4'
else
set @out=@out +' + Item4'
END
END
if @Itemck =1 or @Itemck =2
BEGIN
if @flag=0
set @out=@ck3
else
set @out=@out +' + ' + @ck3
END
print @out
score:0
public string[] getItems(string [] Items)
{
List<string> lstReturn = new List<string>();
using(DBContext context = new DBContext())
{
lstReturn = (from c in context.ItemPackageItems
where Items.Contains(c.Item)
select c.ItemPackage).Distinct().ToList();
var PackagesItems = (from c in context.ItemPackageItems
where lstReturn.Contains(c.ItemPackage)
select c.Item).Distinct();
foreach (string strItem in Items)
{
if (!PackagesItems.Contains(strItem))
lstReturn.Add(strItem);
}
}
return lstReturn.ToArray();
}
Here your result order my be different, first are Packages and the single items
Source: stackoverflow.com
Related Query
- SQL query for product combination
- Determine the source DataContext for a Linq to Sql query
- Seeking for help for SQL server query to search multiple columns of a table using a combination of keywords
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet'
- Steps for a beginner to run very basic linq to sql query using Linqpad
- Dynamic linq query expression tree for sql IN clause using Entity framework
- Entity Framework generates inefficient SQL for paged query
- prevent unnecessary cross joins in count query of generated sql code
- Create Linq Expression for Sql Equivalent "column is null" in c# by creating linq query dynamically
- linq - how do you do a query for items in one query source that are not in another one?
- Is there any open source software for converting SQL statements to LINQ?
- Unable to convert SQL Query to LINQ Query for Left Outer Join
- Avoid extra loop and could not find implementation of query pattern for source type int Select not found
- LINQ to SQL generates a different query for similar group by expressions
- How to get SQL query into LINQ form in C# code
- Code Example for Add DateTime and TimeSpan in EF query
- Could not find an implementation of the query pattern for source type
- What would be a reasonably fast way to code this sql query in c#?
- Identify source of linq to sql query
- What the linq query for SQL like and Soudex for sql server 2008?
- How can I write a method to build a query both for SQL and an array?
- Can I avoid a nested SQL query for a simple Linq to Entities projection?
- Using LINQ query result for data source for GridControl c#
- Alternatives for generating sql server query from lambda expression
- Get to underlying SQL for Cosmos DB Query generated via LINQ
- SQL Query to LINQ for MVC with SUM and IS NOT NULL
- linq to sql query with checkboxes where condition combination
- convert linq to object query to sql query (no linq to sql code or datacontext)
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet` 'Where' not found
- How to see the SQL query for my lambda expression
More Query from same tag
- Local method in Linq without AsEnumerable()
- Looking into email Class with Linq
- Linq query giving inappropriate output
- How can I compare two sets of data in linq and return the common data?
- How to get all rows but specifc columns from a DataTable?
- What's the best way to compile a list of all Parent objects of a list of child objects?
- Dynamic Where filter through navigation Property
- Rewrite this foreach yield to a linq yield?
- Split list into sublist ID wise C#
- How to calculate sum of amount for each 5 weeks before in linq?
- How to get specific sequence of array given the array size and the beginning
- LINQ Subquery issue
- Do multiple DbContext generate a deadlock?
- LINQ Query - Selecting a key based upon the value having a property that matches a string?
- Linq .ForEach Optimization
- C# Xml to Linq remove an item
- How can I use LINQ to get a property matching a conditional sum?
- Fail to iterate through collection returned
- How to use web api to return a collection (array) of arrays of unlabeled values
- Executing stored procedures in parallel.foreach .net
- Filtering MySQL query only when the filter values are not empty-string, white-space or null best practice
- Optimizing A List of athlete's competitions with LINQ query or similar approach
- List<Guid> with LINQ
- Chain multiple WHERE clauses
- Get top n records with top m of relative table in Entity Framework
- C# Polymorphism with generic type constraints
- LINQ How to make a BETWEEN condition
- LINQ Query comprehensions - why is System.Linq namespace required?
- Initialize a Linq to Sql object via business logic
- Most efficient structure for quick access to a random element + the last element on one criteria