Type-Safe Queries Using SnapObjects API

Last Updated: September 2022

Introducing a SQL query example

SnapObjects supports using attributes and properties in the data model class to define SQL queries, and using the SqlModelMapper data access component to execute the SQL queries.

SQL queries are dependent on the model, and the result set of a SQL query is stored in a model object or a collection of model objects. Therefore, the code that executes the SQL query and the code that accesses the query result set are all type-safe.

Note: The code blocks that appear in the current document are only used to demonstrate features. They cannot run directly due to lack of database.

Creating a Data Model and defining the SQL Query

The following example demonstrates how to create a data model class named Department, how to apply the Table attribute on the class, and how to define a simple query in the class. The class is mapped to the dbo.Department table when the Table attribute is applied on the class. In the query, each property in the Department class maps to a column in the Department table (e.g.: the DepartmentID property maps to the dbo.Department.DepartmentID column).

// This model class maps to the dbo.Department table. 
[Table("Department", Schema = "dbo")]
public class Department
{
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }
}

Using SqlModelMapper to execute the SQL Query

The following example demonstrates how to use SqlModelMapper to generate and execute a SQL query, and how to send the result set to a List<Department> object. The SQL query is executed against the database specified in the DataContext in the metadata of the model.

// The following code example demonstrates how to load departments according to the Department class. 
public void SqlModelMapperExample()
{
    // Gets a SqlModelMapper instance by a DataContext object.
    var mapper = _context.SqlModelMapper;

    // Loads all departments.
    // Then gets the result set to a list.
    List<Department> depts = mapper.Load<Department>().ToList();

    Console.WriteLine("The list of departments:");
    foreach (Department dept in depts)
    {
        Console.WriteLine(dept.Name);
    }

    /* The code produces the following output:
    
    The list of departments:
    Engineering
    English
    Economics
    Mathematics
    */
}

Defining a SQL query

Specifying table sources

Attributes used for specifying table sources

Three attributes are supported in SnapObjects to specify the table source. We will describe and give example for each of them.

TableAttribute

It specifies the database table that a model class is mapped to. (TableAttribute comes from .NET)

Only one Table attribute can be applied to one model class.

// A model class can only apply at most one Table attribute..
[Table("Department", Schema = "dbo")]
public class DepartmentTable
{
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }
}

FromTableAttribute

It specifies the database table that a model class is mapped to. It's inherited from TableAttribute.

One or more FromTable attributes can be applied to one model class.

// One or more FromTable attributes can be applied to one model class.
[FromTable("Department", Schema = "dbo")]
public class DepartmentFromTable
{
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }
}

JoinTableAttribute

It sets the joined table to be used in a model class.

Multiple JoinTable attributes can be applied to one model class.

// Multiple JoinTable attributes can be applied to one model class.
// This model class joins dbo.Course with dbo.StudentGrade.
[FromTable("Course", Schema = "dbo")]
[JoinTable("StudentGrade", Schema = "dbo", JoinType =SqlJoinType.Left,
    OnRaw = "Course.CourseID = StudentGrade.CourseID")]
public class CourseJoinTable
{
    public Int32 CourseID { get; set; }

    public String Title { get; set; }

    // This property maps to the Grade column of the joined table (dbo.StudentGrade).
    [SqlColumn("StudentGrade", column: "Grade")]
    public Decimal? Grade { get; set; }

}

Primary table

Primary mapped table represents the primary database table which a model class is mapped to. We refer to it as Primary Table. You can define only one primary table in a model class.

When Table attribute is applied to the model class, the table specified by Table attribute becomes the primary table.

When Table attribute is not applied in the model class, and there is one or more FromTable attribute applied to the model class, the table specified by the first FromTable attribute becomes the primary table.

In other cases, use the class name as the primary table.

When you need to map some columns from the primary table, the SqlColumn attribute and Column attribute can be omitted if the property name of the model class is the same as the column name of the database table (case is insensitive).

Querying from a single table source

You can use either Table or FromTable attribute to specify the table source. If no attribute is specified, it will use the class name as the table source.

// You can use either Table or FromTable attribute to specify the table source
// [Table("Department", Schema = "dbo")]
[FromTable("Department", Schema = "dbo")]
public class Department
{
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }
}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator]
FROM [dbo].[Department]

Querying from multiple table sources

Querying data using JoinTableAttributes

It is recommended to use the JoinTable attribute to specify which tables to join.

