Extension Methods like In don't use IConvertible

[BenKmann]

Hi, it's me again ;).

I wanted to make my life easier and I am now stopping to map two properties to one column.
So I made a custom Type which is like an Enum but i can have also a custom String, if it doesn't fit.
When i tried to use it in a query like this

db.GetTable<MasterClass>()
         .Where(m => m.Value == AnredeAuswahlliste.Herr)
         .ToList();

Linq2Db is happy. But when i try to use it in an "In"-Extension Method it breaks.

var test2 = db.GetTable<MasterClass>()
              .Where(m => m.Value.In<AnredeAuswahlliste>(AnredeAuswahlliste.Frau, AnredeAuswahlliste.Herr))
              .ToList();

Exception message: LinqToDB.LinqToDBException: 'Cannot convert value of type Tests.Linq.EagerLoadingTests+AnredeAuswahlliste to SQL'
Stack trace:
 bei LinqToDB.SqlProvider.ValueToSqlConverter.Convert(StringBuilder stringBuilder, SqlDataType dataType, Object value) in C:\git\linq2db\Source\LinqToDB\SqlProvider\ValueToSqlConverter.cs:Zeile 230.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildValue(SqlDataType dataType, Object value) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 2520.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildExpression(ISqlExpression expr, Boolean buildTableName, Boolean checkParentheses, String alias, Boolean& addAlias, Boolean throwExceptionIfTableNotFound) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 2355.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildExpression(ISqlExpression expr) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 2489.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildInListValues(InList predicate, IEnumerable values) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 2154.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildInListPredicate(ISqlPredicate predicate) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 2086.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildPredicate(ISqlPredicate predicate) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 1864.
   bei LinqToDB.DataProvider.SQLite.SQLiteSqlBuilder.BuildPredicate(ISqlPredicate predicate) in C:\git\linq2db\Source\LinqToDB\DataProvider\SQLite\SQLiteSqlBuilder.cs:Zeile 157.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildPredicate(Int32 parentPrecedence, Int32 precedence, ISqlPredicate predicate) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 2193.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildSearchCondition(SqlSearchCondition condition) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 1794.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildSearchCondition(Int32 parentPrecedence, SqlSearchCondition condition) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 1805.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildWhereSearchCondition(SelectQuery selectQuery, SqlSearchCondition condition) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 1759.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildWhereClause(SelectQuery selectQuery) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 1536.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildSelectQuery(SqlSelectStatement selectStatement) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 283.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildSql() in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 213.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildSql(Int32 commandNumber, SqlStatement statement, StringBuilder sb, Int32 indent, Boolean skipAlias) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 131.
   bei LinqToDB.SqlProvider.BasicSqlBuilder.BuildSql(Int32 commandNumber, SqlStatement statement, StringBuilder sb, Int32 startIndent) in C:\git\linq2db\Source\LinqToDB\SqlProvider\BasicSqlBuilder.cs:Zeile 92.
   bei LinqToDB.Data.DataConnection.QueryRunner.GetCommand(DataConnection dataConnection, IQueryContext query, Int32 startIndent) in C:\git\linq2db\Source\LinqToDB\Data\DataConnection.QueryRunner.cs:Zeile 189.
   bei LinqToDB.Data.DataConnection.QueryRunner.SetQuery(DataConnection dataConnection, IQueryContext queryContext, Int32 startIndent) in C:\git\linq2db\Source\LinqToDB\Data\DataConnection.QueryRunner.cs:Zeile 253.
   bei LinqToDB.Data.DataConnection.QueryRunner.SetQuery() in C:\git\linq2db\Source\LinqToDB\Data\DataConnection.QueryRunner.cs:Zeile 262.
   bei LinqToDB.Linq.QueryRunnerBase.SetCommand(Boolean clearQueryHints) in C:\git\linq2db\Source\LinqToDB\Linq\QueryRunnerBase.cs:Zeile 70.
   bei LinqToDB.Data.DataConnection.QueryRunner.ExecuteReader() in C:\git\linq2db\Source\LinqToDB\Data\DataConnection.QueryRunner.cs:Zeile 433.
   bei LinqToDB.Linq.QueryRunner.<ExecuteQuery>d__13`1.MoveNext() in C:\git\linq2db\Source\LinqToDB\Linq\QueryRunner.cs:Zeile 570.
   bei System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   bei System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   bei Tests.Linq.EagerLoadingTests.TestLoadWith(String context) in C:\git\linq2db\Tests\Tests.Playground\TestTemplate.cs:Zeile 327.

I Tried Converters, to help the Query Builder, but it didn't help.

db.MappingSchema.SetConverter<string, AnredeAuswahlliste>(s => new AnredeAuswahlliste(s));
db.MappingSchema.SetConverter<AnredeAuswahlliste, string>(s => s.Value);

It helps, when i use SetValueToSqlConverter. But then i have to Escape it myself and i think there has to be a better way.

db.MappingSchema.SetValueToSqlConverter(typeof(AnredeAuswahlliste), (builder, type, val) => builder.Append("'" + ((AnredeAuswahlliste)val).Value + "'"));

Steps to reproduce

I use you're Unittests again. (EagerLoadingTests).

[Table]
class MasterClass
{
	
	[Column] [PrimaryKey] public int Id1 { get; set; }
	[Column] [PrimaryKey] public int Id2 { get; set; }

	private string? _value;

	[Column(DataType = DataType.NVarChar)]
	public AnredeAuswahlliste? Value
	{
		get => new AnredeAuswahlliste(_value);
		set => _value = value?.Value;
	}

	[Column] public byte[]? ByteValues { get; set; }

	[Association(ThisKey = nameof(Id1), OtherKey = nameof(DetailClass.MasterId))]
	public List<DetailClass> Details { get; set; } = null!;

	[Association(QueryExpressionMethod = nameof(DetailsQueryImpl))]
	public DetailClass[] DetailsQuery { get; set; } = null!;

	static Expression<Func<MasterClass, IDataContext, IQueryable<DetailClass>>> DetailsQueryImpl()
	{
		return (m, dc) => dc.GetTable<DetailClass>().Where(d => d.MasterId == m.Id1 && d.MasterId == m.Id2 && d.DetailId % 2 == 0);
	}
}

public class AnredeAuswahlliste
{
	public string Value { get; set; }

	public const string Herr = "Herr";

	public const string Frau = "Frau";
	
	public AnredeAuswahlliste(string value)
	{
		Value = value;
	}

	public static implicit operator AnredeAuswahlliste(string value)
		=> new AnredeAuswahlliste(value);

	public static implicit operator string(AnredeAuswahlliste auswahlliste)
		=> auswahlliste.Value;

	public static bool operator ==(AnredeAuswahlliste leftSide, string rightSide)
		=> leftSide?.Value == rightSide;

	public static bool operator !=(AnredeAuswahlliste leftSide, string rightSide)
		=> leftSide?.Value != rightSide;
}

[Test]
public void TestLoadWith([IncludeDataSources(TestProvName.AllSQLite)] string context)
{
	var (masterRecords, detailRecords) = GenerateData();
	var intParam = 0;

	using (new AllowMultipleQuery())
	using (var db = GetDataContext(context))
	using (var master = db.CreateLocalTable(masterRecords))
	using (var detail = db.CreateLocalTable(detailRecords))
	{
		db.MappingSchema.SetConverter<string, AnredeAuswahlliste>(s => new AnredeAuswahlliste(s));
		db.MappingSchema.SetConverter<AnredeAuswahlliste, string>(s => s.Value);
		// db.MappingSchema.SetValueToSqlConverter(typeof(AnredeAuswahlliste), (builder, type, val) => builder.Append("'" + ((AnredeAuswahlliste)val).Value + "'"));

		var works = db.GetTable<MasterClass>()
		             .Where(m => m.Value == AnredeAuswahlliste.Frau)
		             .ToList();
		try
		{
			var crashes = db.GetTable<MasterClass>()
			              .Where(m => m.Value.In<AnredeAuswahlliste>(AnredeAuswahlliste.Frau, AnredeAuswahlliste.Herr))
			              .ToList();
		}
		catch (Exception e)
		{
			Console.WriteLine(e);
			throw;
		}
	}
}

Maybe you can give me a tip what is wrong, or what i have to map to make the queries work, even with "In"-Extensions.

Environment details

linq2db version: 3.1.5
Database Server: SqlServer
Database Provider: SqlServer
Operating system: Win10
.NET Framework: .Net Framework 4.7.2

[sdanyliv]

@BenKmann, I have prepared fix. Anyway try to use this variant: 3d0ecc2#diff-18124390e574f2fcc91588a1743965bff5ca8fbbb323543a17152b5bc9fd1762R96-R98

[BenKmann]

@sdanyliv Great work as always 👍 . I totally forgot the new HasConversion for the column mapping.
Sadly it didn't help me (Version 3.1.5), but i can wait for a patch (probably you're improvements in the PR are needed to make it work).

[BenKmann]

@sdanyliv Testet the PR-Branch and it works great.
Even better, when i implement IConvertible in AnredeAuswahlliste it even works without the HasConversion Mapping. So the Mapping is only neccessary when the Type hasn't implemented the Interface.
Thats great because we use the Type on many Columns and now i don't have to map the conversion for every Column.
All in all a great improvement! Do you know when this will get released roughly?

[sdanyliv]

I think next Thursday we will prepare Bugfix release.