Reading a Single Column from a Table

These scenarios demonstrate how to read a single column from a table.

For an example of reading a scalar value, see Reading a Scalar Value from a Row

Scenario Prototype

public interface ISingleColumnScenario
{
    List<int> GetDivisionKeys(int maxDivisionKey);

    List<string> GetDivisionNames(int maxDivisionKey);

    List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey);

    List<int?> GetMaxEmployees(int maxDivisionKey);

    List<DateTime> GetModifiedDates(int maxDivisionKey);

    List<decimal?> GetSalaryBudgets(int maxDivisionKey);

    List<TimeSpan?> GetStartTimes(int maxDivisionKey);
}

ADO.NET

In ADO.NET, ExecuteScalar returns the first column of the first row in the resultset. Everything else is discarded.

public class SingleColumnScenario : SqlServerScenarioBase, ISingleColumnScenario
{
    public SingleColumnScenario(string connectionString) : base(connectionString)
    { }

    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        var sql = "SELECT DivisionKey FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@MaxDivisionKey", maxDivisionKey);

            var results = new List<int>();
            using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                    results.Add(reader.GetInt32(0));

            return results;
        }
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@MaxDivisionKey", maxDivisionKey);

            var results = new List<string>();
            using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                    results.Add(reader.GetString(0));

            return results;
        }
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        var sql = "SELECT LastReviewCycle FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@MaxDivisionKey", maxDivisionKey);

            var results = new List<DateTimeOffset?>();
            using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                    if (reader.IsDBNull(0))
                        results.Add(null);
                    else
                        results.Add(reader.GetDateTimeOffset(0));

            return results;
        }
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        var sql = "SELECT MaxEmployees FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@MaxDivisionKey", maxDivisionKey);

            var results = new List<int?>();
            using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                    if (reader.IsDBNull(0))
                        results.Add(null);
                    else
                        results.Add(reader.GetInt32(0));

            return results;
        }
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        var sql = "SELECT ModifiedDate FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@MaxDivisionKey", maxDivisionKey);

            var results = new List<DateTime>();
            using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                    results.Add(reader.GetDateTime(0));

            return results;
        }
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        var sql = "SELECT SalaryBudget FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@MaxDivisionKey", maxDivisionKey);

            var results = new List<decimal?>();
            using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                    if (reader.IsDBNull(0))
                        results.Add(null);
                    else
                        results.Add(reader.GetDecimal(0));

            return results;
        }
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        var sql = "SELECT StartTime FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@MaxDivisionKey", maxDivisionKey);

            var results = new List<TimeSpan?>();
            using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                    if (reader.IsDBNull(0))
                        results.Add(null);
                    else
                        results.Add(reader.GetTimeSpan(0));

            return results;
        }
    }
}

Chain

public class SingleColumnScenario : ISingleColumnScenario
{
    const string TableName = "HR.Division";
    readonly SqlServerDataSource m_DataSource;

    public SingleColumnScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        return m_DataSource.From(TableName, "DivisionKey <= @MaxDivisionKey", new { maxDivisionKey })
            .ToInt32List("DivisionKey").Execute();
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        return m_DataSource.From(TableName, "DivisionKey <= @MaxDivisionKey", new { maxDivisionKey })
            .ToStringList("DivisionName").Execute();
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        return m_DataSource.From(TableName, "DivisionKey <= @MaxDivisionKey", new { maxDivisionKey })
            .ToDateTimeOffsetOrNullList("LastReviewCycle").Execute();
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        return m_DataSource.From(TableName, "DivisionKey <= @MaxDivisionKey", new { maxDivisionKey })
            .ToInt32OrNullList("MaxEmployees").Execute();
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        return m_DataSource.From(TableName, "DivisionKey <= @MaxDivisionKey", new { maxDivisionKey })
            .ToDateTimeList("ModifiedDate").Execute();
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        return m_DataSource.From(TableName, "DivisionKey <= @MaxDivisionKey", new { maxDivisionKey })
            .ToDecimalOrNullList("SalaryBudget").Execute();
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        return m_DataSource.From(TableName, "DivisionKey <= @MaxDivisionKey", new { maxDivisionKey })
               .ToTimeSpanOrNullList("StartTime").Execute();
    }
}

Dapper

public class SingleColumnScenario : ScenarioBase, ISingleColumnScenario
{
    public SingleColumnScenario(string connectionString) : base(connectionString)
    { }

    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        var sql = "SELECT DivisionKey FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
            return con.Query<int>(sql, new { maxDivisionKey }).ToList();
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
            return con.Query<string>(sql, new { maxDivisionKey }).ToList();
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        var sql = "SELECT LastReviewCycle FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
            return con.Query<DateTimeOffset?>(sql, new { maxDivisionKey }).ToList();
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        var sql = "SELECT MaxEmployees FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
            return con.Query<int?>(sql, new { maxDivisionKey }).ToList();
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        var sql = "SELECT ModifiedDate FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
            return con.Query<DateTime>(sql, new { maxDivisionKey }).ToList();
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        var sql = "SELECT SalaryBudget FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
            return con.Query<decimal?>(sql, new { maxDivisionKey }).ToList();
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        var sql = "SELECT StartTime FROM HR.Division WHERE DivisionKey < @MaxDivisionKey;";
        using (var con = OpenConnection())
            return con.Query<TimeSpan?>(sql, new { maxDivisionKey }).ToList();
    }
}

DbConnector

public class SingleColumnScenario : ScenarioBase, ISingleColumnScenario
{
    public SingleColumnScenario(string connectionString) : base(connectionString)
    { }

    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        var sql = "SELECT DivisionKey FROM HR.Division WHERE DivisionKey < @maxDivisionKey;";

