ML.NET Predictions with In-Memory Data, CSV Files and SQL Server
In this article, we will see the ML.NET with the prediction implementation with In-Memory Data, CSV file data, and SQL Server.
We will create one solution that has four projects. The first one, mllib, is a class library with a reusable class named DataTrainer. It takes the data, prepares the columns and trains a model. The other three projects are console applications and all of them use DataTrainer. The only thing that changes from one project to another is the place where the data comes from.
- mllib: class library that has the DataTrainer class.
- mlnetdemo: data is kept in memory in a C# list. It predicts the price of a house from its size.
- loadtextfiledemo: data is read from a CSV file named orders.csv. It predicts whether an order will be returned.
- mlnet_sql: data is read from the Sales.SalesOrderHeader table of the AdventureWorks2025 database in SQL Server. It predicts whether an order was placed online or through a reseller.
What is ML.NET?
ML.NET is an open source and cross platform machine learning framework from Microsoft for .NET developers. With ML.NET we can load data, prepare it, train a model, check how good the model is and use it for predictions, all in C#. We do not need to learn Python and we do not need to move our data to some other system.
Let us first understand what machine learning means in simple words. Normally, when we write a program, we write the rules ourselves, for example if total is greater than 5000 then the order is a reseller order. In machine learning we do it the other way round. We give the algorithm a lot of examples where the answer is already known, and the algorithm finds the pattern by itself. The result of this learning is called a model. After that, we give the model new data and it gives us the answer.
ML.NET can be used for many types of problems, for example:
- Regression: predict a number, for example the price of a house. We use this in mlnetdemo.
- Binary classification: predict a yes or no answer, for example will this order be returned. We use this in loadtextfiledemo and mlnet_sql.
- Multiclass classification: predict one category out of many.
- Clustering, ranking, recommendation, anomaly detection and forecasting.
Why use ML.NET?
Most business applications written by .NET teams already have their data in SQL Server and their code in C#. If we want to add a prediction to such an application, one way is to build a separate service in another language, host it somewhere and call it from our application. That means one more technology, one more deployment and one more network call for every prediction.
With ML.NET the model training and the prediction are part of the same solution. We use the same language, the same Visual Studio, the same NuGet, the same debugger and the same build and deployment that we already use. A prediction is just a method call inside our application, and we do not have to send the data to another system for training or for scoring.
Advantages of using ML.NET
- We write everything in C# (or F#) with the tools we already know, like Visual Studio, NuGet, unit tests and the debugger.
- It runs inside our application process. There is no separate server to install and a prediction is a local method call.
- It is cross platform. It runs on Windows, Linux and macOS and it can be used in console, web, desktop, worker service and Azure Functions applications.
- It has algorithms and data preparation steps for the common scenarios like regression, classification, clustering, recommendation, forecasting and anomaly detection.
- Data is read through
IDataView, which reads the rows lazily, so it can work with data sets bigger than what we want to keep in memory. - We can use models trained somewhere else, for example ONNX models, with the help of additional packages.
- It has AutoML, Model Builder in Visual Studio and a command line tool, which help us to try different algorithms quickly.
- It works with dependency injection in ASP.NET Core through the Microsoft.Extensions.ML package.
ML.NET is not the answer for everything. For very large deep learning training, other tools are still more common. But a lot of day to day business predictions, like the ones in this article, do not need that, and ML.NET does the job well.
ML.NET NuGet packages used in the solution
The mlnetdemo and loadtextfiledemo projects have the following packages in their project file.
<PackageReference Include="Microsoft.Extensions.ML" Version="5.0.1" />
<PackageReference Include="Microsoft.ML" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.AutoML" Version="0.23.1" />
<PackageReference Include="Microsoft.ML.FastTree" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.LightGbm" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.Recommender" Version="0.23.1" />
<PackageReference Include="Microsoft.ML.TimeSeries" Version="5.0.1" />Let us see what each of them gives us.
Microsoft.ML is the core package and the one we cannot work without. It has the MLContext class, the data loading classes (text file loader, database loader, in-memory loader), the data preparation steps like one-hot encoding, normalization and concatenation, the standard trainers like SDCA, and the classes to evaluate, save and load a model. All the code in this article needs this package.
Microsoft.Extensions.ML is used when we want to use a model in an ASP.NET Core or a worker service application. A prediction engine is not thread safe, and in a web application many requests come at the same time. This package gives us PredictionEnginePool, which works with dependency injection and can load the model from a file or a URL and reload it when the model changes.
Microsoft.ML.AutoML tries different algorithms and different settings on our data and tells us which one works best. It is useful when we are not sure which trainer to use.
Microsoft.ML.FastTree has the tree based trainers, like FastTree and FastForest. They are decision tree and boosted tree algorithms, which can be used for regression, classification and ranking.
Microsoft.ML.LightGbm has the trainers that use LightGBM, which is a popular gradient boosting library. It is often a good choice for table like data.
Microsoft.ML.Recommender has the matrix factorization trainer which is used for recommendations, for example to suggest products to a user based on the ratings given by other users.
Microsoft.ML.TimeSeries has the classes for time series work, like forecasting the future values and detecting anomalies such as a sudden spike or a change point in the data.
One thing to be clear about. The code in this article uses only the Microsoft.ML package (and Microsoft.Data.SqlClient in the SQL project). The other six packages are added in the two projects so that we can try those features later, and if you want a lighter project you can remove them without changing any code in this article.
The ML.NET workflow
Every ML.NET application follows the same flow. We start with the data, which can be a CSV file, a database table, C# objects or files. The data is exposed as an IDataView. Then we apply transforms to clean the data and to convert the columns into numbers. After that a trainer, which is the learning algorithm, learns from the data. The result is a model, which we can use inside the application to make predictions.
The same six steps are used in all three console projects. Only the first two steps change, because that is where we load the data. You can see the flow of each project in the diagrams given in the respective sections below.
Important ML.NET classes
Before writing the code, let us understand the classes that we will use again and again.
MLContext
MLContext is the starting point of every ML.NET application. It is the first object we create, and everything else is reached through it. It also keeps the random seed, so that we can get repeatable results.
var mlContext = new MLContext(seed: 0);It works like a factory. It has a property for each area of the framework. The ones that we use in this article are:
Data: loading data, converting objects to an IDataView, splitting data into training and test sets.Transforms: data preparation steps likeConcatenate,Categorical.OneHotEncoding,ConversionandNormalizeMeanVariance.Regression,BinaryClassificationandMulticlassClassification: the trainers and the evaluation methods for each type of problem. There are alsoClustering,Ranking,AnomalyDetectionandForecasting.Model: saving and loading a model and creating a prediction engine.
IDataView
IDataView is how ML.NET represents data. We can think of it as a table with named and typed columns. The important point is that it is lazy. When we create an IDataView from a file or a database, the rows are not read at that time. They are read only when something needs them, for example when the model is trained. That is why ML.NET can work with big data.
The main members are the Schema property, which describes the columns, and the GetRowCursor() method, which is used to read the rows. We normally do not call the cursor ourselves. For a quick look at the data we use the Preview() method, which gives us the first rows (100 rows by default) as a DataDebuggerPreview object, with the ColumnView and RowView properties.
DatabaseLoader
DatabaseLoader reads the data from a relational database. We create it from our C# class with mlContext.Data.CreateDatabaseLoader<T>(). Then we tell it where to read from with a DatabaseSource object, which has three important properties: ProviderFactory (which database provider to use, for SQL Server it is SqlClientFactory.Instance), ConnectionString and CommandText (the SQL query). The Load() method of the loader returns an IDataView. We will use this in the mlnet_sql project.
ITransformer
ITransformer is the trained model. When we call Fit() on a pipeline, ML.NET runs all the steps on the training data and gives us back an ITransformer. It remembers everything it learned, so when we give it new data, it applies the same preparation steps and then makes the prediction. The main method is Transform(), which takes an IDataView and returns a new IDataView that has the extra prediction columns (for example Score, or PredictedLabel and Probability). It also has GetOutputSchema() and the IsRowToRowMapper property.
IEstimator and the pipeline
The steps of the pipeline are called estimators (IEstimator<ITransformer>). Each estimator is a step that is ready to learn but has not learned anything yet. We join them with the Append() method. Nothing runs when we append. Only when we call Fit() on the whole chain, the data is read and the training happens. In DataTrainer, we use the EstimatorChain class to start with an empty chain and add the steps one by one.
ModelOperationsCatalog
The mlContext.Model property gives us a ModelOperationsCatalog object. It has the methods to work with a trained model: CreatePredictionEngine(), Save(), Load() and LoadWithDataLoader(). Saving a model creates a .zip file, which we can load later in another application, so that we do not have to train again.
CreatePredictionEngine()
CreatePredictionEngine<TSrc, TDst>(model) creates a PredictionEngine from the trained model. TSrc is the class of the input and TDst is the class for the output. The main method of the engine is Predict(), which takes one input object and gives one output object. It is simple to use and good for single predictions in a console or desktop application.
var engine = mlContext.Model.CreatePredictionEngine<HouseData, PricePrediction>(model);
var prediction = engine.Predict(new HouseData() { Size = 3.0F });Remember that a PredictionEngine is not thread safe. In a web application use PredictionEnginePool from the Microsoft.Extensions.ML package.
Creating the solution
You need Visual Studio with .NET 10 support, and for the last project a SQL Server instance that has the AdventureWorks2025 sample database. Create a blank solution named MLNET. We will add the four projects to this solution one by one.
Step 1: Create a Class Library project and name it mllib
In the MLNET solution, add a new project of the type Class Library, name it mllib and select .NET 10 as the framework. This project will hold the DataTrainer class, which is shared by the other projects.
Step 2: Add the following package in the mllib project
Right click on the project, select Manage NuGet Packages and install Microsoft.ML version 5.0.1. You can also use the command line.
dotnet add package Microsoft.ML --version 5.0.1The project file of mllib will look like this.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.ML" Version="5.0.1" />
</ItemGroup>
</Project>This single package brings the following ML.NET assemblies in the bin folder of the project.
- Microsoft.ML.Core.dll: the basic interfaces, like
ITransformerandIEstimator. - Microsoft.ML.DataView.dll:
IDataView,DataViewSchemaand the column types. - Microsoft.ML.Data.dll:
MLContext, the data loaders (includingDatabaseLoader), thePreview()method, the catalogs and the evaluation metrics. - Microsoft.ML.Transforms.dll: the data preparation steps, like one-hot encoding, normalization, concatenation and type conversion.
- Microsoft.ML.StandardTrainers.dll: the standard trainers, like SDCA.
- Microsoft.ML.CpuMath.dll: fast math for vectors that the trainers use internally.
- Microsoft.ML.KMeansClustering.dll and Microsoft.ML.PCA.dll: the clustering and the principal component analysis algorithms. We do not use them directly.
Step 3: Rename Class1.cs to DataTrainer.cs and write the following code
using Microsoft.ML;
using Microsoft.ML.Data;
using System.Text.Json;
namespace mllib
{
/// <summary>
/// Trains an ML.NET model from an in-memory sequence of objects. The feature columns are prepared
/// automatically (text is one-hot encoded, other numeric/bool types are converted to Single) and the
/// trainer is chosen from the type of the label column.
/// </summary>
public class DataTrainer
{
/// <summary>
/// Trains a model and returns the fitted transformer pipeline.
/// </summary>
/// <remarks>
/// Trainer selection by label column type:
/// <list type="bullet">
/// <item><description>Boolean: binary classification (SDCA logistic regression). Predict with a class property mapped to <c>PredictedLabel</c> (bool), plus optional <c>Probability</c> / <c>Score</c>.</description></item>
/// <item><description>Numeric (Single, Double, Int32, ...): regression (SDCA). Predict with a float property mapped to <c>Score</c>.</description></item>
/// <item><description>Text: multiclass classification (SDCA maximum entropy). Predict with a string property mapped to <c>PredictedLabel</c>.</description></item>
/// </list>
/// Feature columns of type string are one-hot encoded before being concatenated into <c>Features</c>.
/// </remarks>
/// <typeparam name="TData">The input data type mapped to the ML.NET schema.</typeparam>
/// <param name="mlc">The MLContext to use.</param>
/// <param name="database">The data records used to build the training data view.</param>
/// <param name="outputColumnName">The label column (the value to predict).</param>
/// <param name="featureColumnNames">The source columns to use as features.</param>
/// <returns>An <see cref="ITransformer"/> representing the trained pipeline.</returns>
public ITransformer Train<TData>(MLContext mlc, IEnumerable<TData> database, string outputColumnName, params string[] featureColumnNames) where TData : class
{
// Load the in-memory objects as an IDataView. ML.NET builds the schema from the public
// properties of TData (float -> Single, string -> Text, bool -> Boolean, and so on).
IDataView data = mlc.Data.LoadFromEnumerable<TData>(database);
// Print a small sample of the data and the schema. This is only for diagnostics and
// helps to confirm that the column types are what we expect before training.
var vrdata = data.Preview(maxRows: 5);
Console.WriteLine($"Data (first {vrdata.RowView.Length} rows): {JsonSerializer.Serialize(vrdata.RowView)}");
foreach (var column in data.Schema)
{
Console.WriteLine($"Column Name: {column.Name}, Column Type: {column.Type}");
}
// Find the label column. Fail early with a clear message if the name is wrong.
var labelColumn = data.Schema.GetColumnOrNull(outputColumnName)
?? throw new ArgumentException($"Label column '{outputColumnName}' was not found in the data.", nameof(outputColumnName));
// Concatenate requires every input column to have the same type (Single), so each
// feature is prepared first. The pipeline starts empty and grows one step at a time.
IEstimator<ITransformer> pipeline = new EstimatorChain<ITransformer>();
// The names of the columns that will actually be concatenated into "Features"
// (original column names, or the converted/encoded replacements).
var finalFeatureColumns = new List<string>();
foreach (var name in featureColumnNames)
{
// Find the feature column. Fail early with a clear message if the name is wrong.
var col = data.Schema.GetColumnOrNull(name)
?? throw new ArgumentException($"Feature column '{name}' was not found in the data.", nameof(featureColumnNames));
if (col.Type is TextDataViewType)
{
// Text (for example Payment) cannot be used as a number. One-hot encoding turns each
// distinct value into its own 0/1 slot in a Single vector.
var encoded = name + "Encoded";
pipeline = pipeline.Append(mlc.Transforms.Categorical.OneHotEncoding(encoded, name));
finalFeatureColumns.Add(encoded);
}
else if (col.Type is NumberDataViewType nt && nt.RawType == typeof(float))
{
// Already a Single, so no conversion is needed.
finalFeatureColumns.Add(name);
}
else if (col.Type is NumberDataViewType || col.Type is BooleanDataViewType)
{
// Other numeric types (int, double, ...) and bool are converted to Single.
var converted = name + "Single";
pipeline = pipeline.Append(mlc.Transforms.Conversion.ConvertType(converted, name, DataKind.Single));
finalFeatureColumns.Add(converted);
}
else
{
// Already a vector column (e.g. float[]) or another supported type: pass through.
finalFeatureColumns.Add(name);
}
}
// Combine all prepared feature columns into the single "Features" vector that trainers read.
pipeline = pipeline.Append(mlc.Transforms.Concatenate("Features", finalFeatureColumns.ToArray()));
// Standardize every feature (subtract the mean, divide by the standard deviation). The SDCA
// trainers learn badly when one feature is huge (for example an order total of 100000) next
// to others that are 0 or 1, because the large values dominate and training does not converge.
// Standardizing keeps all features on a similar scale without squeezing them into a tiny
// range, which also keeps regression on small data sets accurate. Scaling is part of the
// pipeline, so the same scaling is applied automatically when the model makes predictions.
pipeline = pipeline.Append(mlc.Transforms.NormalizeMeanVariance("Features"));
// Pick the trainer from the label type: bool -> binary classification, text -> multiclass,
// numeric -> regression.
var labelType = labelColumn.Type;
if (labelType is BooleanDataViewType)
{
// Binary classification (true/false label, e.g. Returned). Adds the PredictedLabel,
// Probability and Score output columns.
pipeline = pipeline.Append(mlc.BinaryClassification.Trainers.SdcaLogisticRegression(
labelColumnName: outputColumnName,
featureColumnName: "Features",
maximumNumberOfIterations: 100));
}
else if (labelType is TextDataViewType)
{
// Multiclass classification (text label). The multiclass trainer needs a key-typed label,
// so the text is mapped to keys first, and PredictedLabel is mapped back to text after training.
const string keyLabel = "LabelKey";
pipeline = pipeline
.Append(mlc.Transforms.Conversion.MapValueToKey(keyLabel, outputColumnName))
.Append(mlc.MulticlassClassification.Trainers.SdcaMaximumEntropy(
labelColumnName: keyLabel,
featureColumnName: "Features",
maximumNumberOfIterations: 100))
.Append(mlc.Transforms.Conversion.MapKeyToValue("PredictedLabel"));
}
else if (labelType is NumberDataViewType nl && nl.RawType == typeof(float))
{
// Regression (Single label, e.g. Price). Adds the Score output column.
pipeline = pipeline.Append(mlc.Regression.Trainers.Sdca(
labelColumnName: outputColumnName,
featureColumnName: "Features",
maximumNumberOfIterations: 100));
}
else if (labelType is NumberDataViewType)
{
// Regression (int/double label). The regression trainer needs a Single label,
// so the label is converted to a new column first.
const string floatLabel = "LabelSingle";
pipeline = pipeline
.Append(mlc.Transforms.Conversion.ConvertType(floatLabel, outputColumnName, DataKind.Single))
.Append(mlc.Regression.Trainers.Sdca(
labelColumnName: floatLabel,
featureColumnName: "Features",
maximumNumberOfIterations: 100));
}
else
{
// Any other label type (vector, date, and so on) has no matching trainer here.
throw new NotSupportedException($"Label column '{outputColumnName}' has unsupported type {labelType}.");
}
Console.WriteLine($"Feature Columns {string.Join(", ", featureColumnNames)}");
// Fit runs every step (encoding, concatenation, training) on the data and returns the
// trained model. The same model applies the same prepared steps when it makes predictions.
return pipeline.Fit(data);
}
}
}Let us understand this code step by step.
The method signature. Train<TData> is a generic method, so it works with any class. We pass the MLContext, a list of objects (database), the name of the column that we want to predict (outputColumnName, which is called the label) and the names of the columns that we want to use to make the prediction (featureColumnNames, called the features). The params keyword lets us pass the feature names one after the other. It returns an ITransformer, which is the trained model.
Loading and printing the data. LoadFromEnumerable converts our C# objects to an IDataView. ML.NET looks at the public properties of the class and creates the columns from them, so a float property becomes a Single column, a string becomes a Text column and a bool becomes a Boolean column. Then we print the first five rows and the schema. This is only for checking that the column types are what we expect.
Finding the label. GetColumnOrNull searches the schema by name. If the name is wrong we throw an ArgumentException with a clear message, instead of getting a confusing error later.
Preparing the features. This is the most important part. The trainers understand only numbers, and all the columns that we join into one Features column must have the same type. If we pass a text column such as Payment together with a float column such as Total to Concatenate, we get this error:
Concatenated columns should have the same type. Column 'Payment' has type of String, but expected column type is Single.To avoid this, the loop checks the type of each feature column. If it is text, we apply one-hot encoding. It creates one slot for each different value, so credit_card becomes something like [1, 0, 0, 0, 0] and cash becomes [0, 1, 0, 0, 0]. This is better than giving numbers like 1 and 2 to the payment types, because then the model would think one payment type is bigger than another. If the column is already a float we use it as it is. If it is another number type (int, double) or a bool, we convert it to a float with ConvertType. Anything else, for example a vector, is passed through.
Concatenate and normalize. Concatenate joins all the prepared columns into one column named Features, which is the column that the trainers read. After that, NormalizeMeanVariance standardizes every feature, which means it subtracts the average and divides by the standard deviation. This is needed because the trainer that we use (SDCA) does not learn well when one feature has very big values, like an order total of 100000, next to features that are 0 or 1. The first time I ran the SQL project without this step, the accuracy was the same as a model that always gives the same answer. After adding it, the accuracy went up to about 96 percent. The normalization is part of the pipeline, so it is applied automatically at the time of prediction also.
Choosing the trainer. The type of the label column decides which type of problem we have, so we select the trainer from it.
- A bool label means yes or no, so we use binary classification with
SdcaLogisticRegression. Examples are Returned and IsOnline. - A float label is a number, so we use regression with
Sdca. An example is Price. If the label is an int or a double, we convert it to a float first, because the regression trainer needs a float. - A text label means categories, so we use multiclass classification with
SdcaMaximumEntropy. This trainer needs the label as a key, so we convert the text to a key withMapValueToKeyand convert the answer back to text withMapKeyToValue. None of the projects in this article use this one, but it makes DataTrainer usable for other data also.
SDCA stands for Stochastic Dual Coordinate Ascent. It is a fast learning algorithm that works well on table like data, and we limit it to 100 iterations. For any other label type we throw a NotSupportedException.
Fit. Just before the end we print the names of the feature columns, which is again only to check what we are using. Until now we only added steps to the chain. pipeline.Fit(data) actually runs all of them, encodes the text, builds the Features column, scales it and trains the model.
Assemblies and classes used in this project. The ML.NET classes are MLContext, IDataView, DataViewSchema (each column has the Name and Type properties), TextDataViewType, NumberDataViewType and BooleanDataViewType (all have the RawType property, which is the .NET type of the column), DataKind, IEstimator, EstimatorChain and ITransformer. They are in the Microsoft.ML and Microsoft.ML.Data namespaces. The standard .NET classes are Console (WriteLine()), string (Join()), JsonSerializer (Serialize(), in System.Text.Json), List<string> (Add() and ToArray()), ArgumentException and NotSupportedException.
Step 4: Create a Console project and name it mlnetdemo
Add a new Console App project to the same solution and name it mlnetdemo, with .NET 10 as the framework. In this project the data is kept in memory in a C# list. We will predict the price of a house from its size.
Step 5: Add a project reference to mllib
Right click on Dependencies of the mlnetdemo project, select Add Project Reference and select the mllib project. This gives us access to the DataTrainer class. You can also use the command line.
dotnet add reference ../mllib/mllib.csprojStep 6: Add the following packages in the mlnetdemo project
Install these packages from NuGet, the same ones that we explained earlier: Microsoft.Extensions.ML 5.0.1, Microsoft.ML 5.0.1, Microsoft.ML.AutoML 0.23.1, Microsoft.ML.FastTree 5.0.1, Microsoft.ML.LightGbm 5.0.1, Microsoft.ML.Recommender 0.23.1 and Microsoft.ML.TimeSeries 5.0.1. The project file will look like this.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Extensions.ML" Version="5.0.1" />
<PackageReference Include="Microsoft.ML" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.AutoML" Version="0.23.1" />
<PackageReference Include="Microsoft.ML.FastTree" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.LightGbm" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.Recommender" Version="0.23.1" />
<PackageReference Include="Microsoft.ML.TimeSeries" Version="5.0.1" />
</ItemGroup>
<ItemGroup>
<ProjectReference Include="..\mllib\mllib.csproj" />
</ItemGroup>
</Project>Step 7: Write the following code in Program.cs
Figure 1: The ML.NET pipeline for the in-memory data project
using Microsoft.ML;
using Microsoft.ML.Data;
using mllib;
using System.Text.Json;
Console.WriteLine("The smallest complete ML.NET program. It loads objects, builds a Features column, trains a linear regression, and predicts. With so little data the numbers only show the shape of the API.");
/*
MLContext initializes the ML.NET environment and is the first object you create in any ML.NET application.
It provides access to all ML.NET components, such as data loading, transformations, training algorithms, evaluation, and model management.
It manages reproducibility by allowing you to specify a random seed:
It is used throughout the complete ML workflow, including loading data, building pipelines, training models, evaluating accuracy, and making predictions.
It acts like a factory class, exposing namespaces such as:
mlContext.Data → Data operations
mlContext.Transforms → Feature engineering
mlContext.BinaryClassification → Binary ML models
mlContext.MulticlassClassification → Multi-class models
mlContext.Model → Save/Load models
*/
// The demo runs in a loop so several house sizes can be tried. It exits when 'q' is pressed.
do
{
#region Commented Code
// Earlier version of this demo that built and trained the pipeline directly in this file.
// The same work is now done by DataTrainer.Train in the mllib project.
// Create the ML.NET context. A fixed seed makes the training results repeatable.
var mlc = new MLContext(seed: 0);
/*
// the Data
var database = new HouseDatabase();
// load the data as an IDataView object
IDataView data = mlc.Data.LoadFromEnumerable(database);
var vrdata = data.Preview();
Console.WriteLine($"Data: {JsonSerializer.Serialize(vrdata.RowView)}");
// Print The Data
foreach (var column in data.Schema)
{
Console.WriteLine($"Column Name: {column.Name}, Column Type: {column.Type}");
}
// Transform the data by concatenating the Size column into a Features column
var pipeline = mlc.Transforms.Concatenate("Features", "Size")
.Append(mlc.Regression.Trainers.Sdca(labelColumnName: "Price", maximumNumberOfIterations: 100));
// Train the data
ITransformer model = pipeline.Fit(data);
*/
#endregion
// Helper from the mllib project that prepares the features, picks the trainer and trains.
DataTrainer dataTrainer = new DataTrainer();
// Enter the size of the house to predict the price
Console.WriteLine("Enter the size of the house to predict the price:");
float size = float.Parse(Console.ReadLine());
// Train on the sample houses. Price is a float label, so DataTrainer picks a regression
// trainer, and Size is the only feature.
var model = dataTrainer.Train<HouseData>(mlc, new HouseDatabase(), "Price", "Size");
// Create a prediction engine from the trained model. It maps one input object (HouseData)
// to one output object (PricePrediction).
var engine = mlc.Model.CreatePredictionEngine<HouseData, PricePrediction>(model);
// Make a prediction for the size entered by the user. Price is not set because it is
// the value being predicted.
var prediction = engine.Predict(new HouseData() { Size = size });
// Show the predicted price with two decimal places.
Console.WriteLine($"The Predicated Price for size {size} is: {prediction.Price:F2}");
//Console.ReadLine();
Console.WriteLine("Press any key to run the ML.NET demo, or 'q' to quit.");
// Loop again unless the user pressed 'q'.
} while (Console.ReadKey().KeyChar != 'q');
/*
The Model Classes for defining the Data Schema
*/
/// <summary>
/// One house in the training data (and the input to the prediction engine).
/// </summary>
public class HouseData
{
/// <summary>The size of the house. Used as the feature.</summary>
public float Size { get; set; }
/// <summary>The price of the house. This is the label the model predicts.</summary>
public float Price { get; set; }
}
/// <summary>
/// The prediction output. A regression trainer writes its result to the Score column,
/// so the property is mapped to that name.
/// </summary>
public class PricePrediction
{
/// <summary>The predicted price.</summary>
[ColumnName("Score")]
public float Price { get; set; }
}
/// <summary>
/// A small in-memory training set of house sizes and prices. With so few rows the
/// predictions only show how the ML.NET API works.
/// </summary>
public class HouseDatabase : List<HouseData>
{
public HouseDatabase()
{
// Each row is one training example: the size is the input and the price is the known answer.
Add( new HouseData() { Size = 1.1F, Price = 1.2F });
Add( new HouseData() { Size = 1.9F, Price = 2.3F });
Add( new HouseData() { Size = 2.8F, Price = 3.0F });
Add( new HouseData() { Size = 3.4F, Price = 3.7F });
Add( new HouseData() { Size = 4.0F, Price = 4.5F });
Add( new HouseData() { Size = 5.0F, Price = 5.0F });
Add( new HouseData() { Size = 4.2F, Price = 4.7F });
}
}Let us understand the code.
The model classes. HouseData has two float properties, Size and Price. It is used for the training data and also as the input for the prediction. PricePrediction is the output class. A regression trainer puts its answer in a column named Score, so we use the [ColumnName("Score")] attribute to map that column to our Price property. HouseDatabase inherits from List<HouseData> and adds seven houses in its constructor. This is our in-memory data. Each row has a size and the price that we already know.
The loop. The whole code is inside a do ... while loop, so that we can try different sizes without starting the application again. It ends when we press the q key.
MLContext. We create it with seed: 0, which makes the random parts of the training repeatable. The long comment at the top of the file is my note about what MLContext is. The block inside #region Commented Code is the first version of this demo, where the pipeline was written directly in this file. It is now replaced by DataTrainer, but I kept it so that you can compare.
Training. We ask the user for the size and read it with float.Parse. Then dataTrainer.Train<HouseData>(mlc, new HouseDatabase(), "Price", "Size") trains the model. The label is Price and the only feature is Size. Because the label is a float, DataTrainer selects the regression trainer. The result is the model object.
Prediction. CreatePredictionEngine<HouseData, PricePrediction>(model) creates the engine, and Predict() takes a HouseData object that has only the Size, because Price is the value we want to find. The answer is in prediction.Price, which we show with two decimals.
When I ran this and entered 3 as the size, the output was:
Enter the size of the house to predict the price:
3
Column Name: Size, Column Type: Single
Column Name: Price, Column Type: Single
Feature Columns Size
The Predicated Price for size 3 is: 3.24
Press any key to run the ML.NET demo, or 'q' to quit.The lines that start with Column Name and Feature Columns are printed by DataTrainer. It also prints the first five rows of the data as JSON, which I have left out here to keep the output short.
The predicted price is close to 3.2. If you run it a few times you will see small differences in the second decimal (I saw values between 3.15 and 3.28), because SDCA uses many threads and does not give exactly the same result on every run. With only seven rows of data, the numbers only show how the API works.
Assemblies and classes used in this project. The ML.NET classes are MLContext, ITransformer, PredictionEngine and the ColumnName attribute from Microsoft.ML.Data. The using mllib; line brings the DataTrainer class from our class library. The standard .NET classes are Console (WriteLine(), ReadLine() and ReadKey(), which returns a ConsoleKeyInfo that has the KeyChar property), float (Parse()) and List<T> (Add()).
Step 8: Create a Console project and name it loadtextfiledemo
Add one more Console App project to the solution with the name loadtextfiledemo. This time the data will come from a CSV file.
Step 9: Add a project reference to mllib
Same as Step 5, add the project reference to the mllib project.
Step 10: Add the following packages in the loadtextfiledemo project
Add the same seven packages that we added in Step 6. The project file is the same as the one of mlnetdemo.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Extensions.ML" Version="5.0.1" />
<PackageReference Include="Microsoft.ML" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.AutoML" Version="0.23.1" />
<PackageReference Include="Microsoft.ML.FastTree" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.LightGbm" Version="5.0.1" />
<PackageReference Include="Microsoft.ML.Recommender" Version="0.23.1" />
<PackageReference Include="Microsoft.ML.TimeSeries" Version="5.0.1" />
</ItemGroup>
<ItemGroup>
<ProjectReference Include="..\mllib\mllib.csproj" />
</ItemGroup>
</Project>Step 11: Add the orders.csv file
Create a folder named csvdata and add the orders.csv file. I have used a file with 10,000 rows. The first line has the column names, and each row is one order. These are the first rows.
item_count,total,payment,returned
4,164.87,debit_card,false
1,53.52,credit_card,false
3,115.65,debit_card,false
3,201.50,debit_card,falseThe payment column has five different values: credit_card, debit_card, paypal, cash and gift_card. In my file, 751 of the 10,000 orders have returned set to true. Keep this number in mind, we will come back to it.
Step 12: Write the following code in Program.cs
Figure 2: The ML.NET pipeline for the CSV file project
using Microsoft.ML;
using Microsoft.ML.Data;
using mllib;
using System.Text.Json;
// Demo: load orders.csv, train a model that predicts whether an order will be returned,
// and ask the user for an order total and payment mode to get a prediction.
Console.WriteLine("Loading the CSV file");
// MLContext is the entry point to all ML.NET operations (data, transforms, trainers, models).
var mlContext = new MLContext();
// Load the comma-separated CSV as an IDataView using OrderCsv's [LoadColumn] mappings.
// Skip the header and allow quoted fields, including values containing commas.
// Rows are read lazily when the IDataView is consumed.
// allowQuoting: true allows quoted fields, including values containing commas.
// hasHeader: true skips the first line of the file, which contains column names.
IDataView dataView = mlContext.Data.LoadFromTextFile<OrderCsv>(@"D:\maheshapps\net10\MLNET\csvdata\orders.csv", hasHeader: true, separatorChar: ',', allowQuoting: true );
// Preview() reads up to 100 rows by default, so this prints the number of preview rows, not the file's total.
Console.WriteLine($"{dataView.Preview().RowView.Length}, rows loaded");
// Preview of the loaded data (useful for debugging, for example in the commented loop below).
var preview = dataView.Preview();
// Iterate over the IDataView's schema to see the values
//foreach (var row in preview.RowView)
//{
// Console.WriteLine($"Keys {string.Join(", ", row.Values.Select(v => v.Key))}, Values {string.Join(", ", row.Values.Select(v => v.Value))}");
//}
// one column as an array
//float [] totals = dataView.GetColumn<float>("Total").ToArray();
//Console.WriteLine($"Total Average: {totals.Average()}");
// Put this data in C# objects. reuseRowObject: false creates a new object per row,
// which is required because the list keeps all of them.
List<OrderCsv> orders = mlContext.Data.CreateEnumerable<OrderCsv>(dataView, reuseRowObject: false).ToList();
//Console.WriteLine($"Orders Data: {JsonSerializer.Serialize(orders)}");
// Wrap the interactive part in try/catch so bad input (for example a non-numeric total)
// or a training problem is shown as a message instead of crashing the app.
try
{
// Helper from the mllib project that prepares the features, picks the trainer and trains.
DataTrainer dataTrainer = new DataTrainer();
// Read the order total entered by the user (float.Parse throws on invalid input).
Console.WriteLine("Enter the Order Total in Rs./-");
float orderTotal = float.Parse(Console.ReadLine());
// Read the payment mode, for example credit_card, debit_card, paypal, cash or gift_card.
Console.WriteLine("Enter the Payment Mode to check the status for return status:");
string payment_mode = Console.ReadLine();
// Train the model using the DataTrainer class. Here the Total and Payment columns are the features
// used to predict the Returned column. Because Returned is a bool, DataTrainer picks binary
// classification, and it one-hot encodes the text column Payment.
var model = dataTrainer.Train<OrderCsv>(mlContext, orders, "Returned", "Total", "Payment");
// Create a prediction engine from the trained model. It maps one input object (OrderCsv)
// to one output object (OrderPrediction). It is not thread safe, so use one per thread.
var engine = mlContext.Model.CreatePredictionEngine<OrderCsv, OrderPrediction>(model);
// Predict using the values the user entered. ItemCount is left at 0 because it is not a feature.
var prediction = engine.Predict(new OrderCsv() { Total = orderTotal, Payment = payment_mode });
Console.WriteLine($"Prediction: Returned = {prediction.Returned}");
}
catch (Exception ex)
{
// Show the error message (for example invalid number format or a training error).
Console.WriteLine($"The Error: {ex.Message}");
}
// Keep the console window open until Enter is pressed.
Console.ReadLine();
/// <summary>
/// One row of orders.csv. [LoadColumn(n)] maps each property to the zero-based CSV column index,
/// in the order: item_count, total, payment, returned.
/// </summary>
public class OrderCsv
{
/// <summary>Number of items in the order (CSV column 0).</summary>
[LoadColumn(0)]
public float ItemCount { get; set; }
/// <summary>Order total (CSV column 1). Used as a feature.</summary>
[LoadColumn(1)]
public float Total { get; set; }
/// <summary>Payment mode, such as credit_card or paypal (CSV column 2). Text feature, one-hot encoded.</summary>
[LoadColumn(2)]
public string Payment { get; set; } = null!;
/// <summary>Whether the order was returned (CSV column 3). This is the label the model predicts.</summary>
[LoadColumn(3)]
public bool Returned { get; set; }
}
/// <summary>
/// The prediction output. The binary classifier writes its true/false result to the
/// PredictedLabel column, so the property is mapped to that name.
/// </summary>
public class OrderPrediction
{
/// <summary>The predicted value: true if the order is predicted to be returned.</summary>
[ColumnName("PredictedLabel")]
public bool Returned { get; set; }
}Let us understand the code.
The OrderCsv class. It has one property for each column in the file. The [LoadColumn(n)] attribute tells ML.NET which column of the file goes to which property, and the number is the position of the column, starting from zero. So ItemCount is column 0, Total is column 1, Payment is column 2 and Returned is column 3. The types decide how the values are read: a float, a string and a bool. OrderPrediction is the output class. A binary classifier writes its true or false answer in a column named PredictedLabel, so we map our Returned property to that column.
Loading the file. LoadFromTextFile<OrderCsv> reads the file and gives an IDataView. We pass the path of the file and three options. hasHeader: true skips the first line, because it has the column names and not the data. separatorChar: ',' says the values are separated by a comma. allowQuoting: true allows values inside double quotes, which can contain commas. Remember that the IDataView is lazy, so the file is read only when the data is used. Change the path in the code to the place where you have saved your file.
Preview. dataView.Preview() gives the first 100 rows by default, so the message 100, rows loaded shows the number of preview rows and not the number of rows in the file. The commented lines show two more ways to look at the data. One loops through the RowView of the preview and prints the keys and values of each row, and the other reads one column as an array with GetColumn<float>("Total"), which is useful to calculate things like an average.
CreateEnumerable. DataTrainer accepts a list of objects, so we convert the IDataView to a List<OrderCsv> with CreateEnumerable. We pass reuseRowObject: false because we keep all the objects in a list, and with true the same object would be reused for every row.
try and catch. The part that talks to the user is inside a try block. If the user types something that is not a number, float.Parse throws an exception, and the catch block shows the message instead of crashing the application.
Training and prediction. We read the order total and the payment mode, and call Train with Returned as the label, and Total and Payment as the features. Payment is text, so DataTrainer one-hot encodes it, and because Returned is a bool, it uses binary classification. The rest is the same as before, we create the prediction engine and call Predict() with the values entered by the user. ItemCount is not used as a feature, so we do not set it.
Here is a sample run.
Loading the CSV file
100, rows loaded
Enter the Order Total in Rs./-
250
Enter the Payment Mode to check the status for return status:
credit_card
Column Name: ItemCount, Column Type: Single
Column Name: Total, Column Type: Single
Column Name: Payment, Column Type: String
Column Name: Returned, Column Type: Boolean
Feature Columns Total, Payment
Prediction: Returned = FalseI also tried a few other combinations, like 900 with paypal, 20 with cash, 50 with debit_card and 600 with gift_card, and all of them gave False. This is expected, because only about 7.5 percent of the orders in the file are returned, and the total and the payment mode alone are not enough to say that a particular order will be returned. This is a good reminder that a model can only learn what is there in the data. If you have columns like the product category or the customer history, add them as features and the result will be more useful.
Assemblies and classes used in this project. The ML.NET classes are MLContext, IDataView, DataOperationsCatalog (the mlContext.Data property, with LoadFromTextFile and CreateEnumerable), LoadColumnAttribute, ColumnNameAttribute, ITransformer and PredictionEngine. They are in the Microsoft.ML and Microsoft.ML.Data namespaces. The standard .NET classes are Console (WriteLine() and ReadLine()), float (Parse()), Exception (the Message property), List<T> and the LINQ method ToList(). The using System.Text.Json; line is used only by the commented line that prints the orders as JSON.
Step 13: Create a Console project and name it mlnet_sql
Add the last Console App project to the solution with the name mlnet_sql. Here the data comes from SQL Server. I have used the Sales.SalesOrderHeader table from the AdventureWorks2025 database, which has 31,465 sales orders. The column OnlineOrderFlag tells whether the order was placed online, and this is what we will predict, using the sub total, the freight and the sales territory of the order.
Step 14: Add a project reference to mllib
Again, add the project reference to the mllib project, in the same way as in Step 5.
Step 15: Add the following packages in the mlnet_sql project
Install Microsoft.ML version 5.0.1 and Microsoft.Data.SqlClient. The second package is the .NET data provider for SQL Server. ML.NET does not talk to SQL Server by itself, it uses a provider that we give it. From Microsoft.Data.SqlClient we use the SqlClientFactory class.
dotnet add package Microsoft.ML --version 5.0.1
dotnet add package Microsoft.Data.SqlClient<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Data.SqlClient" Version="7.1.1" />
<PackageReference Include="Microsoft.ML" Version="5.0.1" />
<PackageReference Include="System.Security.Cryptography.Pkcs" Version="10.0.14" />
</ItemGroup>
<ItemGroup>
<ProjectReference Include="..\mllib\mllib.csproj" />
</ItemGroup>
</Project>Step 16: Write the following code in Program.cs
Figure 3: The ML.NET pipeline for the SQL Server project
using Microsoft.Data.SqlClient;
using Microsoft.ML;
using Microsoft.ML.Data;
using mllib;
// Demo: load data from SQL Server (AdventureWorks2025) instead of a CSV file, train a model
// that predicts whether a sales order was placed ONLINE (true) or through a reseller/salesperson
// (false), and let the user try their own order values.
//
// Source table: Sales.SalesOrderHeader (about 31,000 orders)
// Label : OnlineOrderFlag (bit) -> the value we want to predict
// Features : SubTotal, Freight (money) and TerritoryID (a code, treated as a category)
// Connection string. Uses Windows authentication to the local default instance.
// To use another server, set the environment variable MLNET_SQL_CONNECTION.
// TrustServerCertificate is on because a local developer instance usually has a self-signed certificate.
string connectionString =
Environment.GetEnvironmentVariable("MLNET_SQL_CONNECTION")
?? "Server=.;Database=AdventureWorks2025;Integrated Security=True;TrustServerCertificate=True;";
// The query returns exactly the columns the OrderRow class expects, in the same order as the
// [LoadColumn] indexes. The casts matter because ML.NET reads columns as float / string / bool:
// money/decimal -> REAL (float)
// TerritoryID -> NVARCHAR, so it is one-hot encoded as a category (the ID number itself has no
// meaningful size, territory 9 is not "more" than territory 2)
// OnlineOrderFlag -> BIT (bool)
const string sql = @"
SELECT CAST(SubTotal AS REAL) AS SubTotal,
CAST(Freight AS REAL) AS Freight,
CAST(TerritoryID AS NVARCHAR(10)) AS TerritoryId,
CAST(OnlineOrderFlag AS BIT) AS IsOnline
FROM Sales.SalesOrderHeader
WHERE TerritoryID IS NOT NULL";
// MLContext is the entry point to ML.NET. A fixed seed makes the results repeatable.
var mlContext = new MLContext(seed: 0);
try
{
Console.WriteLine("Loading orders from SQL Server...");
// DatabaseLoader is the ML.NET way to read from a database. CreateDatabaseLoader<T>() builds the
// column layout from the [LoadColumn] attributes on OrderRow.
DatabaseLoader loader = mlContext.Data.CreateDatabaseLoader<OrderRow>();
// DatabaseSource ties together the provider (SQL Server), the connection string and the query.
var source = new DatabaseSource(SqlClientFactory.Instance, connectionString, sql);
// Load() is lazy. Nothing is read until the data is consumed on the next line.
IDataView sqlView = loader.Load(source);
// Read the rows into memory once as C# objects. DataTrainer takes a list of objects,
// and this avoids going back to the database for every pass over the data.
List<OrderRow> allRows = mlContext.Data.CreateEnumerable<OrderRow>(sqlView, reuseRowObject: false).ToList();
Console.WriteLine($"{allRows.Count} orders loaded from SQL Server.");
// Hold back 20% of the rows to test the model on orders it has never seen.
// A model scored on its own training data would look better than it really is.
IDataView allData = mlContext.Data.LoadFromEnumerable(allRows);
var split = mlContext.Data.TrainTestSplit(allData, testFraction: 0.2, seed: 0);
List<OrderRow> trainRows = mlContext.Data.CreateEnumerable<OrderRow>(split.TrainSet, reuseRowObject: false).ToList();
// Train with the shared DataTrainer class from the mllib project. IsOnline is a bool label, so it
// uses binary classification, and it one-hot encodes the text column TerritoryId on its own.
var trainer = new DataTrainer();
ITransformer model = trainer.Train<OrderRow>(mlContext, trainRows, "IsOnline", "SubTotal", "Freight", "TerritoryId");
// Score the model on the held-back test rows.
IDataView scoredTest = model.Transform(split.TestSet);
var metrics = mlContext.BinaryClassification.Evaluate(scoredTest, labelColumnName: "IsOnline");
// Compare with a model that always answers "online". If accuracy is not clearly above this
// baseline, the model has learned nothing useful.
double baseline = allRows.Count(r => r.IsOnline) / (double)allRows.Count;
Console.WriteLine();
Console.WriteLine($"Accuracy on unseen orders: {metrics.Accuracy:P1} (always guessing 'online' gives {baseline:P1})");
Console.WriteLine($"AUC: {metrics.AreaUnderRocCurve:F3} (1.0 is perfect, 0.5 is a coin flip)");
// The prediction engine turns one OrderRow into one OrderPrediction. It is not thread safe.
var engine = mlContext.Model.CreatePredictionEngine<OrderRow, OrderPrediction>(model);
// Let the user try their own orders until they leave the first question empty.
while (true)
{
Console.WriteLine();
Console.Write("Order SubTotal (empty to quit): ");
string? subTotalText = Console.ReadLine();
if (string.IsNullOrWhiteSpace(subTotalText))
{
break;
}
Console.Write("Freight: ");
string? freightText = Console.ReadLine();
Console.Write("TerritoryID (1 to 10): ");
string? territory = Console.ReadLine();
// TryParse avoids a crash on bad input and lets the user try again.
if (!float.TryParse(subTotalText, out float subTotal) || !float.TryParse(freightText, out float freight)
|| string.IsNullOrWhiteSpace(territory))
{
Console.WriteLine("Please enter numbers for SubTotal and Freight, and a TerritoryID.");
continue;
}
// IsOnline is left at its default because it is the value being predicted.
var prediction = engine.Predict(new OrderRow
{
SubTotal = subTotal,
Freight = freight,
TerritoryId = territory.Trim()
});
// Probability is the model's confidence (0 to 1) that the order is online.
string channel = prediction.IsOnline ? "an ONLINE order" : "a RESELLER order";
Console.WriteLine($"Prediction: this looks like {channel} ({prediction.Probability:P0} chance of online).");
}
}
catch (Exception ex)
{
// Most likely causes: SQL Server not reachable, wrong database name, or no permission.
Console.WriteLine($"The Error: {ex.Message}");
}
/// <summary>
/// One order read from Sales.SalesOrderHeader. [LoadColumn(n)] matches each property to the
/// zero-based column position in the SQL query above.
/// </summary>
public class OrderRow
{
/// <summary>Order value before tax and freight. Feature.</summary>
[LoadColumn(0)]
public float SubTotal { get; set; }
/// <summary>Shipping cost. Feature.</summary>
[LoadColumn(1)]
public float Freight { get; set; }
/// <summary>Sales territory code (1 to 10) kept as text so it is treated as a category. Feature.</summary>
[LoadColumn(2)]
public string TerritoryId { get; set; } = string.Empty;
/// <summary>True if the order was placed online. This is the label the model predicts.</summary>
[LoadColumn(3)]
public bool IsOnline { get; set; }
}
/// <summary>
/// The prediction output of the binary classifier.
/// </summary>
public class OrderPrediction
{
/// <summary>The predicted answer: true if the order is predicted to be online.</summary>
[ColumnName("PredictedLabel")]
public bool IsOnline { get; set; }
/// <summary>How confident the model is that the order is online (0 to 1).</summary>
public float Probability { get; set; }
}Let us understand the code.
The connection string. It connects to the default SQL Server instance on the local machine (Server=.) and the AdventureWorks2025 database, with Windows authentication (Integrated Security=True). TrustServerCertificate=True is added because a local development server normally has a self-signed certificate. If your server is different, set the environment variable MLNET_SQL_CONNECTION instead of changing the code. Environment.GetEnvironmentVariable reads it, and the ?? operator uses the default string when the variable is not set.
The SQL query. It returns exactly the four columns that the OrderRow class expects, in the same order as the [LoadColumn] numbers. The casts are important. ML.NET reads a column as a float, a string or a bool, so the money columns SubTotal and Freight are cast to REAL (a float), and OnlineOrderFlag is cast to BIT (a bool). TerritoryID is cast to NVARCHAR, which makes it a text column, and DataTrainer will one-hot encode it. The territory is a code, and territory 9 is not bigger than territory 2, so it should be treated as a category and not as a number.
Loading from SQL Server. CreateDatabaseLoader<OrderRow>() builds the loader from the [LoadColumn] attributes of the class. DatabaseSource joins the three things that the loader needs: the provider (SqlClientFactory.Instance), the connection string and the query. loader.Load(source) returns an IDataView, and as always it is lazy. The rows are read when we call CreateEnumerable. We read them once into a List<OrderRow>, so that we do not go to the database again for every pass over the data.
Train and test split. In the earlier projects we trained on all the rows. If we check the model with the same rows that it learned from, the result looks better than it really is. So here we keep 20 percent of the rows aside. TrainTestSplit returns an object that has two properties, TrainSet and TestSet. We train only on the TrainSet.
Training. The label is IsOnline and the features are SubTotal, Freight and TerritoryId. DataTrainer sees a bool label, so it uses binary classification, and it one-hot encodes the text column TerritoryId by itself. This is the benefit of having a shared class, we did not have to write any new training code for this project.
Evaluation. model.Transform(split.TestSet) runs the model on the held back rows and adds the prediction columns. Then BinaryClassification.Evaluate compares the predictions with the real values and returns a BinaryClassificationMetrics object. We print two of its properties. Accuracy is the share of correct predictions. AreaUnderRocCurve (AUC) tells how well the model separates the two answers, where 1.0 is perfect and 0.5 is no better than a coin flip. Other properties of this class are F1Score, PositivePrecision, PositiveRecall, NegativePrecision, NegativeRecall, AreaUnderPrecisionRecallCurve and ConfusionMatrix.
Accuracy alone can be misleading, because 87.9 percent of the orders in this table are online orders. A model that always says online will be correct 87.9 percent of the time without learning anything. That is why the code also prints this baseline, so we can see whether the model is really better than guessing.
The prediction loop. The code asks for the sub total, the freight and the territory and calls Predict(). The output class OrderPrediction has PredictedLabel mapped to IsOnline, and Probability, which tells how sure the model is that the order is online. We use float.TryParse instead of float.Parse, so a wrong input shows a message and asks again instead of throwing an exception. An empty sub total ends the loop.
This is the output of a run.
Loading orders from SQL Server...
31465 orders loaded from SQL Server.
Column Name: SubTotal, Column Type: Single
Column Name: Freight, Column Type: Single
Column Name: TerritoryId, Column Type: String
Column Name: IsOnline, Column Type: Boolean
Feature Columns SubTotal, Freight, TerritoryId
Accuracy on unseen orders: 96.2% (always guessing 'online' gives 87.9%)
AUC: 0.925 (1.0 is perfect, 0.5 is a coin flip)
Order SubTotal (empty to quit): 300
Freight: 9
TerritoryID (1 to 10): 1
Prediction: this looks like an ONLINE order (98% chance of online).
Order SubTotal (empty to quit): 25000
Freight: 750
TerritoryID (1 to 10): 6
Prediction: this looks like a RESELLER order (0% chance of online).The model is right about 96 percent of the time on orders that it has never seen, which is clearly better than the 87.9 percent of the guessing model. In my runs the accuracy was between 96.2 and 96.3 percent and the AUC was about 0.93. A small order is predicted as online and a big order is predicted as a reseller order, which makes sense when we look at the data, because the average sub total of an online order is about 1,061 and the average of a reseller order is about 21,148.
Assemblies and classes used in this project. From ML.NET we use MLContext, DatabaseLoader, DatabaseSource (properties ProviderFactory, ConnectionString, CommandText and CommandTimeoutInSeconds), IDataView, DataOperationsCatalog (LoadFromEnumerable, CreateEnumerable and TrainTestSplit), BinaryClassificationMetrics, ITransformer, PredictionEngine, LoadColumnAttribute and ColumnNameAttribute. From Microsoft.Data.SqlClient we use SqlClientFactory, which has a static Instance property. The standard .NET classes are Environment (GetEnvironmentVariable()), Console (Write(), WriteLine() and ReadLine()), float (TryParse()), string (IsNullOrWhiteSpace() and Trim()), List<T> (Count), the LINQ methods ToList() and Count(), and Exception (Message).
What is next: saving the model
In all three projects we train the model every time the application starts. That is fine for a demo, but in a real application we train once, save the model and load it when needed. This is done with the ModelOperationsCatalog that we saw earlier. This code is not part of the sample solution.
// save the trained model to a .zip file
mlContext.Model.Save(model, dataView.Schema, "model.zip");
// load it again, maybe in another application
ITransformer loadedModel = mlContext.Model.Load("model.zip", out DataViewSchema inputSchema);In a web application, register the loaded model with PredictionEnginePool from the Microsoft.Extensions.ML package, so that many requests can use it at the same time.
Conclusion
In this article, we saw how to use ML.NET to make predictions with data from three different places: a C# list in memory, a CSV file and a SQL Server table. The important point is that the machine learning work was written only once, in the DataTrainer class of the mllib project. The three console applications only load the data in a different way and then use the same class. We also saw the main ML.NET classes, MLContext, IDataView, DatabaseLoader, ITransformer, ModelOperationsCatalog and the CreatePredictionEngine method, and how they fit in the flow of data, transforms, trainer, model and prediction. I hope this helps you to start with ML.NET in your own .NET applications.
