How to map a complex parameter type to the underlying db type?

[jfrank14]

I'm using InsightDB to call stored procs, and this has been working for me beautifully for many years.

Now I'm trying to add some strong type safety to my code base so that instead of having properties like int LoginId {get; set;} on my models, I use a (code generated) custom strong type to make it LoginId LoginId {get; set;}

So I have a model like

class Beer
{
   LoginId LoginId {get; set;}
   string Something {get; set;}
   ...
}

and I want to pass this to a call like ExecuteAsync and it fails with Failed to convert parameter value from a LoginId to a Int32.

This is not surprising to me, since how could it know what I mean here?

But I cannot find a way to configure this so that it works. What I want is a way to tell InsightDB that whenever you see a property of type LoginId, reach into it and use LoginId.Value instead. But I cannot find any configuration that lets me do this. The closest I've come is a custom IParameterMapper, but this isn't firing as I would expect; it's only called on the first proc I call, and then never again.

EntityFramework has the concept of a value converter, but I can't find anything like that in InsightDB.

How to proceed?!

[jonwagner]

hm.... I'm pretty sure i made it "just work" at some point.

Try this:

class LoginId {
[RecordId] public int Value;
}

it might get the automapper to treat "LoginId" as a value.

Lemme go look at the test cases and see if I have an example of that or if it helps me remember. :)

[jfrank14]

Thanks for the speedy reply! I really want to make this work.

I just tried adding the RecordId attribute to my LoginId class and get same result. Does it matter if Value is a field (as you show above) or a read-only property (which I am using)?

Looking closer, I also see an error stating InvalidCastException: Object must implement IConvertible. But implementing that would be a real pain, so I hope that's not necessary.

[jonwagner]

Aha!

It may "just work" if:

• the ID class has a constructor matching the database type.
• the ID class has a converter - here it's ToString - insight will do an implicit ID->ToString->convert to dbtype.

This is in TypeTests.cs ~lines 400-500.

There's probably a slightly better way. I'll keep looking.

#region Class By Constructor
		class FooID
		{
			public int Value;

			public FooID(int i)
			{
				Value = i;
			}

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

		class Foo
		{
			public FooID ID;
			public string Name;
		}

		[Test]
		public void TestThatSimpleClassesCanBeDeserializedByConstructor()
		{
			var data = Connection().QuerySql<Foo>("SELECT ID=1, Name='foo'", Parameters.Empty);
			ClassicAssert.AreEqual(1, data.Count);
			ClassicAssert.IsNotNull(data[0].ID);
			ClassicAssert.AreEqual(1, data[0].ID.Value);
			ClassicAssert.AreEqual("foo", data[0].Name);
		}

		[Test]
		public void TestThatSimpleClassesCanBeProcParametersByToString()
		{
			Foo f = new Foo() { ID = new FooID(1), Name = "goo" };

			var data = Connection().Query<Foo>("ConvertClassToString", f);
			ClassicAssert.AreEqual(1, data.Count);
			ClassicAssert.IsNotNull(data[0].ID);
			ClassicAssert.AreEqual(1, data[0].ID.Value);
			ClassicAssert.AreEqual("goo", data[0].Name);
		}

[jonwagner]

Apparently I made a bunch of tests and even a base class for this:

		public class ID<T>
		{
			public T Value;

			public ID(T value)
			{
				Value = value;
			}

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

		public class ObjectWithID<T>
		{
			public ID<T> ID;
		}

[jonwagner]

for better efficiency, you could make a base class IntIDBase similar to below, then just derive all of your IDs from that.

The only advantage would be eliminating the int->ToString->dbint step. This would allow insight to just detect that it's int-compatible and use that.

		class FooConvertibleID : IConvertible
		{
			public int Value;

			public FooConvertibleID(int i)
			{
				Value = i;
			}

			TypeCode IConvertible.GetTypeCode()
			{
				throw new NotImplementedException();
			}

			bool IConvertible.ToBoolean(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			byte IConvertible.ToByte(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			char IConvertible.ToChar(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			DateTime IConvertible.ToDateTime(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			decimal IConvertible.ToDecimal(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			double IConvertible.ToDouble(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			short IConvertible.ToInt16(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			int IConvertible.ToInt32(IFormatProvider provider)
			{
				return Value;
			}

			long IConvertible.ToInt64(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			sbyte IConvertible.ToSByte(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			float IConvertible.ToSingle(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			string IConvertible.ToString(IFormatProvider provider)
			{
				return Value.ToString(provider);
			}

			object IConvertible.ToType(Type conversionType, IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			ushort IConvertible.ToUInt16(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			uint IConvertible.ToUInt32(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}

			ulong IConvertible.ToUInt64(IFormatProvider provider)
			{
				throw new NotImplementedException();
			}
		}

[jonwagner]

OK that seems to be the path:

• Add a constructor for the ID class. Insight will pick that up for conversions from dbtype->ID class.
• Add one of the conversion methods above. IConvertible is faster because Insight can skip conversions, but ToString is a generic fallback Insight uses if it can't figure out how to serialize a type to the dbtype.

[jfrank14]

I do have the constructor and the ToString methods, but one difference is that I don't have a class, but a struct:

    [StronglyTypedId("int-custom")]
    public readonly partial struct LoginId
    { 
    }

Using a struct gives the class value type semantics, and StronglyTypedId is a nice tool that generates all the boilerplate to make the struct do the right thing. But does a struct not work with InsightDB here?

I suppose I could implement IConvertible here, as most of the methods aren't needed, just int. But curious if you would expect it to work anyway.

[jonwagner]

Ah. I did see some test cases around structs so I know that can work.

I haven't used StronglyTypedId but it looks great. Based on its readme, we should at least be able to add the right methods to the template for int-custom id and get that working.

Lemme go look at the struct tests and see if that has any hints on what's needed.

[jonwagner]

Two options for the construction of a structure. Construction or operator.

		struct FooStructID
		{
			public int Value;

			public FooStructID(int i)
			{
				Value = i;
			}
		}

		struct FooStructByConversionID
		{
			public int Value;

			public static explicit operator FooStructByConversionID(int i)
			{
				return new FooStructByConversionID() { Value = i };
			}
		}

As for conversion, I think either a ToString or implementing IConvertible in the stronglytypedid template should do it.

But this int-full template should include sufficient conversions to round trip the ID. Give this template a try and if we need to tune/trim/debug it or add it to your custom one, that would be the next step.

https://github.com/andrewlock/StronglyTypedId/blob/master/src/StronglyTypedIds.Templates/int-full.typedid

[jonwagner]

Yeah if that doesn't work on its own, I will try to add support for using the TypeConverter that the template brings in. It has a ToString, but not an IConvertible.

Let me know how it goes.

[jfrank14]

Will try now. Thanks again for the quick and detailed help. I LOVE InsightDB, use it everywhere, and recommend it widely, so I really want to get this working!

[jonwagner]

Well you know me...if it doesn't work I'll make it work. :)

[jfrank14]

Implementing IConvertible seems to fix the passing of parameters to stored procs. Getting my LoginId type back in a result shape also seems to work, although that must be for some other reason, because IConvertible is only used to convert to a CLR type, not the other direction.

The RecordIdAttribute does not seem to work here, although it seems like it should.

I haven't experimented with more exotic things like Return or Out params.

But I think this should hold me for now. Thanks again for the guidance, and for InsightDB.