Creating parameterized Solver Foundation models using LINQ to SQL

On the Solver Foundation MSDN forum there was a question about how to read model data from a DB and use it within a Solver Foundation model.  In this post I will extend my production planning sample to use LINQ to SQL.  To follow along at home you will need to have a recent version of SQL Server installed locally, and some basic knowledge of how to create SQL tables.  You should also have compiled and run the code from my previous post.

Step 1: Create and populate the DB 

The first step is to create tables corresponding to the entities in my model.  I created a very simple DB with three tables: Countries, Products, and Yields.  The Yields table has foreign key constraints to the Countries and Products tables.  Here is a diagram:

To populate the DB I just wrote a script that inserts my problem data, and ran itin SQL Management Studio.  Here's the script (and forgive my SQL):

 GO 

DELETE FROM Yields
DELETE FROM Products
DELETE FROM Countries
GO

INSERT INTO Countries (Id, Name, Limit, Cost)
VALUES (0, 'SA', 9000, 20)

INSERT INTO Countries (Id, Name, Limit, Cost)
VALUES (1, 'VZ', 6000, 15)

GO

INSERT INTO Products (Id, Name, Demand)
VALUES (0, 'Gas', 1900)

INSERT INTO Products (Id, Name, Demand)
VALUES (1, 'Jet Fuel', 1500)

INSERT INTO Products (Id, Name, Demand)
VALUES (2, 'Lubricant', 500)

GO

INSERT INTO Yields (CountryId, ProductId, Value)
VALUES (0, 0, 0.3)
INSERT INTO Yields (CountryId, ProductId, Value)
VALUES (1, 0, 0.4)

INSERT INTO Yields (CountryId, ProductId, Value)
VALUES (0, 1, 0.4)
INSERT INTO Yields (CountryId, ProductId, Value)
VALUES (1, 1, 0.2)

INSERT INTO Yields (CountryId, ProductId, Value)
VALUES (0, 2, 0.2)
INSERT INTO Yields (CountryId, ProductId, Value)
VALUES (1, 2, 0.3)

 

Step 2: Create Entity and DataContext classes in Visual Studio

Scott Guthrie's blog (and the MSDN docs) show you exactly how to do this: 

1. Add a new "Linq to SQL file" to your project called Petrochem.dbml.
2. Bring up the Server Explorer window, connect to your database and drag the tables into the dbml window.  Visual Studio will automatically create a Datacontext class (mine is called PetrochemDataContext) and an entity class for each table that you include.

Step 3: Modify Solver Foundation Services data binding code

This is in fact very easy because Solver Foundation Services was designed to work well with LINQ.  Take the PetrochemDataBinding sample from last time, and change the SetBinding statements to work with the PetrochemDataContext class instead of a hardcoded DataSet.  The code is almost identical:

   private static void PetrochemLinqDataBinding() {
      SolverContext context = SolverContext.GetContext();
      context.ClearModel();
      Model model = context.CreateModel();

      PetrochemDataContext db = new PetrochemDataContext();

      Set products = new Set(Domain.Any, "products");
      Set countries = new Set(Domain.Any, "countries");

      Parameter demand = new Parameter(Domain.Real, "demand", products);
      demand.SetBinding(db.Products, "Demand", "Id");

      Parameter yield = new Parameter(Domain.Real, "yield", products, countries);
      yield.SetBinding(db.Yields, "Value", "ProductId", "CountryId");

      Parameter limit = new Parameter(Domain.Real, "limit", countries);
      limit.SetBinding(db.Countries, "Limit", "Id");

      Parameter cost = new Parameter(Domain.Real, "cost", countries);
      cost.SetBinding(db.Countries, "Cost", "Id");

      model.AddParameters(demand, yield, limit, cost);

      Decision produce = new Decision(Domain.RealNonnegative, "produce", countries);
      model.AddDecision(produce);

      model.AddGoal("goal", GoalKind.Minimize, Model.Sum(Model.ForEach(countries, c => cost[c] * produce[c])));

      model.AddConstraint("Demand",
        Model.ForEach(products, p => Model.Sum(Model.ForEach(countries, c => yield[p, c] * produce[c])) >= demand[p])
        );

      model.AddConstraint("Production limit",
        Model.ForEach(countries, c => produce[c] <= limit[c])
        );

      Solution solution = context.Solve(new SimplexDirective());
      Report report = solution.GetReport();
      Console.WriteLine(report);
    }

That's all there is to it!  Note that instead of passing the entire collection (e.g. db.Countries) you could easily use LINQ statements or stored procedures, or whatever you like.

Comments

• Anonymous
  May 05, 2009
  PingBack from http://microsoft-sharepoint.simplynetdev.com/creating-parameterized-solver-foundation-models-using-linq-to-sql/

• Anonymous
  May 06, 2009
  Neat, though I'd hate to know how many round trips it entailed.  Try setting db.Log = Console.Out and seeing whether and how many redundant queries there are.

• Anonymous
  May 06, 2009
  Absolutely true - this example is not optimized for DB performance.  I probably should have made that more clear.

• Anonymous
  May 09, 2009
  The comment has been removed

• Anonymous
  May 09, 2009
  Hi Hendre, unfortunately I am not familiar with SQL Compact.  But the error does seem a bit fishy - if you look at my SQL code it is clear that the (1,0) value is being inserted into the table.  Maybe you want to view the Yields table to verify that all six rows are there?

• Anonymous
  May 09, 2009
  Stupid me!! Thats what happens when you work till early in the morning! Thanks for the help, I left the (1,0) value out..... The program runs now, but I get an infeasible solution: ===Solver Foundation Service Report=== Datetime: 05/10/2009 10:39:00 Model Name: Default Capabilities requested: LP Solve Time (ms): 420 Total Time (ms): 855 Solve Completion Status: Infeasible Solver Selected: Microsoft.SolverFoundation.Solvers.SimplexSolver Directives: Simplex(TimeLimit = -1, MaximumGoalCount = -1, Arithmetic = Default, Pricing = Default, IterationLimit = -1, Algorithm = Default, Basis = Default, GetSensitivity = False) Algorithm: Primal Arithmetic: Double Pricing (double): SteepestEdge Basis: Slack Pivot Count: 4 Why is this?

• Anonymous
  May 10, 2009
  I got an optimal solution with my data, so I would check your input for typos.  Perhaps if you printed out the values of your demands, costs, yields then you can verify that they have the expected values.

• Anonymous
  May 11, 2009
  Hi, thanks for this great article. Could you just explain the problem you are solving in words? I know it is a problem to minimize the costs of the products that come from the refineries etc. But the Yield value is not clearly explained?

• Anonymous
  June 02, 2009
  Erwin, a modeling consultant and top Solver Foundation user, encountered some problems trying to do two-way