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.