How to Aggregate below table using linq query
Date tagname value
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 54.73497
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(5) 3.417564
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 94.82829
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 15.08091
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(5) 3.6422
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 5.078211
06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 68.00956
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(5) 94.6864
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 32.43211
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 65.16206
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(5) 81.18947
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 4.419947
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 95.77668
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(5) 10.43907
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 79.12902
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 62.20364
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(5) 97.43433
06-06-2018 14:30:00 Poll.Registers Block 0.310-PT-304_(4) 25.74978
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(5) 50.49747
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(4) 65.33123
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(4) 18.90912
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(5) 55.9916
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(4) 23.86106
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(4) 18.72116
06-06-2018 14:45:00 Poll.Registers Block 0.310-PT-304_(5) 0.06596069
expected result should be like
only distinct tagname per timeslot with avegate value into that slot,
e.g. output should be
06-06-2018 14:15:00 Poll.Register Block 0.310-PT-304(4) "Value should be avg"
06-06-2018 14:15:00 Poll.Register Block 0.310-PT-304(5) "Value should be avg"
06-06-2018 14:30:00 Poll.Register Block 0.310-PT-304(4) "Value should be avg"
06-06-2018 14:30:00 Poll.Register Block 0.310-PT-304(5) "Value should be avg"
06-06-2018 14:45:00 Poll.Register Block 0.310-PT-304(4) "Value should be avg"
06-06-2018 14:45:00 Poll.Register Block 0.310-PT-304(5) "Value should be avg"
--- EDIT -- These are couple of queries I've tried..
var g = (from x in ObjEntities.TagDataValues
where x.ValueDateTime >= FromDate && x.ValueDateTime <= EndDate && MachineName.Contains(x.MachineName) && ServerName.Contains(x.ServerName) && Tags.Contains(x.TagName)
select new
{
TagName = x.TagName,
MachineName = x.MachineName,
ServerName = x.ServerName,
TagValue = x.TagValue,
DtTime = x.ValueDateTime
}).ToList().GroupBy(cd => new
{
date = cd.DtTime.AddSeconds(-cd.DtTime.Second).AddMinutes(-cd.DtTime.Minute % 15),
tagname = cd.TagName,
tagvalue = cd.TagValue
}).ToList().Select(o => new
{
Date = o.Key.date,
tagname = o.Key.tagname,
value = o.Key.tagvalue
}).ToList().GroupBy(tr => tr.tagname).Select(x => new {
TagName = x.Key,
Value = x.Average(gf => gf.value),
Date = x.Select(gf => gf.Date).Distinct()
}).ToList();
var g = (from x in ObjEntities.TagDataValues
where x.ValueDateTime >= FromDate && x.ValueDateTime <= EndDate && MachineName.Contains(x.MachineName) && ServerName.Contains(x.ServerName) && Tags.Contains(x.TagName)
select new
{
TagName = x.TagName,
MachineName = x.MachineName,
ServerName = x.ServerName,
TagValue = x.TagValue,
DtTime = x.ValueDateTime
}).ToList().GroupBy(cd => new
{
date = cd.DtTime.AddSeconds(-cd.DtTime.Second).AddMinutes(-cd.DtTime.Minute % 15),
tagname = cd.TagName,
tagvalue = cd.TagValue
}).ToList().Select(o => new
{
Date = o.Key.date,
tagname = o.Key.tagname,
value = o.Key.tagvalue
}).ToList().GroupBy(tr => new {
TagName = tr.tagname,
DateTime = tr.Date
} ).Select(x => new {
TagName = x.Key,
Value = x.Average(gf => gf.value),
Date = x.Key.DateTime
}).ToList();
I'm new to LINQ, so not able to pull proper result..
I've written sql query for same, its give perfect result as of
declare @StartDate DateTime = CAST('06/06/2018 14:26:56' AS datetime) declare @EndDate DateTime = CAST('06/06/2018 14:32:56' AS datetime) SELECT CONVERT(nvarchar, ValueDateTime, 113) as MINUTE , avg(TagDataValue.TagValue) Value, TagName FROM TagDataValue WHERE ValueDateTime >= @StartDate AND ValueDateTime <= @EndDate and TagName in ('Poll.Registers Block 0.310-PT-304_(4)','Poll.Registers Block
0.310-PT-304_(5)') GROUP BY CONVERT(nvarchar, ValueDateTime, 113) , TagName
This query gives perfect result but since code is in linq not able to use this query or able to convert the same.
Any help pls..
Grouping by DateTimes is always tricky as many functions available at C# are not available when generating SQL code and probably can cause problems.
As I have no way to reproduce your environment, I have created some example based only linq. You probably will have to recreated using TruncateTime so that it runs fully at DB level.
var g = entityList
.Where(x => x.ValueDateTime >= FromDate && x.ValueDateTime <= ToDate && MachineNames.Contains(x.MachineName))
.Select(x => new
{
quarterDateTime = x.ValueDateTime
.AddSeconds(-x.ValueDateTime.Second)
.AddMinutes(-x.ValueDateTime.Minute % 15),
x.MachineName,
x.Value
})
.GroupBy( x => new { x.quarterDateTime, x.MachineName })
.Select( x => new { x.Key.quarterDateTime, x.Key.MachineName, AverageValue = x.Average(p => p.Value) })
.OrderBy( x => x.quarterDateTime )
.ToList();
I would say, that probably the 1st Select and GroupBy can be merged, but I left separated for better readibility.
Refer to this for more information about TruncateTime.