CODEWITHSUNDEEP
c#.netsqllinq
Mayur Dhingra
Mayur Dhingra

Reputation: 1577

LINQ Query to get values from database at specified time intervals

I want to fetch values from database with specific intervals in C# and need a LINQ query for that. This is what my database looks like

Id      SensorId  Value       CreatedOn   

1         8        33.5       15-11-2012  5:48 PM     
2         5        49.2       15-11-2012  5:48 PM 
3         8        33.2       15-11-2012  5:49 PM
4         5        48.5       15-11-2012  5:49 PM
5         8        31.8       15-11-2012  5:50 PM  
6         5        42.5       15-11-2012  5:50 PM  
7         8        36.5       15-11-2012  5:51 PM  
8         5        46.5       15-11-2012  5:51 PM  
9         8        39.2       15-11-2012  5:52 PM  
10        5        44.4       15-11-2012  5:52 PM  
11        8        36.5       15-11-2012  5:53 PM  
12        5        46.5       15-11-2012  5:53 PM  
13        8        39.2       15-11-2012  5:54 PM  
14        5        44.4       15-11-2012  5:54 PM  
..        .        ....       ...................

The interval is in minutes. So, if the interval is 10 minutes, then we need the values at 5:48, 5:58, 6:08 and so on...

This is what I have tried

while (startDateTime <= endDateTime)
            {
               var fetchIndex =
                    fullValues.Where(
                        item =>
                        item.CreatedOn >= startDateTime &&
                        item.CreatedOn < startDateTime.AddMinutes(1)).Select(
                            item => item.FetchIndex).FirstOrDefault();
                if (fetchIndex != 0)
                {
                    var temp = fullValues.Where(item => item.FetchIndex == fetchIndex).ToList();
                    result = result.Union(temp);
                }

                startDateTime = startDateTime.AddMinutes(interval);
            }

Since the while loop iterates through the table, it takes a lot of time to get these values.

Is there any way of getting the data in a single query?

Upvotes: 0

Views: 1537

Answers (2)

Ivan Golović
Ivan Golović

Reputation: 8832

Following approach creates a list requiredTimes which holds all of the relevant datetimes between startDateTime and endDateTime (datetimes in 10 minute intervals between start and end datetimes). After such list is created the fullValues is inner joined on it. The result is an IEnumerable with datetimes from fullValues that satisfy the interval condition. It should work fast since creating such list is quick and the Join method is an inner join which should also work fast, try it:

        DateTime startDateTime = DateTime.Parse("15-11-2012 5:48 PM");
        DateTime endDateTime = DateTime.Parse("16-11-2012 5:58 PM");

        List<DateTime> requiredTimes = new List<DateTime>();

        DateTime dt = startDateTime;
        requiredTimes.Add(dt);
        while (dt <= endDateTime)
        {
            dt = dt.AddMinutes(10);
            requiredTimes.Add(dt);
        }

        var result = fullValues.Join(
            requiredTimes,
            fv => fv.CreatedOn,
            rt => rt,
            (fv, rt) => fv);

Upvotes: 2

Mhd. Yasseen
Mhd. Yasseen

Reputation: 1037

from x in table
where   x.CreatedOn >= startDateTime &&
        x.CreatedOn <= endDateTime &&
        (x.CreatedOn.Minute % 10) == 0
select x

this will give you 1:00,1:10,1:20

The 10 represents your interval and 0 is your offset.

Upvotes: 2

Related Questions