CODEWITHSUNDEEP
.netlinq
bernie2436
bernie2436

Reputation: 23901

How to safely use the .substring method in a linq where clause

I have two datetime pickers that I'm using to set a start and end date for desired records in a database. The "dates" in the database are stored as strings in the format yyyymmdd, but some dates are missing characters (ex. 2001094, which is missing the last digit on the day).

I want to write a single LINQ query that pulls the records for the relevant timespan, but if I just do this:

var = 
    from rec in datacontext 
    where rec.date.substring(0,8) is in the daterange 
    select rec

then I get out of range exceptions for the short stringed records (because they are not 8 characters long).

Right now I'm pulling in the whole table and using two lambdas to (1) remove all the records that don't begin with 8 integers and then (2) remove all of the now .substring-safe records that don't match the range. This works, but it's really ugly.

If I have two conditions in my where clause, does LINQ evaluate them sequentially? So if I say

from ... where (rec.date starts with 8 integers) AND (rec.date is in range)

will LINQ skip over records when it sees they don't match the first condition?

The only other thing I can think of (also sort of ugly) is to use .StartsWith instead of .SubString in the initial LINQ queries, and then use lambdas after the initial query to remove the out of range records. This is better than pulling in all rows like I'm doing now, but not really the elegant single query I'm looking for.

if datetimepickers are both in the same year...
var=from ... where rec.date.startswith(yyyy) 'at least I'm not pulling in everything...
var.remove(lambda to remove short date strings)
var.remove(lambda to remove out of range dates)

Upvotes: 0

Views: 4208

Answers (1)

Dessus
Dessus

Reputation: 2177

In this case, adding a check for the lenght of the string will fix the problem. In this case, LINQ will evaluate the first part of an AND condition and then stop evaluating if it fails. So if it has A && B, if A is false, then it won't evaluate B

var=from rec in datacontext where rec.date.length >= 8 && rec.date.substring(0,8) is in the daterange select rec

Upvotes: 3

Related Questions