Advanced LINQ Grouping and Projection Query for Hierarchical Data (EF 4.0 + LINQ + ASP.NET MVC + HighCharts)
asp.net-mvc-3, c#, entity-framework, highcharts, linq
Solution
As I understand, you have millions of records in a database, and you need very good performance from your application, and you have very complex structure.
Stay away from EF and I mean it. You can use it for smaller datasets, but for anything that requires superb performance from a huge query, don't even go there.
My suggestion is to write the queries manually, because if you wanted to optimize as much as you could with EF, you would practically not be able to skip much code anyway and would basically be writing SQL in LINQ way (ie specifying all SELECT statements).
If you want a little bit more specific, use Dapper. It's the "ORM" that runs StackOverflow. There's comparisons on speed here: http://code.google.com/p/dapper-dot-net/
However, if you're dead set on using EF...
Don't try to write one query. It won't happen. Instead, split them. Take a different one for each group that you need to make. It's by far the easiest way out. I believe you should already have a LINQ query for getting just one group out. Now repeat that with every other group you need, especially if you have a ton of Wheres and OrderBy's in it.
Source: a guy that works with large dataset queries daily.
Problem
Problem overview: I have database tables that describe data gathering from multiple devices using web services into centralized database. The table that stores results is deliberately de-normalized for performance: faster querying and grouping by multiple indexes. I am using Entity Framework and Linq for data access. I need to properly design Linq queries with hierarchical grouping and projection. Devices database modeling overview: Currently I have 2 types of devices 1. Rfid devices First table is RfidTag that describes tag which gathers data, 1 single RfidTag = 1 single sensor. So for example 1 single tag can get data about temperature. Second table is RfidReader that describes reading module which gather and send all data from attached tags to it. There is no restriction about quantity of attached RfidTags to single RfidReader. However one single RfidTag can be attached to one single RfidReader during reading period. ``` CREATE TABLE [dbo].[RfidTag] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), ModifiedDateTime DATETIME, -- ReaderId INT NULL, -- Reference to reader SensorTypeId INT NOT NULL, -- Reference to sensor type SensorParameters NVARCHAR(50) NULL, -- Sensor parameters Hex NVARCHAR(50) NOT NULL, -- Hex tag identifier stored as string Name NVARCHAR(50) NOT NULL, -- Tag name [Description] NVARCHAR(200) NULL, -- Tag description -- ) CREATE TABLE [dbo].[RfidReader] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), ModifiedDateTime DATETIME, -- Name NVARCHAR(20), -- Tag name [Description] NVARCHAR(200), -- Tag description SerialNumber NVARCHAR(12), -- Unique device serial name -- ) ``` Each RfidReader can be attached to specific measurement zone that is described for specific construction. ``` CREATE TABLE [dbo].[RfidReaderPlacement] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), -- ReaderId INT NOT NULL, -- Reference to Reader. MeasurementZoneId INT NOT NULL, -- Reference to Measurement Zone. StartDate DATETIME NOT NULL, -- Start date of reading. StopDate DATETIME, -- End date of reading. -- ) ``` Single data gathered by RfidTag is saved in de-normalized table. This table stores millions of records and is really heavy loaded. From this table we will be gathering data using LINQ queries. ``` CREATE TABLE [dbo].[RfidReading] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), ModifiedDateTime DATETIME, -- ReaderPlacementId INT NOT NULL, -- Reference to Rfid Reader Placement ConstructionId INT NOT NULL, -- Reference to Construction MeasurementZoneId INT NOT NULL, -- Reference to Measurement Zone ReaderId INT NOT NULL, -- Reference to Rfid Reader TagId INT NOT NULL, -- Reference to Rfid Tag SensorTypeId INT NOT NULL, -- Reference to Sensor Type ReadingDate DATETIME NOT NULL, -- Reading date Value FLOAT NOT NULL -- Measured value -- ) ``` 2. ZigBee devices First table is ZigBeeNodeProbe that describes single probe that gathers data, 1 single ZigBeeNodeProbe = 1 single sensor. So for example 1 single probe can get data about temperature. Second table is ZigBeeNode that describes single device that contains attached probes, 1 single ZigBeeNode = 3 ZigBeeNodeProbes. Third table is ZigBeeReader that describes reading module that gather and send all data from attached nodes to it. There is no restriction about quantity of attached ZigBeeNodes (that has attached ZigBeeNodeProbes) to single ZigBeeReader. However one single ZigBeeNode can be attached to one single ZigBeeReader during reading period. ``` CREATE TABLE [dbo].[ZigBeeNodeProbe] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), -- NodeId INT NULL, -- Reference to node SensorTypeId INT NOT NULL, -- Reference to sensor type SensorParameters NVARCHAR(50) NULL, -- Sensor parameters SocketNumber INT NOT NULL, -- Socket number used in parent ZigBeeNode Name NVARCHAR(50) NOT NULL, -- Node name [Description] NVARCHAR(200) NULL, -- Node description -- ) CREATE TABLE [dbo].[ZigBeeNode] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), ModifiedDateTime DATETIME, -- ReaderId INT NULL, -- Reference to reader NetworkAddress NVARCHAR(50) NOT NULL,-- Node address in ZigBee network Name NVARCHAR(50) NOT NULL, -- Given name [Description] NVARCHAR(200) NULL, -- Tag description SocketCount INT NOT NULL DEFAULT 0, -- Count of available sockets to plug in probe NodeFrequency INT NULL, -- Node frequency ) CREATE TABLE [dbo].[ZigBeeReader] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), ModifiedDateTime DATETIME, -- Name NVARCHAR(20), -- Tag name [Description] NVARCHAR(200), -- Tag description SerialNumber NVARCHAR(12), -- Unique device serial name -- ) ``` Each ZigBeeReader can be attached to specific measurement zone that is described for specific construction. ``` CREATE TABLE [dbo].[ZigBeeReaderPlacement] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), -- ReaderId INT NOT NULL, -- Reference to Reader. MeasurementZoneId INT NOT NULL, -- Reference to Measurement Zone. StartDate DATETIME NOT NULL, -- Start date of reading. StopDate DATETIME, -- End date of reading. -- ) ``` Single data gathered by ZigBeeNodeProbe is saved in de-normalized table. This table stores millions of records and is really heavy loaded. From this table we will be gathering data using LINQ queries. ``` CREATE TABLE [dbo].[ZigBeeReading] ( Id INT IDENTITY(1,1), CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(), ModifiedDateTime DATETIME, -- ReaderPlacementId INT NOT NULL, -- Reference to ZigBee Reader Placement ConstructionId INT NOT NULL, -- Reference to Construction MeasurementZoneId INT NOT NULL, -- Reference to Measurement Zone ReaderId INT NOT NULL, -- Reference to ZigBee Reader NodeId INT NOT NULL, -- Reference to ZigBee Node ProbeId INT NOT NULL, -- Reference to ZigBee Node Probe SensorTypeId INT NOT NULL, -- Reference to Sensor Type ReadingDate DATETIME NOT NULL,-- Reading date Value FLOAT NOT NULL -- Measured value -- ) ``` Quering, grouping and projection issues: As you may see above, we have 2 de-centralized tables that contains data gathered by 2 mentioned types of devices. And yes we may assume that RfidTag is in business modeling almost the same as ZigBeeNodeProbe. ``` RfidReader -- RFidTag -- RFidTag ... ZigBeeReader -- ZigBeeNode ---- ZigBeeNodeProbe ---- ZigBeeNodeProbe ---- ZigBeeNodeProbe -- ZigBeeNode ---- ZigBeeNodeProbe ---- ZigBeeNodeProbe ---- ZigBeeNodeProbe ... ``` And now we have to query both of these tables, project values from different tables into the same view models, and add specific grouping for filtering data. Common scenario: We want to create chart that will present average temperature in specific measurement zone, remember that specific measurement zone may contain multiple attached readers (both Rfid and ZigBee) sow we have to provide series of data. I am using HightStock http://www.highcharts.com/products/highstock chart that must have enabled: - Zoom for 1 month, 3 months, 6 months, etc. - From, To date period - Export features - Legend with enabling and disabling series Example of chart: http://jsfiddle.net/hNHUY/1/ Issue: How to create LINQ Grouping and Projection Query for Hierarchical Data? We need to provide common view model for both RfidReading and ZigBeeReading tables. My first attempt was with something like: ``` public class ReadingReaderDataModel { public string SeriesName { get; set; } public ReadingPeriod ReadingPeriod { get; set; } public IEnumerable<ReadingNodeDataModel> ReadingNodeDataModels { get; set; } public ReadingReaderDataModel() { ReadingNodeDataModels = new List<ReadingNodeDataModel>(); } } public class ReadingNodeDataModel { public string NodeName { get; set; } public IEnumerable<double> DataValues { get; set; } public ReadingNodeDataModel() { DataValues = new LinkedList<double>(); } } public enum ReadingPeriod { OneMonth, ThreeMonths, SixMonths, YearToDay, OneYear, All } public enum ReaderType { Rfid, ZigBee } ``` Later on I have to design LINQ projection for ASP.NET MVC controller, and here I have no idea how to create proper query that must contain average value from time period grouped by OneMonth, ThreeMonths, SixMonths, YearToDay, OneYear, All. Can anyone help me design this advanced LINQ query ? EDIT Please do not point me any "recommendations" for large datasets... This is not question about performance. This question is about complex LINQ query. I am seeking proper LINQ code and NOT answers like "that car is faster and you should try to use that car"... Code answers please... If you do not even try to understand scenario, and provide any code, do not participate... I specifically added a bounty to find a solution for LINQ, since it is advanced LINQ question as in the subject of this question.