How to return dynamic number of columns using Entity Framework

Question

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.

Klepht

There are many questions on this topic. Since the only answer is: can't be done, there's no answer we can refer to and people keep asking... – Mckee

Answer 1

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.

Derose

I created a SQL Server stored procedure which executes a dynamic built SQL query. That query uses PIVOT and I got the data returned exactly how I want. However, when importing that stored procedure to the entity data model, generated method returns an int. I think that is because the query run inside the SP is using exec sp_executesql @SQLQuery to get the results. Is there a way to do that in EF? so that get the results from that SP and assigned it to a dynamic generated object? (object should be dynamic because I should create properties corresponding to the service names at run time). – Klepht

That adds completely new information to your question. Having that sproc, it's very simple: use Dapper. Or if the output always has exactly the same shape: cheat, i.e. create a stored procedure that only SELECTs a dummy result. Import it. Then change it to the real thing. – Mckee

@GertArnold yes.. that is new, but it seems the question cannot be answered using Entity Framework, so in the effort to solve the problem, I am trying to let the SQL Server engine to do the job and return the results ready to be processed by EF. I did not know about Dapper so I will read about it. The output does not have always the same shape. It depends on the services present in the system. – Klepht

Then EF is out of the question. Use Dapper. – Mckee

@GertArnold Thanks Gert... it was a fact that with EF what I wanted to do is not possible. Finally I have used Dapper calling a stored procedure that returns the data the way I needed and after fighting a little with the resulting Dynamic type, I finished what I was doing and it works perfectly (and fast). If you want, write a short answer to the question. – Klepht

Answer 2

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

Mckee

Answer 3

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)..

Etana

Can you explain a little bit, please? – Klepht

Insert, CrossTab... are menu options in Crystal used to insert a CrossTab. In your case, you can insert the CrossTab in the Report Header or Report Footer section. You would then get a dialog allowing you to set the CrossTab row field(s), Column field(s), and value field(s). Follow my suggestions to populate those options. – Etana

I don't want to use Crystal Reports. – Klepht