Converting Stored Procedure PIVOT table to LINQ query

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

Question

My idea was to convert the currently stored procedure what I've defined before. The intention is, that I can't return the data from the database with the stored procedure. The reasons for this purpose was leaving in the query. I need to convert an existing table to a pivot table and after that I have to return the data via ASP.NET WebAPI. This pivot table is dynamically that means when the user adds a new article then it will be added in the pivot table as a column.

The normal table looks like as follows:

datum      | rate     | article
--------------------------------
2013-01-03 |  97,766..|  DE011
2013-01-05 |  90.214..|  DE090
2013-01-10 |  97,890..|  DE011
2013-01-13 |  65,023..|  DE220
2013-01-13 |  97,012..|  DE300
2013-01-15 |  97,344..|  DE300
....

the pivot table should looks like as follows:

rate | DE011 | ... | DE090 | ... | DE220 | ... | DE300
-------------------------------------------------------
100  |   0   | ... |   1   | ... |   0   | ... |   0
 98  |   2   | ... |   0   | ... |   1   | ... |   0
 97  |   0   | ... |   0   | ... |   0   | ... |   2
 90  |   0   | ... |   1   | ... |   0   | ... |   4
...

the column datum is important for the pivot table because the user have to take some input in the angular view.. in this example the user is choosing dateFrom and dateTo inputs. The rate will round the numbers how you can see in the pivot column rate.The article descriptions are in the new table column titles and the rate will count for each article.

My stored procedure works fine in SQL Server. But after the SP was imported to the EDM Model the Entity Framework defined a return type INT and that is impossible for my purposes.

Here is the code of EF:

 public virtual int getMonthIsin(Nullable<System.DateTime> fromDate, Nullable<System.DateTime> toDate)
        {
            var fromDateParameter = fromDate.HasValue ?
                new ObjectParameter("fromDate", fromDate) :
                new ObjectParameter("fromDate", typeof(System.DateTime));

            var toDateParameter = toDate.HasValue ?
                new ObjectParameter("toDate", toDate) :
                new ObjectParameter("toDate", typeof(System.DateTime));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("getMonthIsin", fromDateParameter, toDateParameter);
        }

I also have tried the .SqlQuery() in my WebAPI-Controller as follows:

return db.Database.SqlQuery<IQueryable>("EXEC getMonthIsin @fromDate, @toDate", fromDate, toDate).AsQueryable();

But it doesn't work.

Well, now the idea is to try a LINQ query and get the return values. I don't have any idea to implement this :(

Currently I've tried approximately this LINQ query:

public IQueryable getDatas(DateTime fromDate, DateTime toDate)
{
   var query = from t in db.table1
               where t.datum >= fromDate && t.datum <= toDate
               group t by t.article
                 into grp
                 select new
                 {
                    articles = grp.Key,
                    rate = grp.Select(g => g.rate),
                    total = grp.Select(g => g.rate).AsQueryable()
                 };

   return query;
}

But this isn't really the correct return. It would be very helpful when anyone can help me!! I will upvoted each good answer!

1
2
9/15/2015 11:57:46 AM

Accepted Answer

Entity Framework is not suitable for fetching dynamic data structures. Dapper is the tool to use here. It basically is a collection of extension methods on IDbConnection, one of which is Query that returns an IEnumerable<dynamic>, where dynamic is an object that implements IDictionary<string, object>. Getting the data is really simple:

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);
}

Now you have an IEnumerable<IDictionary<string, object>> in which one item (IDictionary<string, object>) represents one row of key/value pairs from the stored procedure's result set:

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

It's up to you how to go from here. You could, for instance, convert the result to a DataTable as shown here: Dictionary<string, object> to DataTable.

By the way, Dapper isn't only simple, it's blazingly fast too.

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