        return DbConnector.ReadToList<int>(sql, new { maxDivisionKey }).Execute();
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey < @maxDivisionKey;";

        return DbConnector.ReadToList<string>(sql, new { maxDivisionKey }).Execute();
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        var sql = "SELECT LastReviewCycle FROM HR.Division WHERE DivisionKey < @maxDivisionKey;";

        return DbConnector.ReadToList<DateTimeOffset?>(sql, new { maxDivisionKey }).Execute();
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        var sql = "SELECT MaxEmployees FROM HR.Division WHERE DivisionKey < @maxDivisionKey;";

        return DbConnector.ReadToList<int?>(sql, new { maxDivisionKey }).Execute();
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        var sql = "SELECT ModifiedDate FROM HR.Division WHERE DivisionKey < @maxDivisionKey;";

        return DbConnector.ReadToList<DateTime>(sql, new { maxDivisionKey }).Execute();
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        var sql = "SELECT SalaryBudget FROM HR.Division WHERE DivisionKey < @maxDivisionKey;";

        return DbConnector.ReadToList<decimal?>(sql, new { maxDivisionKey }).Execute();
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        var sql = "SELECT StartTime FROM HR.Division WHERE DivisionKey < @maxDivisionKey;";

        return DbConnector.ReadToList<TimeSpan?>(sql, new { maxDivisionKey }).Execute();
    }
}

Entity Framework 6

public class SingleColumnScenario : ISingleColumnScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public SingleColumnScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.DivisionKey).ToList();
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.DivisionName!).ToList();
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.LastReviewCycle).ToList();
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.MaxEmployees).ToList();
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.ModifiedDate).ToList();
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.SalaryBudget).ToList();
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.StartTime).ToList();
    }
}

Entity Framework Core

public class SingleColumnScenario : ISingleColumnScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public SingleColumnScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.DivisionKey).ToList();
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.DivisionName!).ToList();
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.LastReviewCycle).ToList();
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.MaxEmployees).ToList();
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.ModifiedDate).ToList();
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.SalaryBudget).ToList();
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.StartTime).ToList();
    }
}

LINQ to DB

public class SingleColumnScenario : ISingleColumnScenario
{
    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.DivisionKey).ToList();
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.DivisionName!).ToList();
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.LastReviewCycle).ToList();
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.MaxEmployees).ToList();
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.ModifiedDate).ToList();
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.SalaryBudget).ToList();
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey <= maxDivisionKey)
                .Select(d => d.StartTime).ToList();
    }
}

LLBLGen Pro

public class SingleColumnScenario : ISingleColumnScenario
{
    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            // As the field in the projection isn't nullable, LLBLGen Pro will normally return the default value
            // for when the field is null. Here, however this gives a problem, so we have to
            // cast the field to int? to make FirstOrDefault return a nullable type.
            // A workaround could have been to project to the type first and use that as a check, however that's
            // less efficient.
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey <= maxDivisionKey)
                                            .Select(d => d.DivisionKey).ToList();
        }
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey <= maxDivisionKey)
                                            .Select(d => d.DivisionName).ToList();
        }
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey <= maxDivisionKey)
                                            .Select(d => d.LastReviewCycle).ToList();
        }
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey <= maxDivisionKey)
                                            .Select(d => d.ModifiedDate).ToList();
        }
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey <= maxDivisionKey)
                                            .Select(d => d.SalaryBudget).ToList();
        }
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey <= maxDivisionKey)
                                            .Select(d => d.StartTime).ToList();
        }
    }

    List<int?> ISingleColumnScenario.GetMaxEmployees(int maxDivisionKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey <= maxDivisionKey)
                                            .Select(d => d.MaxEmployees).ToList();
        }
    }
}

NHibernate

TODO

RepoDb

public class SingleColumnScenario : BaseRepository<Division, SqlConnection>,
    ISingleColumnScenario
{
    public SingleColumnScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        return Query(e => e.DivisionKey <= maxDivisionKey).Select(d => d.DivisionKey).ToList();
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        return Query(e => e.DivisionKey <= maxDivisionKey).Select(d => d.DivisionName!).ToList();
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        return Query(e => e.DivisionKey <= maxDivisionKey).Select(d => d.LastReviewCycle).ToList();
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        return Query(e => e.DivisionKey <= maxDivisionKey).Select(d => d.MaxEmployees).ToList();
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        return Query(e => e.DivisionKey <= maxDivisionKey).Select(d => d.ModifiedDate).ToList();
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        return Query(e => e.DivisionKey <= maxDivisionKey).Select(d => d.SalaryBudget).ToList();
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        return Query(e => e.DivisionKey <= maxDivisionKey).Select(d => d.StartTime).ToList();
    }
}

ServiceStack

public class SingleColumnScenario : ISingleColumnScenario
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public SingleColumnScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public List<int> GetDivisionKeys(int maxDivisionKey)
    {
        throw new AssertInconclusiveException("TODO");
    }

    public List<string> GetDivisionNames(int maxDivisionKey)
    {
        throw new AssertInconclusiveException("TODO");
    }

    public List<DateTimeOffset?> GetLastReviewCycles(int maxDivisionKey)
    {
        throw new AssertInconclusiveException("TODO");
    }

    public List<int?> GetMaxEmployees(int maxDivisionKey)
    {
        throw new AssertInconclusiveException("TODO");
    }

    public List<DateTime> GetModifiedDates(int maxDivisionKey)
    {
        throw new AssertInconclusiveException("TODO");
    }

    public List<decimal?> GetSalaryBudgets(int maxDivisionKey)
    {
        throw new AssertInconclusiveException("TODO");
    }

    public List<TimeSpan?> GetStartTimes(int maxDivisionKey)
    {
        throw new AssertInconclusiveException("TODO");
    }
}