How to return dynamic number of columns using Entity Framework

user_1048588 0
Viewed 165

Currently, I have the following EntityFramework query to populate a report:

        var reporte = ObtieneMarcaciones().GroupBy(m => m.CenterName).Select(m => new
        {
            Center = string.IsNullOrEmpty(m.Key) ? "(sin centro de costo)" : m.Key,
            Services = m.Count()
        });

ObtieneMarcaciones() method returns the result of a SQL Server query, this way:

        marcaciones.Select(m => new DatoMarcacion()
        {
            CenterId = m.CentroCostoId,
            CenterName = m.CentroCostoNombre,
            ServiceId = m.ServicioId,
            ServiceName = m.Servicio.ServicioNombre
        });

Where marcaciones is just a SQL Server table (an EF6 entity).

The result of ObtieneMarcaciones(...) is, for example,

  CenterId         CenterName          ServiceId        ServiceName
      1               Name1                2              Service2
      1               Name1                2              Service2
      1               Name1                2              Service2
      1               Name1                3              Service3
      1               Name1                3              Service3

As you can see in my first code, I am grouping by CenterName and counting ServiceId so, the final report is:

   Center           Services
    Name1              5

Of course I am showing only one Center. In the real scenario, there are a lot of them.

Well, so far, so good. Now I need to separate service counts in different columns, to have a final report something like this:

   Centers           Service2     Service3      Total
    Name1                3            2           5

It is something like generating the Service columns dynamically. Of course, in real scenario, I don't know how many services exist.

How can I do it so that the query is ran and returned in the SQL Server engine? It should be done without calling ToList first.

entity-framework-6 grouping sql-server sql
3 Answers

Any query that you run on SQL Server must have a stable fixed schema. It means that you technically can't write a query that would return dynamic number of columns. The quantity and names of the columns must be defined. The number of rows that the query returns can vary, but the number of columns can't.

In your case you can write a query that returns a summary for each Center and Service and then use some client-side reporting tool like Crystal Reports to pivot/transpose this result set into a table to present to the user and calculate totals.

SELECT
    CenterName
    ,ServiceName
    ,COUNT(*) AS ServiceCount
FROM
    YourTable
GROUP BY
    CenterName
    ,ServiceName
;

This would return

+------------+-------------+--------------+
| CenterName | ServiceName | ServiceCount |
+------------+-------------+--------------+
| Name1      | Service2    | 3            |
+------------+-------------+--------------+
| Name1      | Service3    | 2            |
+------------+-------------+--------------+
| Name2      | Service2    | 5            |
+------------+-------------+--------------+
| Name2      | Service3    | 7            |
+------------+-------------+--------------+

Now write your own C# code or use some reporting tool in the client application to transpose this table to the representation that you need.

If you know in advance that you will have a specific fixed set of service names, then you can pivot this result set on the server - look up the PIVOT operator. If you don't know what service names can be there, then the pivot better be done on the client side.

user_41160170

For querying data of variable shape, Entity Framework is not the right tool. Use Dapper. It's basically a set of extension methods on a .Net IDbConnection and it's very easy to use.

The dynamic output of the main extension method to be used for non-statically typed data, Query, can be somewhat puzzling. Here's just one way to deal with it when calling a stored procedure accepting one integer parameter:

using Dapper;
using System.Data.SqlClient;


var p = new DynamicParameters();
p.Add("@id", 1);

using var cnn = new SqlConnection(connectionString);

var result = ((IEnumerable<IDictionary<string,object>>)cnn.Query(
    sql: "GetPivotedData", 
    param: p, 
    commandType: CommandType.StoredProcedure)).ToList();
user_8617160

Simply do Insert, CrossTab...

Set the CrossTab row to be by CenterName

Set the CrossTab column to be by ServiceName

Set the value to the Max or Min or Avg of the count (it doesn't matter because each crosstab cell would have only one row to aggregate in your particular case (the report is already aggregated)..

user_17347220
Related