Dapper Anonymous Result Discover How to Map Result to Dynamic Object
Description
Dapper's anonymous result is one of the key features that sets it apart from other ORMs. It allows you to map your query results to an anonymous list of objects, without having to create a separate type. These are useful for simple queries where you don't need to create a separate class to represent your data. This can save you a considerable amount of time and effort when working with complex SQL queries.
- When we query a database, we often want to map the results to a strongly typed object. However, sometimes we just want to get a list of key-value pairs or an anonymous type.
- This can be extremely useful when working with dynamic data, or when your data structure is constantly changing.
The anonymous result can be mapped from the following extension methods:
These extension methods can be called from any object of type IDbConnection
.
Example - Query
The raw SQL query can be executed using the Query method and map the result to a dynamic list.
The following example shows how to map the Query
method results to an anonymous result type.
string sql = "SELECT TOP 10 * FROM OrderDetails"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetails = connection.Query(sql); FiddleHelper.WriteTable(orderDetails); }
In this example, the variable orderDetails
would be of type dynamic
. This means that you can access the columns of the result set by their names.
Try it: .NET Core | .NET Framework
Example - QueryFirst
The QueryFirst method can execute a query and map the first result to a dynamic list.
The following example shows how to map the QueryFirst
method results to an anonymous result type.
string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetail = connection.QueryFirst(sql, new {OrderDetailID = 1}); FiddleHelper.WriteTable(orderDetail); }
Try it: .NET Core | .NET Framework
Example - QueryFirstOrDefault
The QueryFirstOrDefault method can execute a query and map the first result to a dynamic list, or a default value if the sequence contains no elements.
The following example shows how to map the QueryFirstOrDefault
method results to an anonymous result type.
string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetail = connection.QueryFirstOrDefault(sql, new {OrderDetailID = 1}); FiddleHelper.WriteTable(orderDetail); }
Try it: .NET Core | .NET Framework
Example - QuerySingle
The QuerySingle method can execute a query and map the first result to a dynamic 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 an anonymous result type.
string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetail = connection.QuerySingle(sql, new {OrderDetailID = 1}); FiddleHelper.WriteTable(orderDetail); }
Try it: .NET Core | .NET Framework
Example - QuerySingleOrDefault
The QuerySingleOrDefault method can execute a query and map the first result to a dynamic 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 an anonymous result type.
string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetail = connection.QuerySingleOrDefault(sql, new {OrderDetailID = 1}); FiddleHelper.WriteTable(orderDetail); }
Try it: .NET Core | .NET Framework
Related Articles
- Dapper - Query
- Dapper - QueryFirst
- Dapper - QueryFirstOrDefault
- Dapper - QuerySingle
- Dapper - QuerySingleOrDefault
ZZZ Projects