Converting Stored Procedure PIVOT table to LINQ query

asp.net-web-api c# entity-framework-6 linq stored-procedures

Accepted Answer

For retrieving dynamic data structures, Entity Framework is inadequate. The appropriate tool is Dapper. Essentially, it is a set of extension techniques forIDbConnection among which isQuery that gives anIEnumerable<dynamic> , wheredynamic is a thing that carries outIDictionary<string, object> . Obtaining the data is really easy:

IEnumerable<IDictionary<string, object>> result;

using (var cnn = new SqlConnection(connectionString))
{
    cnn.Open();

    var p = new DynamicParameters();
    p.Add(" @fromDate", fromDate, DbType.DateTime);
    p.Add(" @toDate", toDate, DbType.DateTime);

    result = (IEnumerable<IDictionary<string, object>>)
                cnn.Query(sql: "getMonthIsin", 
                          param: p, 
                          commandType: CommandType.StoredProcedure);
}

You now possess anIEnumerable<IDictionary<string, object>> wherein one thing (IDictionary<string, object> ) represents a row of key/value pairs from the result set of the stored procedure:

Key    Value
-----  ----
rate   100
DE011  0
...    ...
DE090  1
...    ...
DE220  0
...    ...
DE300  0

You decide where to go from here. You may, for example, change the outcome to aDataTable the following way: Dictionary to DataTable: string, object.

Dapper is, incidentally, not only straightforward, but also incredibly quick.

1
5/23/2017 11:51:47 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow