Conversion failed when converting the varchar value 'Zon7' to data type int

Question

I'm getting this error :

Conversion failed when converting the varchar value 'Zon7' to data type int.

establishments = GetEstablishments(waters.Select(t => ReplaceZonToEmptyString(t.IdGeographie)));

public static int ReplaceZonToEmptyString(string zoneId)
{
     zoneId.Replace("Zon", string.Empty);
     var sbInput = zoneId.Replace(" ", string.Empty);
     return Convert.ToInt32(sbInput.ToString());
 }

 public static IQueryable<Etablissement> GetEstablishments(IQueryable<int> ids)       
 {
   return from zone in entities.Zones
          where ids.Contains(zone.IdZone)
          select zone.IdBatimentNavigation.IdEtablissementNavigation;

 }

var result = establishments.ToList();

in database i have a column of type varchar the column name is 'IdGeographie' with values that start with 'Zon', something like this "ZonXXXX"

Answer 1

You are trying to compare a VARCHAR column with values of type int. One of these values will have to change and since it con not be the SQL column, it has to be the compare value:

public static string ReplaceZonToEmptyString(string zoneId)
{
    var sbInput = new StringBuilder(zoneId);
    sbInput.Replace("Zon", string.Empty);
    sbInput.Replace(" ", string.Empty);
    return sbInput.ToString();
}

public static IQueryable<Etablissement> GetEstablishments(IQueryable<string> ids)       
{
    return from zone in entities.Zones
        where ids.Contains(zone.IdZone)
        select zone.IdBatimentNavigation.IdEtablissementNavigation;
}

If the signature of the methods can't change, you have to do the conversion within GetEstablishments:

public static IQueryable<Etablissement> GetEstablishments(IQueryable<int> ids)       
{
    var textIds = ids.Select(id => id.ToString());
    return from zone in entities.Zones
        where textIds.Contains(zone.IdZone)
        select zone.IdBatimentNavigation.IdEtablissementNavigation;
}

Note that in waters.Select(t => ReplaceZonToEmptyString(t.IdGeographie)), the value waters must be a materialized list of values (i.e. not another EF query), since your replace operation can not work within Entity Framework (in either of the options).

Answer 2

What do you think the following code will do:

string original = "Hello World!";
string changed = original.Recplace("o", "xx");

original won't be changed, changed will equal "Hellxx, Wxxrld".

So you should change your ReplaceZonToEmptyString:

public static int ExtractZoneNr(string zoneId)
{
    string idWithoutZon = zoneId.Replace("Zon", string.Empty);
    string sbInput = idWithoutZon.Replace(" ", string.Empty);
    return Int32.Parse(sbInput);
}

Alas this will only work on IEnumerable, not on IQueryable

A better solution:

This solution only works if IdGeographie is the three letters "Zon" followed by the string representation of the IdZone and nothing else. So no spaces, no leading zeroes etc: "Zon4" and not "Zon004", nor "Zon 4"

You have two IQueryables one for waters and one for zones:

IQuerybale<Water> waters = ...            // probably entities.Waters
IQueryable<Zone> zones = entities.Zones

Every Zone contains an int property IdZone, and a property .IdBatimentNavigation.IdEtablissementNavigation, which seems to be of type Etablissement

Furhermore every Water has a string property GeographieId in the format "ZonX" where X is an integer number.

Now you want to query the IdBatimentNavigation.IdEtablissementNavigation of all Zones with an IdZone that equals the X part of one or more of the Waters

For example: if you have the following Waters

[0] GeographieId = "Zon10"
[1] GeographieId = "Zon42"
[2] GeographieId = "Zon7"

And you have Zones with IdZone: 4, 42, 30, 7, 22.

Then as a result you want the IdBatimentNavigation.IdEtablissementNavigation of the Zones with IdZone: 42 any 7 (in any order)

Why not join waters and zones?

var result = zones.
    .Select(zone => new
    {
        GeographieId = "Zon" + zone.IdZone.ToString(),
        Etablissement = zoneIdBatimentNavigation.IdEtablissementNavigation,
    })
    .Join(waters,                            // join with waters
       zone => zone.GeographieId,            // from zone take the GeoGraphieId
       water => water,                       // from waters take idGeographie
       (zone, water) => zone.Etablissement); // when thay match return

A better solution would be to try to remove the "Zon" part from IdGeographie, and Parse the remaining XXXX to an int. Alas there is no function that can perform the parsing AsQueryable.