Eduardo Mello Eduardo Mello - 2 months ago 21
SQL Question

Invalid Cast Exception, Stored Procedure, LINQ TO SQL

9 months later, the same problem shows up again. I've tried everything I can think of:


  • I cast as varchar(max) on the stored procedure;

  • I changed the mapping;

  • tried to find the collection linq works with, but couldn't find anywhere;



I have been running this stored procedure for some time Mapping it to my objects using LINQ to SQL, and now, all of the sudden, LINQ is trying to cast the field 'value' as double.




the error:

specified cast is not valid
System.Data.SqlClient.SqlBuffer.get_Double()
System.Data.SqlClient.SqlDataReader.GetDouble(Int32 i)
Read_Field(ObjectMaterializer`1 )
System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
CmsCsharp.BL.FieldManager.FindContentField(Int32 id_content, Int32 id_fieldType, String fieldName)





the methods

public static Field FindContentField(int id_content, int id_fieldType, string fieldName)
{
using (DbBase conn = new DbBase())
{
try
{
return conn.ClientDB.sprocFindContentField(id_content, id_fieldType, fieldName).Single();
}
catch (Exception e)
{
return null;
}
}
}



[Function(Name = "dbo.sprocFindContentField")]
public ISingleResult<Field> sprocFindContentField([Parameter(DbType="Int")] System.Nullable<int> id_content, [Parameter(DbType="Int")]System.Nullable<int> id_fieldType, [Parameter(DbType="Varchar(255)")]string fieldName)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), id_content, id_fieldType, fieldName);
return ((ISingleResult<Field>)(result.ReturnValue));
}





the field:

public class TextField : Field
{
private string _Value;

[Column(Storage="_Value", DbType="VarChar(MAX) NOT NULL", Name="value", CanBeNull=true)]
public string Value
{ //get and set }
}





the procedure:

SELECT
type = 'textField',
fieldLabel.id_fieldEditor,
fieldLabel.id_fieldType ,
fieldLabel.description,
fieldLabel.id ,
sessionField.id AS relationId,
fieldLabel.name ,
fieldText.content AS value,
fieldText.id AS valueId,
cf.id as contentRelationId
FROM
fieldLabel
INNER JOIN
sessionField
ON
sessionField.id_fieldLabel = fieldLabel.id
INNER JOIN
cms_contentField AS cf
ON
cf.id_sessionField = sessionField.id
INNER JOIN
fieldText
ON
fieldText.id = cf.id_fieldValue
INNER JOIN
fieldType
ON
fieldLabel.id_fieldType = fieldType.id
INNER JOIN
fieldTable
ON
fieldTable.id = fieldType.id_fieldTable
WHERE
cf.id_content = @id_content
AND
fieldTable.id = 1

Answer

I've changed the 9th line in the stored procedure to fieldText.content,

SELECT 
     type = 'textField',
     fieldLabel.id_fieldEditor,
     fieldLabel.id_fieldType ,
     fieldLabel.description,
     fieldLabel.id ,         
     sessionField.id  AS relationId,
     fieldLabel.name ,
     fieldText.content AS value, //this line
     fieldText.id  AS valueId,
     cf.id as contentRelationId

then I changed the mapping accordingly:

[Column(Storage="_Value", DbType="VarChar(MAX) NOT NULL", Name="content", CanBeNull=true)]
public string Value
{ //get and set }

and it worked!

Have no idea why it worked. Does the labeling (as value) could be the cause?

Thank you

Comments