Filtering while using AdoTypeConverters

[Musashi]

I have an enum on my entity. In the database, it is represented by a char. Naturally, I made an AdoTypeConverter that maps the chars to the enum. For reading, this seems to work just fine. Writing I haven't had a chance to test  yet.

But when I query, i get an exception saying "Conversion failed when converting the varchar value 'G' to data type int", for example.

Since all I have are blog posts and github code samples to go off of instead of actual documentation, I'm at a loss to why querying doesn't work.

So, to set up a full example:

Below, EnumDbValueConverter is a class that maps enums values to database values (string).

A side note: CreateLiteralSql is never called.

public enum CompanyType
{
    General,
    LawFirm
}

public class Company
{
    public int PrimaryKey { get; set; }
    public string Name { get; set; }
    public CompanyType Type { get; set; }
}

public class CompanyTypeConverter : AdoTypeConverter
{
    public override Type DefaultType
    {
        get { return typeof(CompanyType); }
    }

    public override object Read(ref DataHolder holder)
    {
        bool isNull = holder.Reader.IsDBNull(holder.Position);

        holder.NoValue = isNull;

        if (isNull)
        {
            holder.ObjectValue = EnumDbValueConverter.ConvertDbValue<CompanyType>(null);
        }
        else
        {
            holder.ObjectValue = EnumDbValueConverter.ConvertDbValue<CompanyType>(holder.Reader.GetValue(holder.Position).ToString());
        }
        return (holder.Box) ? holder.ObjectValue : null;
    }

    public override void Write(ref DataHolder holder)
    {
        holder.Parameter.DbType = System.Data.DbType.String;
        if (!holder.NoValue)
        {
            string s = EnumDbValueConverter.ConvertEnumValue((CompanyType)holder.ObjectValue);
            holder.Parameter.Size = s.Length;
            holder.Parameter.Value = s;
        }
    }

    public override bool CreateLiteralSql(ref DataHolder holder)
    {
        if (holder.NoValue)
        {
            holder.StringValue = this.NullValueSql;
            return false;
        }
        else
        {
            holder.StringValue = EnumDbValueConverter.ConvertEnumValue((CompanyType)holder.ObjectValue);
            return true;
        }
    }
}

//  this fails with the message "Telerik.OpenAccess.RT.sql.SQLException: Conversion failed when converting the varchar value 'G' to data type int."
return Repository.GetCompanies().Where(c => c.Type == CompanyType.LawFirm);

[Musashi]

Am I SOL here?

If no one can help me with the original post, maybe someone can point me to a tutorial, or some kind of documentation for AdoTypeConverters?

[Musashi]

Ok, the questions still stand, but I seem to have gotten somewhere.

I remembered one issue I had with variables vs constants in a Linq statement. On a whim I changed my query to this:

CompanyType ct = CompanyType.LawFirm;
return Repository.GetCompanies().Where(c => c.Type == ct);

I'm no longer getting the exception and I get back the results I expect.

But, the breakpoint is never hit in CreateLiteralSql. Is that normal?

Is anyone else using AdoTypeConverters?

[Musashi]

Are there any admins that can chime in on this?

I'm running into another issue where an artificial property with a converter on it is causing issues in concurrency.

A nullable datetime column is mapped to an artificial nullable datetime property with a converter that is supposed to convert nulls and dates before 1899 in the database to nulls in the application.

I think this is causing an issue where when I try to update an artificial datetime property, the optimistic concurrency verification is attempting to find the row by using 'null', when in the database it's '1899-1-1'. So the row is not found, exception is thrown.

Some code to aid in troubleshooting.

mappingConfiguration.HasArtificialPrimitiveProperty(udf.FieldName, typeof(Nullable<DateTime>)).HasFieldName(fieldName).ToColumn(udf.FieldName).WithConverter<DateTimeConverter>();

public class DateTimeConverter : AdoTypeConverter
{
    public override Type DefaultType
    {
        get { return typeof(DateTime?); }
    }

    public override object Read(ref DataHolder holder)
    {
        //check if the value is dbnull
        bool isNull = holder.Reader.IsDBNull(holder.Position);

        //set wheather or not there is a value
        holder.NoValue = isNull;

        //if the value is null, then we need to return default values
        DateTime? value = null;
        if (!isNull)
        {
            //the value is not null, so here we want to pull the value out, and convert it
            value = holder.Reader.GetValue(holder.Position) as DateTime?;

            //if the value is nullabel, or needs to be boxed, we set the ObjectValue Property of the DataHolder
            if (value.RemoveTime() <= InteumDate.EMPTY_DATE)
                value = null;
        }
        holder.ObjectValue = value;

        //now we return our value
        return value;
    }

    public override void Write(ref DataHolder holder)
    {
        //set the db type
        holder.Parameter.DbType = System.Data.DbType.DateTime;

        //if there is no value we could specify what to set the db field to
        if (holder.NoValue)
        {
            holder.Parameter.Value = InteumDate.EMPTY_DATE;
        }
        else
        {
            holder.Parameter.Value = holder.ObjectValue == null ? InteumDate.EMPTY_DATE : holder.ObjectValue as DateTime?;
        }
    }

    public override bool CreateLiteralSql(ref DataHolder holder)
    {
        //If there is no value, then we just want to query against null.
        if (holder.NoValue)
        {
            holder.StringValue = "NULL";

            //returning false indicates that no quotes are required. We want NULL instead of 'NULL'
            return false;
        }
        else
        {
            holder.DateTimeValue = (holder.ObjectValue as DateTime?).GetValueOrDefault(InteumDate.EMPTY_DATE);
            // return true indicates that quotes are needed around the value
            return true;
        }
    }
}

Exception message:

"Row not found: GenericOID@50ac1abb TECHNOL PRIMARYKEY=4210
UPDATE [TECHNOL] SET [RUPDATEDD] = ?, [UDFG6EDDT] = ? WHERE [PRIMARYKEY] = ? AND [RUPDATEDD] = ? AND [UDFG6EDDT] is null"

 The value for column UDFG6EDDT in the database is (SQL Server) '1899-1-1'. So natually, there is no row found with the given filter. How is this fixed?

[Ady]

Hello Mushashi,

In the 'Changed' optimistic concurrency mode DataAccess checks the old values of the fields that are to be updated to detect whether the object has changed in the meantime. To do this it maintains a copy of the old values internally. Since your converter does not load the values at all DataAccess has only 'NULL' as the original value. It cannot know the original value in the database.

You can switched from 'Changed' concurrency to using an explicit version field or load the value of the field without using the converter and in the property implementation is your class you can return 'NULL' if the value is before 1899.

Do get back in case you need further assistance.

Regards,
Ady
Telerik