.net temp table issue

Question

I got an c# .net core project with an model called Sensor.

public class Sensor
{
    public int Id { get; set; }
    public long timestamp { get; set; }
    [Display(Name = "Country")]
    public string country { get; set; }
    [Display(Name = "Region")]
    public string region { get; set; }
    [Display(Name = "Emitter")]
    public string emitter { get; set; }
    [Display(Name = "Value")]
    public string value { get; set; }
}

i need to get the following data:

the combination off country and region (call it tag) and its occurrences. on that tag, add the emitter and count the occurrences separately.

Ex:

usa.south- 1000

usa.south.sensor01 - 700

usa.south.sensor02 - 300

canada.north- 1500

canada.north.sensor01 - 1250

canada.north.sensor02 - 250

My original idea was to use and temp table to hold the values, but its not working.

CREATE TABLE _Results (Tag Varchar(150), Total Int)

INSERT INTO[_Results] (Tag, Total)"  
SELECT country  '.'  region AS Tag, COUNT() AS Total
FROM Sensor
GROUP BY country, region

INSERT INTO[_Results](Tag, Total)
SELECT country  '.'  region  '.'  emitter AS Tag, COUNT() AS Total
FROM Sensor
GROUP BY country, region, emitter

SELECT * FROM _Results

DROP TABLE _Results

But i cant get this to work.

Can someone help me? An Memory usage solution is not available, so i got to use the SQL and the Microsoft.EntityFrameworkCore

Answer 1

No need for temporary tables

SELECT country + '.' + region AS Tag, '' as Emitter, COUNT() AS Total
FROM Sensor
GROUP BY country, region
UNION
SELECT country + '.' + region AS Tag, Emitter, COUNT() AS Total
FROM Sensor
GROUP BY country, region, emitter

Create an entity for Tag, Emitter and Total. Let's call it Statistic. Declare a dbset in your DBContext

public virtual DbSet<Statistic> Statistics { get; set; }

Run the above SQL with FromSql and AsNoTracking

    query = context.Statistics
    .FromSql(sql)
    .AsNoTracking();

sql is the above SQL query saved in a string.