Convert string to int in linq-to-sql query: how to deal with values which cannot be converted?

Question

I'm running this query against a database:

var result = (from leads in dc.T_DM_FactDemandeWebLeads
                         join  demands in dc.T_DM_DimDemandeWebs on leads.DemandeWeb_FK equals demands.DemandeWeb_PK
                         join  temps in dc.T_DM_Temps on demands.DateDemande_FK equals temps.Temps_PK
                         where leads.longitudeClient != null && (Convert.ToInt32(leads.GeolocDistanceRouteDistrib) > 1000*30) && (temps.Date > new DateTime(2000, 1, 1).Date)
                         select new Lead
                         {

                             lng = leads.longitudeClient,     

                             lat = leads.latitudeClient,

                             distance = leads.GeolocDistanceRouteDistrib

                         }).Take(1000000);

Problem: This line is buggy:

(Convert.ToInt32(leads.GeolocDistanceRouteDistrib) > 1000*30)

as leads.GeolocDistanceRouteDistrib is a VARCHAR which takes "Unknown" in some cases, leading to a format exception:

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

This problem is solved here, but the method cannot be converted to SQL.

Question: is there any way to rewrite the query so the conversion is done during the execution of the query?

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

Use the technique in this answer to create the IsNumeric sql function in your dbml file. And use it in your query.

Something like this:

join  temps in dc.T_DM_Temps on demands.DateDemande_FK equals temps.Temps_PK
where dc.ISNUMERIC(leads.GeolocDistanceRouteDistrib) // Added!
where leads.longitudeClient != null && (Convert.ToInt32(leads.GeolocDistanceRouteDistrib) > 1000*30) && (temps.Date > new DateTime(2000, 1, 1).Date)
select new Lead

edited May 23 '17 at 11:51

Community

1
1

answered May 27 '13 at 12:42

Maarten

22,527
3
47
68

Seems a good idea, but I still get the error: somehow the convert is called anyway. – L. Sanna May 27 '13 at 12:58

score 1 · Answer 2 · answered Apr 05 '18 at 05:58

Add .AsEnumerable() to your entity, then you can use all mothods

var result = (from leads in dc.T_DM_FactDemandeWebLeads.AsEnumerable()
                         join  demands in dc.T_DM_DimDemandeWebs on leads.DemandeWeb_FK equals demands.DemandeWeb_PK
                         join  temps in dc.T_DM_Temps on demands.DateDemande_FK equals temps.Temps_PK
                         where leads.longitudeClient != null && (Convert.ToInt32(leads.GeolocDistanceRouteDistrib) > 1000*30) && (temps.Date > new DateTime(2000, 1, 1).Date)
                         select new Lead
                         {
                             lng = leads.longitudeClient,     
                             lat = leads.latitudeClient,
                             distance = leads.GeolocDistanceRouteDistrib
                         }).Take(1000000);

Convert string to int in linq-to-sql query: how to deal with values which cannot be converted?

2 Answers2