Dapper Strongly Typed Result Discover How to Map Result to Entity

Description

Dapper provides you with the ability to run strongly typed queries against your database. It means that you can get your query results as strongly typed objects instead of just getting back a dynamic object. In this article, we will take a look at how to use Dapper's methods to map our query results to a strongly typed list of objects.

The strongly typed result can be mapped from the following methods:

• Query
• QueryFirst
• QueryFirstOrDefault
• QuerySingle
• QuerySingleOrDefault

These extension methods can be called from any object of type IDbConnection. First, let's create a simple class that we can use to map our query results and we will call this class OrderDetail.

public class OrderDetail
{
	public int OrderDetailID { get; set; }
	public int OrderID { get; set; }
	public int ProductID { get; set; }
	public int Quantity { get; set; }
}

Next, we write our SQL query to fetch a particular OrderDetail from our database.

Example - Query

The raw SQL query can be executed using the Query method and map the result to a strongly typed list.

The following example shows how to map the Query method results to a strongly typed result type.

string sql = "SELECT TOP 10 * FROM OrderDetails";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var orderDetails = connection.Query<OrderDetail>(sql).ToList();

    Console.WriteLine(orderDetails.Count);

    FiddleHelper.WriteTable(orderDetails);
}

In the above example, the variable orderDetails would be a list of strongly typed objects.

Try it: .NET Core | .NET Framework

Example - QueryFirst

The QueryFirst method can execute a query and map the first result to a strongly typed list.

The following example shows how to map the QueryFirst method results to a strongly typed result type.

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var orderDetail = connection.QueryFirst<OrderDetail>(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable( new List<OrderDetail>() { orderDetail });
}

Try it: .NET Core | .NET Framework

Example - QueryFirstOrDefault

The QueryFirstOrDefault method can execute a query and map the first result to a strongly typed list, or a default value if the sequence contains no elements.

The following example shows how to map the QueryFirstOrDefault method results to a strongly typed result type.

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var orderDetail = connection.QueryFirstOrDefault<OrderDetail>(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}

Try it: .NET Core | .NET Framework

Example - QuerySingle

The QuerySingle method can execute a query and map the first result to a strongly typed list and throws an exception if there is not exactly one element in the sequence.

The following example shows how to map the QuerySingle method results to a strongly typed result type.

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var orderDetail = connection.QuerySingle<OrderDetail>(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}

Try it: .NET Core | .NET Framework

Example - QuerySingleOrDefault

The QuerySingleOrDefault method can execute a query and map the first result to a strongly typed list, or a default value if the sequence is empty; this method throws an exception if there is more than one element in the sequence.

The following example shows how to map the QuerySingleOrDefault method results to a strongly typed result type.

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var orderDetail = connection.QuerySingleOrDefault<OrderDetail>(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}

Try it: .NET Core | .NET Framework

Related Articles

• Dapper - Query
• Dapper - QueryFirst
• Dapper - QueryFirstOrDefault
• Dapper - QuerySingle
• Dapper - QuerySingleOrDefault