1

Long story short I need to get items based on a range (high/low value), however the column/property from the database is a string and I'm not allowed to change that. So how am I supposed to compare a range?

I can't seem to find a way to convert the entity property to an int so i can compare. Of course Convert.toInt32 doesn't work either. I don't see any SqlFunctions that will help but maybe I'm overlooking something easy.

I've been playing with it, but so far no luck. This is my latest try that doesn't work:

var result = Repo.Query().Where(e => e.SerialNumber.Cast<int>().First() >= myIntLowVariable && e.SerialNumber.Cast<int>().First() <= myIntHighVariable);

Any suggestions?

Update

The user enters two SerialNumbers and I need to return all the records in that range, however SerialNumber is a string/varchar column/property.

Data - Sorry no idea how to put a table in here..

SN :: Description
1 :: Milk
2 ::Eggs
3 :: Bread
4 :: Cheese
5 :: Bacon
6 :: Yogurt
7 :: Mustard
8 :: Chicken
9 :: Pizza
10 :: Chips

MisterIsaak
  • 3,882
  • 6
  • 32
  • 55
  • What kind of string are stored in your database? is it a fixed format? some examples would help getting a better answer. – Samy Arous May 18 '12 at 00:07
  • can you post some examples of the data please – web_bod May 18 '12 at 00:09
  • What's the maximum number of digits? Would it be feasible to construct a query that looks at the length and does a comparison based on each of 1, 2, 3, ... digits? Or what about zero padding to a fixed length and comparing as strings? – Ian Mercer May 18 '12 at 02:44

2 Answers2

2

If you call ToList() on your query then you should be able to cast your string to Int32.

var result = Repo.Query().ToList().Where(e => e.StringNumberField.Cast<int>().First() >= myIntLowVariable && e.StringNumberField.Cast<int>().First() <= myIntHighVariable);

If you cannot project the entire query then you can do the following:

Create a stored procedure in you db that does the filtering. Something like this will do:

CREATE PROCEDURE spGetStuffBetween
(
    @min int,
    @max int
)
AS
BEGIN
    SELECT * from dbo.Stuff where Convert(int, SerialNumber) < @max and  Convert(int, SerialNumber) > @min;
END

Update your model from the database with the newly-added stored procedure using the EDM tools. Under 'Add', select the stored procedure you just created and select 'Finish'.

After you have updated your Model you will not be able to see the stored procedure so right-click on the design surface and select 'Model Browser'. You will be able see it under the Stored Procedures node.

Double-click on the stored procedure in the Model Browser. Here you can specify the type of collection you are returning from the stored procedure.

Now you can call the following code to get the results that you want:

int myIntLowVariable = 1000;
int myIntHighVariable = 1000000;

var results= Repo.spGetAnimalsBetweenRange(myIntLowVariable , myIntHighVariable );
Ryan Spears
  • 2,963
  • 2
  • 31
  • 39
1

Right align the strings with leading zeroes using the answer here and do the same with your input strings, now you can do an alphabetic comparison for the desired range.

You could also try the approach in this question.

Community
  • 1
  • 1
Ian Mercer
  • 38,490
  • 8
  • 97
  • 133
  • I liked the second link option the best, but I couldn't make it work. I'm def going to look into it more when I have the time. I had no idea model defined functions existed. Thanks! – MisterIsaak May 23 '12 at 15:20