带有类型转换器的OrmLite SQL表达式查询

本文关键字:OrmLite SQL 表达式 查询 转换器 类型 类型转换 | 更新日期: 2023-09-27 18:25:57

我正在用一个小例子测试OrmLite的一些功能,并发现在使用类型转换器时,SQL表达式API和原始SQL表达式之间存在不同的行为。

我有2个域类:

public class Account : Entity
{
    public string Name { get; set; }
    public Money Balance { get; set; }
    public Currency Currency { get; set; }
}
public class Currency : Unit
{
    public string Name { get; set; }
    public string Symbol { get; set; }
    public Currency(string format)
    {
        this.format = format;
    }
    public static implicit operator string (Currency value)
    {
        return value.Symbol;
    }
    public static implicit operator Currency(string value)
    {
        switch (value)
        {
            case "EUR":
                return Euro;
            case "USD":
                return Dollar;
            default:
                throw new NotSupportedException();
        }
    }
    private static Currency euro = new Currency("{0:n2} €") { Name = "Euro", Symbol = "EUR" };
    private static Currency dollar = new Currency("${0:n2}") { Name = "Dollar", Symbol = "USD" };
    public static Currency Euro
    {
        get
        {
            return euro;
        }
    }
    public static Currency Dollar
    {
        get
        {
            return dollar;
        }
    }
}

A型转换器:

public class CurrencyConverter : OrmLiteConverter
{
    public override string ColumnDefinition { get { return "char(3)"; } }
    public override DbType DbType { get { return DbType.StringFixedLength; } }
    public override object ToDbValue(Type fieldType, object value)
    {
        return (value as Currency).Symbol;
    }
    public override object FromDbValue(Type fieldType, object value)
    {
        Currency currency = value as string;
        return currency;
    }
}

还有一个测试:

    [TestMethod]
    public void TestMethod()
    {
        var dbFactory = new OrmLiteConnectionFactory("Data Source=Server;Initial Catalog=Test;Integrated Security=True", SqlServerDialect.Provider);
        SqlServerDialect.Provider.RegisterConverter<Money>(new MoneyConverter());
        SqlServerDialect.Provider.RegisterConverter<Currency>(new CurrencyConverter());
        using (IDbConnection db = dbFactory.Open())
        {
            db.DropAndCreateTable<Account>();
            var account = new Account()
            {
                Name = "My account",
                Currency = Currency.Dollar,
                Balance = 200
            };
            db.Save(account);
            // Raw SQL get the correct value
            var result = db.Single<Account>("Currency = @currency", new { currency = Currency.Dollar });
            // SQL expression gets null
            var otherResult = db.Single<Account>(x => x.Currency == Currency.Dollar);
        }
    }

我希望在两个Single调用中都能收到相同的结果,但SQL表达式查询似乎没有使用类型转换器来获取参数的值。

有什么方法可以用SQL表达式实现这种行为吗?

带有类型转换器的OrmLite SQL表达式查询

类型化SqlExpression默认情况下使用内联SQL,而不是参数化查询,后者调用TypeConverter的ToQuotedString() API。

相反,您可以通过设置:来启用在SQL Server和Oracle的SQL表达式中使用参数化查询的预览支持

OrmLiteConfig.UseParameterizeSqlExpressions = true;