// This model class joins dbo.Course with dbo.StudentGrade.
// JoinType is Left Join.
// The raw SQL ""Course.CourseID = StudentGrade.CourseID"" defines the search condition on which the join is based.
[FromTable("Course", Schema = "dbo")]
[JoinTable("StudentGrade", Schema = "dbo", JoinType =SqlJoinType.Left,
    OnRaw = "Course.CourseID = StudentGrade.CourseID")]
public class CourseJoinTable
{
    // It omits SqlColumn attribute here, because the property name CourseID is 
    // the same as the column name of the Course table (Primary Table).     
    public Int32 CourseID { get; set; }

    public String Title { get; set; }

    // This properties mapped to the Grade column of the joined table (dbo.StudentGrade).
    [SqlColumn("StudentGrade", column: "Grade")]
    public Decimal? Grade { get; set; }

}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[dbo].[Course].[CourseID],
[dbo].[Course].[Title],
[StudentGrade].[Grade]
FROM [dbo].[Course]
LEFT JOIN [dbo].[StudentGrade]
ON [Course].[CourseID] = [StudentGrade].[CourseID]

Querying data using FromTableAttributes

You can also use the FromTable attribute to specify the table to be joined, and you have to add the join conditions of the table sources in the WHERE clause of the SQL query.

[Table("Course", Schema = "dbo")]
[FromTable("StudentGrade", Schema = "dbo")]
[SqlWhere("Course.CourseID", "StudentGrade.CourseID")]
public class CourseFromTable
{
    [SqlColumn("Course", column: "CourseID")]
    public Int32 CourseID { get; set; }

    [SqlColumn("Course", column: "Title")]
    public String Title { get; set; }

    [SqlColumn("StudentGrade", column: "Grade")]
    public Decimal? Grade { get; set; }

}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[Course].[CourseID],
[Course].[Title],
[StudentGrade].[Grade] 
FROM [dbo].[Course] ,
[dbo].[StudentGrade] 
WHERE ([Course].[CourseID] = [StudentGrade].[CourseID])

Specifying the Select List

When using SnapObjects, it maps the database columns or expressions to be queried through properties on the Model. And specifies different mapping types by applying attributes on properties.

Attributes used for specifying the Select List

ColumnAttribute

It specifies the database column that the property is mapped to. (ColumnAttribute comes from .NET)

Since the Column attribute cannot specify an alias for a table when instantiating the attribute, it is usually only used for scenes where the model is mapped to a single table.

[Table("Department", Schema = "dbo")]
public class DepartmentColumn
{
    [Key]
    public Int32 DepartmentID { get; set; }

    // This property maps to the Name column.
    [Column("Name")]
    public String DepartmentName { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }
   
}

SqlColumnAttribute

It specifies the database column that the property is mapped to.

You can specify the alias of the column and the alias of the table when instantiating the SqlColumn attribute. It can be used for scenarios where the model performs single-table and multi-table queries.

[Table("Course", Schema = "dbo")]
[FromTable("StudentGrade", Schema = "dbo")]
[SqlWhere("Course.CourseID", "StudentGrade.CourseID")]
public class CourseSqlColumn
{
    // Maps to Course.CourseID
    [SqlColumn("Course", column: "CourseID")]
    public Int32 CourseID { get; set; }

    // Maps to Course.Title
    [SqlColumn("Course", column: "Title")]
    public String Title { get; set; }

    // Maps to StudentGrade.Grade
    [SqlColumn("StudentGrade", column: "Grade")]
    public Decimal? Grade { get; set; }

}

SqlComputeAttribute

It specifies the database computed column or an SQL expression that the property is mapped to.

You can specify an alias for the computed column of the query when you instantiate the attribute.

[Table("Person", Schema = "dbo")]
public class PersonSqlCompute
{
    [Key]
    [Identity]
    public Int32 PersonID { get; set; }

    // The FullName property mapps to the the compute "LastName +'.' + FirstName".
    [SqlCompute("LastName +'.' + FirstName")]
    public String FullName { get; set; }

    public DateTime? HireDate { get; set; }

    public DateTime? EnrollmentDate { get; set; }

    public String Discriminator { get; set; }

}

NotMappedAttribute

Denotes that a property should be excluded from database mapping. (NotMappedAttribute comes from .NET)

[Table("Department", Schema = "dbo")]
public class DepartmentNotMapped
{
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

    // The TemporaryValue property should be excluded from database mapping.
    [NotMapped]
    public Int32 TemporaryValue { get; set; }

}

Querying from a single table source

You can used either Column or SqlColumn attribute to specify the column. You can also omit these attributes if the property name of the model class is the same as the column name of the database table (case is insensitive).

