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:


Petrochem Entities


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.

Petrochem DB


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 (9)

  1. Keith Farmer says:

    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.

  2. Nate Brixius says:

    Absolutely true – this example is not optimized for DB performance.  I probably should have made that more clear.

  3. Hendre Page says:

    Hi Nathan, thx for the great post.

    Just a quick question: To get better understanding of this I tried to recreate your solution exactly, except that I did it with a SQL Server Compact database file. I populated the database by typing in the data directly. Then, since the DBML designer does not support SQL Compact, I used sqlmetal to generate the code.

    When I run the program I get the following exception at the "Solution solution = context.Solve(new SimplexDirective())" line: Missing parameter value: yield(1,0)

    Any ideas?

  4. Nate Brixius says:

    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?

  5. Hendre Page says:

    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?

  6. Nate Brixius says:

    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.

  7. Rick Coetzee says:

    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?

  8. Erwin, a modeling consultant and top Solver Foundation user, encountered some problems trying to do two-way

Skip to main content