Custom value type in where clause

Tomas

Hi,

I have a struct (see code below) that I want to use as parameter in a where clause on a sql query. I want it to be treated as a string when the query executes but I get this message: "No mapping exists from object type [MyStruct] to a known managed provider native type.". Can I somehow give a hint that I want to use the value in the member Value and that it is a string? I have tried IConvertible with no luck. It must work on Oracle, MySql and SQL Server. Also see call stack below (SQL Server).

internal struct MyStruct
{
public readonly string Value;

// more code...
}

System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen)
System.Data.SqlClient.SqlParameter.GetMetaTypeOnly()
System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
...

Thanks in advance,
Tomas

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bechir Bejaoui17y ago
you can think to render it more flexible by separating the interface from the business logic and prevent creating a solution for each particular case
Tomas17y ago
FYI
I managed to solve the problem by implementing an interface that existed in the platform I use. The company I work for has created a framework to access the database, so it is not a general solution for others, but it solves my problem :-)
Bechir Bejaoui17y ago
Thank you ;-)
Tomas17y ago
I tried the IConvertible and implemented the convertion to string, but it didn't seem to do the trick either. I will try again next week and see if I did something wrong.

I think the key is what happens in the first (last) two entries in the call stack, but what is GetMetaTypeOnly and SqlClient.MetaType? I can't find it on msdn. How do they determine the type?

System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen)
System.Data.SqlClient.SqlParameter.GetMetaTypeOnly()

Have a nice weekend...
Bechir Bejaoui17y ago
In this case you can implement the IConvertible interface to provide custom conversion in the two direections from structure to string and from string to structure

from structure to string
you implement the ToString() method

from string to structure
you implement the ToType(Type, FormatProvider) method

and you can leave the rest of the methods not implemented and catch the NoImplementedException by wrapping it within a try catch block
Tomas17y ago
Yes, I have... no luck. I'll provide more code...
My current solution is to provide the actual string instead of MyStruct, but it would be much nicer to be able to provide the struct since it makes more sense in my domain.

internal struct MyStruct
{
      public readonly string Value;

         public MyStruct(string value)
        {
            this.Value = value;
        }

        public override string ToString()
        {
            return this.Value;
        }

        public static implicit operator string(MyStruct value)
        {
            return value.Value;
        }

        public static explicit operator MyStruct(string value)
        {
            return new MyStruct(value);
        }
}
Bechir Bejaoui17y ago
Have you ever overriten the tostring method within your structure, it could help you a lot