Two way data binding using LINQ and Solver Foundation Services

Recently on the Microsoft Solver Foundation discussion boards there was a question about two-way data binding and Solver Foundation Services.  The MSF-SFS Programming Primer has plenty of examples for helping you learn to use Solver Foundation Services in your .Net applications.  Sample 5 is about binding output values – user “detond” from our discussion boards extended the example to go against a SQL DB, and I have cleaned up the code.  A few notes:

    1. You’ll need to create a table named “ProductionCapacity” with nvarchar(max) Country, int MaxProduction, float Output columns.
    2. Create rows for Venezuela and SaudiArabia, with MaxProduction = 9000, 6000 to reproduce sample 5.
    3. The using() statement is necessary to prevent a SQL connection from leaking.
    4. db.SubmitChanges() is necessary to commit the changes back to the DB.  Otherwise only your in-memory copy is modified.
using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text;
using Microsoft.SolverFoundation.Services;

namespace OutputBindings {

  [Table(Name = "ProductionCapacity")]
  public class Production {
    [Column(IsPrimaryKey = true)]
    public string Country;

    private int _MaxProduction;

    [Column(Storage = "_MaxProduction")]
    public int MaxProduction {
      get { return this._MaxProduction; }
      set { this._MaxProduction = value; }
    }

    private double _Output;

    [Column(Storage = "_Output")]
    public double Output {
      get { return this._Output; }
      set { this._Output = value; }
    }
  }

  class Program {
    static void Main(string[] args) {

      Decision vz, sa;
      Solution solution = null;
      using (DataContext db = new DataContext(@"Data Source=xxx\yyy; Initial Catalog=MSF; Integrated Security=True")) {
        db.Log = Console.Out; // Let's see what's happening.

        // Get a typed table to run queries
        Table<Production> Productions = db.GetTable<Production>();

        // Get the context and creating a new model.
        SolverContext context = SolverContext.GetContext();
        Model model = context.CreateModel();

        // Create two decision variables representing the number of barrels to
        // purchase from two countries.
        // AddDecisions tells the model about the two variables.
        vz = new Decision(Domain.RealNonnegative, "barrels_venezuela");
        sa = new Decision(Domain.RealNonnegative, "barrels_saudiarabia");

        vz.SetBinding(from row in Productions where row.Country == "Venezuela"
           select row, "Output");
        sa.SetBinding(from row in Productions where row.Country == "SaudiArabia"
           select row, "Output");
        model.AddDecisions(vz, sa);

        Parameter maxvz = new Parameter(Domain.RealNonnegative, 
           "maxproduction_venezuela");
        Parameter maxsa = new Parameter(Domain.RealNonnegative,   
           "maxproduction_saudiarabia");

        // To get the same results as in the sample, set MaxProduction=9000 for  
        // Venezuela, MaxProduction=6000 for SaudiArabia in your DB table.
        maxvz.SetBinding(from row in Productions where row.Country == "Venezuela" 
            select row, "MaxProduction");
        maxsa.SetBinding(from row in Productions where row.Country == "SaudiArabia" 
            select row, "MaxProduction");
        model.AddParameters(maxvz, maxsa);

        // Adding five contraints. The first line is two contraints giving the
        // allowable range for the two decision varibles. The other contraints put
        // minimums on the total yield of three products.
        model.AddConstraints("limits",
        0 <= vz <= maxvz,
        0 <= sa <= maxsa);
        model.AddConstraints("production",
        0.3 * sa + 0.4 * vz >= 2000,
        0.4 * sa + 0.2 * vz >= 1500,
        0.2 * sa + 0.3 * vz >= 500);

        model.AddGoal("cost", GoalKind.Minimize, 20 * sa + 15 * vz);
        solution = context.Solve(new SimplexDirective());

        context.PropagateDecisions(); // Propagate values to the in-memory rep.
        db.SubmitChanges(); // Commit to DB.
      }

      if (solution != null) {
        Report report = solution.GetReport();
        Console.WriteLine("vz: {0}, sa: {1}", vz, sa);
        Console.Write("{0}", report);
      }
    }
  }
}

Author: natebrix

Follow me on twitter at @natebrix.

2 thoughts on “Two way data binding using LINQ and Solver Foundation Services”

  1. Hi Nathan, I am wondering whether there is a way to make the above codes a stored procedure that can be called from SQL server? My problems is I would like to create a stored procedure using linq and solver foundation so that the stored procedure can be called in SQL sever using T-SQL ( exec storeprocedurename “parameter1”, “parameter2” )? Thanks! — Luna

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s