Entity Framework - How to convert from String to Integer in query

Question

I have a legacy database with the same ID stored with multiple representations (string and integer). I need my query to join based on the key.

I know about SqlFunctions.StringConvert, but it doesn't work for my case, because the ID has 0-prefixes and the canonical representation of the number does not have string equivalence to other representations.

How can I convert my numeric string value into an integer from within my query?

Could you give an example of what you're trying to do? I don't quite understand what "canonical representation of the number does not have string equivalence to other representations" means. — Dustin Kingen, Apr 11 '13 at 15:45
e.g. "0015" is a valid string id, but "15" is its canonical representation. despite the inequivalence, i want them to compare as equal — smartcaveman, Apr 11 '13 at 15:48

score 0 · Answer 1 · answered Apr 11 '13 at 15:48

Not sure if that what you are looking for but since your numeric string may have characters in it you can extract just the numbers from string

var getNumbers =Convert.ToInt32 (from t in stringToQuery
                   where char.IsDigit(t)
                   select t).ToArray().ToString());

score 0 · Answer 2 · answered Apr 11 '13 at 15:52

May be you should try something like this:

    //example
    List<string> texts = new List<string>();
    List<int> integers = new List<int>();
    for (int j = 1; j <= 10; j++)
    {
        text.Add("00" + j.ToString());
        integers.Add(j);
    }
    var a = from t in texts
            join i in integers on Convert.ToInt32(t) equals i
            select t;

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

Can't you just use TrimStart?

~~id.TrimStart('0');~~

(edit) Actually LINQ to Entities doesn't like that so you need to try this instead to strip the leading zeros before the comparison:

user trimstart in entity framework query

edited May 23 '17 at 12:27

Community

1
1

answered Apr 11 '13 at 15:55

LouD

3,776
4
19
17

score 0 · Answer 4 · answered Apr 11 '13 at 16:03

I would create a class to store your representation.

public sealed class CanonicalInt: IEquatable<int>, IEquatable<string>
{
    private int _number;
    private string _canonical
    {
        get
        {
            return ""; //logic to turn int into format
        }
        set
        {
            _number = 0; ////logic to turn string into format
        }
    }

    public CanonicalInt(int number)
    {
        _number = number;
    }

    public CanonicalInt(string number)
    {
        _canonical = number;
    }

    public bool Equals(int other)
    {
        return _number.Equals(other);
    }

    public bool Equals(string other)
    {
        if(other == null)
            return false;

        return _canonical.Equals(other);
    }

    public static implicit operator int(CanonicalInt canonicalInt) 
    {
        return canonicalInt._number;
    }

    public static implicit operator string(CanonicalInt canonicalInt) 
    {
        return canonicalInt._canonical;
    }
}

Usage:

var number = new CanonicalInt(23);
var result = number == 23; // True

I'm afraid this cannot be translated to a query and will be performed in memory — Davi Fiamenghi, Apr 11 '13 at 16:05

score 0 · Answer 5 · answered Apr 11 '13 at 20:48

if your string always ends with canonical number them may be something like a combination of patindex,datalength and stringconvert ? (please replace simulated SqlFunctions with real, it should run in 2entities context on tables then):

string [] Strings = new string [] {"0015","0-00152","00-0012"};
int[] Integers = new int[] { 15,12};

var MixedResult = Strings.Where(s => Integers.Any(i => (PatIndex(StringConvert(i),s) + DataLength(StringConvert(i))) == DataLength(s))).ToList();

these are just simulated SqlFunctions:

private string  StringConvert(int x) 
{
    return x.ToString();
}

private int PatIndex(string pattern,string target)
{
      return target.IndexOf(pattern);
}

private int DataLength(string x) 
{
    return x.Length;
}

Entity Framework - How to convert from String to Integer in query

5 Answers5