Read Data from Files or Database

Populating your business data by hand in the source code only is feasible when experimenting with example models that are (relatively) limited in size. Once you want to work on real world problems, you need more efficient means of loading large amounts of data. We suggest using entity mappers, such as Entity Framework, CsvHelper, LinqToExcel, LinqToCsv, or similar. The idea is to use those tools for generating IEnumerables of your BusinessObjects, that can be (persisted and) used as IndexSet for your VariableCollection<T>. For specific details on how to achieve this, please refer to the documentation of the entity mapper of your choice.

Example: Using CsvHelper

The following example is taken from the Capacitated Lot-Sizing Problem introduction and shows the usage of the CsvHelper package. CsvHelper by far is the most popular csv-mapper available on NuGet, currently having more than 2,800,000 downloads.

First, we need to define a DTO (DataTransfer/Business Object) whose structure matches the structure of our CSV Source.

timesteps.csv:

Period;Demand;SetupCost;ProductionCost;InventoryCost;Capacity
1;0;90;10;10;20
...

PeriodInformation.cs:

using System;

namespace MIP_CLSP
{
    /// <summary>
    /// A time step of the Capacitated Lot-Sizing Model
    /// </summary>
    public class PeriodInformation
    {
        /// <summary>
        /// Gets the name of this time step
        /// </summary>
        public int Period { get; set; }

        /// <summary>
        /// Gets the demand in this time step
        /// </summary>
        public double Demand { get; set; }

        /// <summary>
        /// Gets the cost to setup the machine in this time step
        /// </summary>
        public double SetupCost { get; set; }

        /// <summary>
        /// Gets the cost to produce a single good in this time step
        /// </summary>
        public double ProductionCost { get; set; }

        /// <summary>
        /// Gets the inventory cost in this time step
        /// </summary>
        public double InventoryCost { get; set; }

        /// <summary>
        /// Production capacity in the time step
        /// </summary>
        public double Capacity { get; set; }

        /// <summary>
        /// Name of the time step
        /// </summary>
        /// <returns>
        /// The name of this time step (<see cref="string"/>).
        /// </returns>
        public override string ToString() => Convert.ToString(Period);

    }
}

Then, we simply can load the data from csv. The CsvHelper will take care of creating and populating your business objects. The result is an IEnumerable<PeriodInformation>, that contains one item per row in the csv file.

var csvReader = new CsvReader(File.OpenText("timesteps.csv"));
csvReader.Configuration.Delimiter = ";";
csvReader.Configuration.CultureInfo = new CultureInfo("en-US");
var periodInformation = csvReader.GetRecords<PeriodInformation>();

Before using the item source as IndexSet, make sure to evaluate and persist the IEnumerable. The following code will look the same when working with the EntityFramework, since the EF also returns the results of DB-queries as IEnumerable (or IQueryable, respectively).

// Execute + persist LINQ query
this.PeriodInformation = periodInformation.ToList();
// ... 

// Binary indicator variables
this.y = new VariableCollection<PeriodInformation>(
    this.Model,
    this.PeriodInformation,
    "y",
    timestep => $"machines_active_in_period_{timestep.Period}",
    timestep => 0,
    timestep => 1,
    VariableType.Binary);

That's it. With the created infrastructure you can easily load csv files that contain several hundred/thousand rows of data without having to change a single line of your source code.