Dapper - Query

Description

Query method is an extension method which can be called from any object of type IDbConnection. It can execute a query and map the result.

The result can be mapped to:

• Anonymous
• Strongly Typed
• Multi-Mapping (One to One)
• Multi-Mapping (One to Many)
• Multi-Type

Parameters

The following table shows different parameter of an Query method.

Name	Description
sql	The query to execute.
param	The query parameters (default = null).
transaction	The transaction to use (default = null).
buffered	True to buffer readeing the results of the query (default = true).
commandTimeout	The command timeout (default = null)
commandType	The command type (default = null)

Example - Query Anonymous

Raw SQL query can be executed using Query method and map the result to a dynamic list.

string sql = "SELECT TOP 10 * FROM OrderDetails";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{	
var orderDetail = connection.Query(sql).FirstOrDefault();
FiddleHelper.WriteTable(orderDetail);

}

Try it online

Example - Query Strongly Typed

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

string sql = "SELECT TOP 10 * FROM OrderDetails";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{			
var orderDetails = connection.Query<OrderDetail>(sql).ToList();
Console.WriteLine(orderDetails.Count);

FiddleHelper.WriteTable(orderDetails);

}

Try it online

Example - Query Multi-Mapping (One to One)

Raw SQL query can be executed using Query method and map the result to a strongly typed list with a one to one relation.

string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
        sql,
        (invoice, invoiceDetail) =>
        {
            invoice.InvoiceDetail = invoiceDetail;
            return invoice;
        },
        splitOn: "InvoiceID")
    .Distinct()
    .ToList();

}

Example - Query Multi-Mapping (One to Many)

Raw SQL query can be executed using Query method and map the result to a strongly typed list with a one to many relations.

string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{			
var orderDictionary = new Dictionary<int, Order>();
var list = connection.Query&lt;Order, OrderDetail, Order&gt;(
sql,
(order, orderDetail) =&gt;
{
	Order orderEntry;

	if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))
	{
	orderEntry = order;
	orderEntry.OrderDetails = new List&lt;OrderDetail&gt;();
	orderDictionary.Add(orderEntry.OrderID, orderEntry);
	}

	orderEntry.OrderDetails.Add(orderDetail);
	return orderEntry;
},
splitOn: &quot;OrderID&quot;)
.Distinct()
.ToList();

Console.WriteLine(list.Count);

FiddleHelper.WriteTable(list);
FiddleHelper.WriteTable(list.First().OrderDetails);

}

Try it online

Example - Query Multi-Type

Raw SQL query can be executed using Query method and map the result to a list of different types.

string sql = "SELECT * FROM Invoice;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = new List<Invoice>();

using (var reader = connection.ExecuteReader(sql))
{
    var storeInvoiceParser = reader.GetRowParser<StoreInvoice>();
    var webInvoiceParser = reader.GetRowParser<WebInvoice>();

    while (reader.Read())
    {
        Invoice invoice;

        switch ((InvoiceKind) reader.GetInt32(reader.GetOrdinal("Kind")))
        {
            case InvoiceKind.StoreInvoice:
                invoice = storeInvoiceParser(reader);
                break;
            case InvoiceKind.WebInvoice:
                invoice = webInvoiceParser(reader);
                break;
            default:
                throw new Exception(ExceptionMessage.GeneralException);
        }

        invoices.Add(invoice);
    }
}

My.Result.Show(invoices);

}