// You can use either Table or FromTable attribute to specify the data source
[Table("Department", Schema = "dbo")]
public class Department
{
    //The attribute SqlColumn is omitted from the properties below. 
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }
}

Querying from multiple table sources

When you need to map some columns from the primary table, the SqlColumn attribute and Column attribute can be omitted if the property name of the model class is the same as the column name of the database table (case is insensitive).

[Table("Course", Schema = "dbo")]
[FromTable("StudentGrade", Schema = "dbo")]
[SqlWhere("Course.CourseID", "StudentGrade.CourseID")]
public class CourseFromTable
{
    // This property maps to Course.CourseID.
    // You can also omits SqlColumn here, because Course table is the primary table, 
    // and the property name CourseID is the same as the column name of the Course table.    
    [SqlColumn("Course", column: "CourseID")]
    public Int32 CourseID { get; set; }

    // Maps to Course.Title
    [SqlColumn("Course", column: "Title")]
    public String Title { get; set; }

    // Maps to StudentGrade.Grade
    [SqlColumn("StudentGrade", column: "Grade")]
    public Decimal? Grade { get; set; }

}

Restricting Query Results

You can use the SqlWhere attribute to add search conditions. The SqlAndWhere attribute and the SqlOrWhere attribute can be used to add more search conditions.

Attributes used to restrict query results

SqlWhereAttribute

It can be used to set the search condition for the result set when a model class loads the data. Only one SqlWhere attributes can be applied to the model class. The SqlWhere attribute is equivalent to adding a WHERE clause (that defines the search condition for the result set) to the SQL SELECT statement of the model class.

You can specify the left and right parts of the search condition, and then specify the binary operator SqlBinaryOperator, which is easier to maintain. Operator is SqlBinaryOperator.Equals by default, so you can omit it.

You can also add raw SQL directly to SqlWhere attribute without distinguishing between the left and right parts. This is more flexible but less maintainable.

[FromTable("Department", Schema = "dbo")]
// The left is "DepartmentID", the right is "1", Operator is SqlBinaryOperator.Equals by default.
// You can also write raw SQL search condition instead.
// [SqlWhere("DepartmentID = 1")]
[SqlWhere("DepartmentID", "1")]
public class DepartmentSqlWhere
{
    [Key]
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator]
FROM [dbo].[Department]
WHERE ([DepartmentID] = 1)

SqlAndWhereAttribute

Sets an additional search condition on top of the existing search condition in the model class. One or more SqlAndWhere attributes can be applied to one model class. The SqlAndWhere attribute must be used together with the SqlWhere attribute. The SqlAndWhere attribute denotes that a model class should add an AND logical operator and another search condition in the WHERE clause of the SQL SELECT statement.

[FromTable("Department", Schema = "dbo")]
[SqlWhere("Budget > 1")]
// It adds another search condition "Administrator = 2".
[SqlAndWhere("Administrator = 2")]
public class DepartmentSqlAndWhere
{
    [Key]
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator]
FROM [dbo].[Department]
WHERE ([Budget] > 1
AND [Administrator] = 2)

SqlOrWhereAttribute

Sets an optional search condition on top of the existing search condition in the model class. Either condition must be met. One or more SqlOrWhere attributes can be applied to one model class. The SqlOrWhere attribute must be used together with the SqlWhere attribute. It denotes that a model class should add an OR logical operator and another search condition in the WHERE clause of the SQL SELECT statement.

Note that the OR logical operator has a lower priority than the AND logical operator. If necessary, you can add parentheses in raw SQL to ensure that the query conditions are correct.

[FromTable("Department", Schema = "dbo")]
[SqlWhere("Budget > 1")]
[SqlAndWhere("Administrator = 2")]
[SqlOrWhere("Administrator > 4")]
public class DepartmentSqlOrWhere
{
    [Key]
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator]
FROM [dbo].[Department]
WHERE ([Budget] > 1
AND [Administrator] = 2
OR [Administrator] > 4)

SqlBinaryOperator

The following operators (enumeration values of SqlBinaryOperator) can be used to test the two expressions on the left and right.

Name	Description
Equals	=
NotEquals	<>
LessThan	<
LessThanOrEquals	<=
GreaterThan	>
GreaterThanOrEquals	>=
Like	LIKE
NotLike	NOT LIKE
In	IN
NotIn	NOT IN
IsNot	IS NOT
Is	IS
EqualsAll	= ALL
NotEqualsAll	<> ALL
GreaterThanAll	> ALL
GreaterThanOrEqualsAll	>= ALL
LessThanAll	< ALL
LessThanOrEqualsAll	<= ALL
EqualsAny	= ANY
NotEqualsAny	<> ANY
GreaterThanAny	> ANY
GreaterThanOrEqualsAny	>= ANY
LessThanAny	< ANY
LessThanOrEqualsAny	<= ANY

