changing variable used in where clause each time where clause returns true

Question

I have a database with about 3 milion records, each record has id (int, primary key), uniqueNumber(int) and updateTime(string).

uniqueNumber can belong to several records. updateTime represents the time of the day the record was updated such as 14:33:42

EDIT: the records are ordered by update time.

I want to let the user define a uniqueNumber and an interval. Then I want to query the DB to get all the records belongs to that uniqueNumber where their updateTime is equal to a variable which it's value is increasing by interval each time the where condition is true.

For example, I want something like this: (there is no DB used in this example)

    string currentTime = "12:25:00";
    int interval = 2;

    public void LinqExample()
    {
        string[] arr = new string[4] { "12:23:00", "12:26:00", "12:27:00", "12:28:00"};

        var query = from c in arr
                    where c.Replace(":", "").CompareTo(currentTime.Replace(":", "")) > 0 && Inc()
                    select c;

        // query's output is: "12:26:00", "12:28:00"
    }

    private bool Inc()
    {
        currentTime = DateTime.Parse(currentTime).AddMinutes(interval).ToLongTimeString();
        return true;
    }

is my code is the proper way to do this?

thanks!

You won't be able to directly query a database with this because it relies on local state. This type of processing would require you to pull *all the records* from the database. I suggest revisiting your requirements and schema. — Jon, Sep 20 '12 at 12:21
ok. but is it possible to change a variable's value each time the where clause is true when using linq on database without to pull all the records from the database? — adl, Sep 20 '12 at 12:27
@remi: To be able to do that the LINQ provider for your database should be able to convert `Inc()` to SQL. Not likely I'm afraid. — Martin Liversage, Sep 20 '12 at 12:29
@remi: what DBMS are you using? and have you got ability to create objects in DB? I think that it would be better to create function in your DB that returns all the records you need. — tsionyx, Sep 20 '12 at 12:43
Are the records ordered by update time? If not, what would the output be of, say, `"12:28:00", "12:26:00", "12:27:00", "12:23:00"`? Also, your description says updateTime should be equal, but your code uses greater than. Which one is it? — Risky Martin, Sep 20 '12 at 19:13
@T_12 I'm using Entity Framework with SQL Sever Express. The code I wrote it's perpose is to get all the records I need. thanks — adl, Sep 21 '12 at 11:18
@RiskyMartin good remarks. Yes, the records are ordered by update time. Second, I used greater than because the time in the DB might be a bit later than the requested time, so I want to get the closest time to the required time. thanks — adl, Sep 21 '12 at 11:18
@remi Good to know! Is there a threshhold where if the closest time is too far after the currentTime it is no longer a match? If so, how big could this threshold be in comparison to the interval? — Risky Martin, Sep 21 '12 at 17:07
@RiskyMartin sorry I didn't mention it before. I like your comments. Usually the requested time should always exist in the DB or a little after. In some cases the gap might be big however there is no threshhold. — adl, Sep 22 '12 at 18:28
@remi Aw, if there were a threshhold, I could have come up with a LINQ solution that would have at least a slim chance of working depending on what works with LINQ to Entities. But now I can't think of anything. My only other idea is use LINQ to filter as much as you can in the DB, and then filter the rest in the program. Anyways, thanks for answering everything. Hopefully my comments at least helped other people with their solutions. — Risky Martin, Sep 22 '12 at 21:22

score 1 · Answer 1 · answered Sep 20 '12 at 17:39

1

What you want is a modulo operation with an offset. The difference in minutes between the current time and the recorded time is an integer. If that integer is divisible by the interval value then you've got a match:

(Assuming Entity Framework, with linq-to-sql use SqlMethods)

var now = DateTime.Now;
var curr = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, 0);

var query = context.Table
.Where(t => t.uniqueNumber == uniqueNumber
.Where(t => t.updateTime > curr) 
.Where (t => EntityFunctions.DiffMinutes(curr, t.updateTime) % interval == 0)
.Select (t => new { t.updateTime, .... } )

answered Sep 20 '12 at 17:39

Gert Arnold

105,341
31
202
291

2

I like this idea, but it's not exactly what remi's asking for. Remi wants not the items that fall on any interval, but instead items that fall on a certain interval. – Risky Martin Sep 20 '12 at 18:32
@RiskyMartin I think you're right there. Also, come to think of it, it's about differences between times, not dates. But the main twist is the modulo, maybe that's enough for remi to work it out. Another possible problem may be the values itself. If they are stored as string it's impossible (I think) to solve this in any way that translates to SQL. Remi? – Gert Arnold Sep 20 '12 at 18:47
Anyway, following @RiskyMartin, the modulo value of the number of minutes since midnight should be used. – Gert Arnold Sep 20 '12 at 18:49
@Gert Arnold I also like your idea but I agree with Risky_Martin. Second, yes the updateTime is stored as string. – adl Sep 21 '12 at 11:24
As string :( :( Can you do anything about it? Strings will impede any effort to do any quick sorting and predicates on the field as time, because the values must be converted first, which rules out any index on the field. – Gert Arnold Sep 21 '12 at 11:44
@GertArnold you are right. I will change it from string to BigInt which will represent the time using the [unix time](http://en.wikipedia.org/wiki/Unix_time). – adl Sep 22 '12 at 18:17

score 1 · Accepted Answer · answered Sep 21 '12 at 12:26

It seems that code you want if it exists require generating SQL query with CURSOR over your table by EF. However as I quess, EF never produces code with cursors, therefore you cannot solve your problem using LINQ. IMHO the only way to do this is to write table-valued function like this

CREATE FUNCTION GetTimesByUniqueNumberWithIntervalAndCondition
(
    @interval int, @uniqueNumber int,@currentTime time
)
RETURNS 
@Times TABLE 
(
    t char(10)
)
AS
BEGIN
    DECLARE timeCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT updateTime FROM Table2 WHERE uniqueNumber = @uniqueNumber
    ORDER BY updateTime 

    DECLARE @t char(10);

    OPEN timeCursor
    FETCH NEXT FROM timeCursor INTO @t;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF 
            1 = 1 -- place your condition here
        BEGIN
            INSERT @Times(t) VALUES(@t)
            SET @currentTime = DATEADD(MINUTE,@interval,CAST(@t AS time))
        END

        FETCH NEXT FROM timeCursor INTO @t;
    END

    CLOSE timeCursor
    DEALLOCATE timeCursor

    RETURN 
END
GO

and query against this function every time you need the selection.

but pay the attention at the fact that cursors are very undesirable to use in SQL Server — tsionyx, Sep 22 '12 at 19:07
can you please explain why cursors are undesirable to use in SQL Server? — adl, Sep 22 '12 at 21:47
I found [this link](http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server) — adl, Sep 22 '12 at 21:55

changing variable used in where clause each time where clause returns true

2 Answers2