Parameterized Queries

Defining a parameterized query in the model

The SqlParameter attribute can be used to define a parameter with name and type in the model class.

In the following example, the SqlParameter attribute defines an parameter named arg1. The SqlWhere attribute adds a Where clause using :arg1 as parameter placeholder.

The format of parameter placeholder is :ParameterName.

// Define an parameter named `arg1`.
[SqlParameter("arg1", typeof(string))]
[Table("Department", Schema = "dbo")]
// Use :arg1 as parameter placeholder in the Where clause. 
[SqlWhere("Name = :arg1")]
public class DepartmentByName
{
    [Key]
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

}

Passing in parameter value when executing SQL queries

When executing a query, there are two ways to pass in the parameter values: Passing in value and passing in ParamValue object.

Passing in the parameter value directly

Pass in the parameter values in the sequence of the SqlParameter attribute instances defined in the model.

Passing in the parameter values is more convenient when coding, but it is not maintainable.

public void PassInValueExample()
{
    // Pass in the parameter value "English" directly.
    List<DepartmentByName> departments = 
        _context.SqlModelMapper.Load<DepartmentByName>("English").ToList();
}

Passing in ParamValue object

Create ParamValue objects that contain the parameter name, type and value, and pass in the ParamValue objects. The order of the parameters can be ignored.

Passing in ParamValue objects is more maintainable.

public void PassInObjectExample()
{
    // Create an ParamValue object that contains the parameter name, type and value.
    ParamValue value1 = ParamValue.New<string>("arg1", "English");

    // Pass in the ParamValue object.
    List<DepartmentByName> departments = 
        _context.SqlModelMapper.Load<DepartmentByName>(value1).ToList();
}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

exec sp_executesql N'SELECT
 [DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator] 
FROM [dbo].[Department] 
WHERE ([Name] = @arg1)',N'@arg1 nvarchar(4000)',@arg1=N'English'

Managing Query Results

For queries that return more than one result, it's often helpful to organize those results.

Ordering Query Results

The SqlOrderBy attribute defines how a model class sorts the result set when loading data.

Ordering query results in descending order

You can pass in the raw SQL order by expression to the SqlOrderBy attribute.

[FromTable("Department", Schema = "dbo")]
[SqlOrderBy("DepartmentID desc")]
public class DepartmentDesc
{
    [Key]
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

}

The following is the T-SQL Query that is actually executed when connecting to the MS SQL Server database.

SELECT
[DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator]
FROM [dbo].[Department]
ORDER BY
[DepartmentID] DESC

Ordering results in ascending order

You can pass in the raw SQL order by expression to the SqlOrderBy attribute.

[FromTable("Department", Schema = "dbo")]
[SqlOrderBy("DepartmentID asc")]
public class DepartmentAsc
{
    [Key]
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator]
FROM [dbo].[Department]
ORDER BY
[DepartmentID] ASC

Ordering query results by more than one criteria

You can pass in an array of raw SQL order by expressions to the SqlOrderBy attribute.

[FromTable("Department", Schema = "dbo")]
[SqlOrderBy("Budget asc", "DepartmentID desc")]
public class DepartmentDescAsc
{
    [Key]
    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator]
FROM [dbo].[Department]
ORDER BY
[Budget] ASC,
[DepartmentID] DESC

Grouping Query Results

The SqlGroupBy attribute defines that a model class should divide the query result into groups of rows. And SqlHaving, SqlAndHaving and SqlOrHaving attributes restrict those groups according to search conditions.

Grouping query results without specifying search conditions

You can specify an array of raw SQL expressions to the SqlOrderBy attribute. The following example, only groups the query with "StudentID" as an expression by applying the SqlGroupBy attribute. You can also specify more raw SQL expressions as [SqlGroupBy("expresion 1", "expression 2",..., "expression N")]

[Table("StudentGrade", Schema = "dbo")]
// It specifies only one expression "StudentID" here.
[SqlGroupBy("StudentID")]
public class StudentGradeSqlGroupBy
{
    public Int32 StudentID { get; set; }

    [SqlCompute(alias: "SumGrade", expression: "Sum(Grade)")]
    public decimal? SumGrade { get; set; }

}

The following is the T-SQL query that is actually executed when connecting to the MS SQL Server database.

SELECT
[StudentID],
SUM([Grade]) AS [SumGrade]
FROM [dbo].[StudentGrade]
GROUP BY
[StudentID]

Grouping query results and specifying search conditions

The following example uses the SqlGroupBy attribute to group with StudentID. At the same time, the SqlHaving property and the SqlAndHaving property are used to specify two search conditions, to query the records, whose summary of grades is >= 30 and < 60 , from the grouped result sets.

[Table("StudentGrade", Schema = "dbo")]
[SqlGroupBy("StudentID")]
[SqlHaving("Sum(Grade)", "30", Operator = SqlBinaryOperator.GreaterThanOrEquals)]
[SqlAndHaving("Sum(Grade)", "60", Operator = SqlBinaryOperator.LessThan)]
public class StudentGradeSqlHaving
{
    public Int32 StudentID { get; set; }

    [SqlCompute(alias: "SumGrade", expression: "Sum(Grade)")]
    public decimal? SumGrade { get; set; }

}

The following is the T-SQL Query that is actually executed when connecting to the MS SQL Server database.

SELECT
 [StudentID],
SUM([Grade]) AS [SumGrade]
FROM [dbo].[StudentGrade]
GROUP BY
 [StudentID]
HAVING (SUM([Grade]) >= 30
AND SUM([Grade]) < 60)

Executing SQL Queries

Querying one value

Using Scalar method

The ModelMapper's Scalar method retrieves data from the first column, in the first row, for the specified SQL expression, according to the criteria specified in a model class.

Using the Scalar method is more flexibly.

public void ScalarExample()
{
    // Calculates the minimum budget for departments.
    decimal MinBudget = _context.SqlModelMapper
                                .Scalar<Department, decimal>("min(Budget)");

    Console.WriteLine("The minimum budget is {0}.",
        MinBudget.ToString("N0"));

    /* The code produces the following output:
            
    The minimum budget is 120,000.
    */
}

The following is the T-SQL Query that is actually executed when connecting to the MS SQL Server database.

SELECT
MIN([Budget]) 
FROM [dbo].[Department]

Using Aggregate methods

For the commonly used aggregate methods, you can use a series of methods encapsulated directly by ModelMapper instead of using the Scalar method.

When using the aggregate method, it is more maintainable than the Scalar method mentioned above.

In the following example, when using the Min method, the SQL query will be executed exactly the same as the example of Scalar method above.

public void MinExample()
{
    // Calculates the minimum budget for departments.
    decimal MinBudget = (decimal)_context.SqlModelMapper
                                            .Min<Department>("Budget");

    Console.WriteLine("The minimum budget is {0}.",
        MinBudget.ToString("N0"));

    /* The code produces the following output:
            
    The minimum budget is 120,000.
    */
}

The following is the T-SQL Query that is actually executed when connecting to the MS SQL Server database.

SELECT
MIN([Budget]) 
FROM [dbo].[Department]

Aggregate methods

Method Name	Description
Avg	Computes the average value using the specified SQL expression for the data retrieved according to the criteria specified in a model.
Count	Gets the number of rows in the result set retrieved according to the criteria specified in a model.
DistinctCount	Gets the number of all distinct values specified by the SQL expression, according to the criteria specified in a model.
Exists	Checks whether any record exists according to the retrieval criteria specified in a model.
Min	Gets the minimum value for the specified SQL expression, according to the retrieval criteria specified in a model.
Max	Gets the maximum value for the specified SQL expression, according to the retrieval criteria specified in in a model.
Sum	Computes the sum of the value using the specified SQL expression for the data retrieved according to the criteria specified in a model.

Querying one or more records

The ModelMapper's Load method retrieves data according to the SQL query defined in a TModel class, and returns an ILoadable<TModel> object which contains the result sets.

You can use ToList, ToArray or FirstOrDefault methods of the returned ILoadable<TModel> object to get data from the ILoadable<TModel> object.

public void LoadExample()
{
    // Get an SqlModelMapper object.
    var mapper = _context.SqlModelMapper;

    // Load all department records and gets a list.
    List<Department> depts = mapper.Load<Department>().ToList();
 
    Console.WriteLine("The list of departments:");
    foreach (Department dept in depts)
    {
        Console.WriteLine(dept.Name);
    }

    /* The code produces the following output:
            
    The list of departments:
    Engineering
    English
    Economics
    Mathematics
}

The following is the T-SQL Query that is actually executed when connecting to the MS SQL Server database.

SELECT
[DepartmentID],
[Name],
[Budget],
[StartDate],
[Administrator] 
FROM [dbo].[Department]