IDbConnection问题选择vs存在

Question

我很难理解我的存在查询失败的原因。

这是SS4.0.22 sqlite32中的一个问题，在预发行版4.0.23中，它工作得很好。向下滚动寻找解决方案

我有三个表，令牌表、ServiceInstance表和映射表TokenServiceInstance：

[Alias("Token")]
public class Token
{
    [AutoIncrement]
    [Alias("Id")]
    [IgnoreDataMember]
    public int Id { get; set; }

    [References(typeof(Customer))]
    [Alias("CustomerId")]
    [IgnoreDataMember]
    public int CustomerId { get; set; }

    [Index]
    [Alias("TokenString")]
    public string TokenString { get; set; }

    [Alias("MasterTokenId")]
    [IgnoreDataMember]
    public int MasterTokenId { get; set; }

}

[Alias("ServiceInstance")]
public class ServiceInstance
{
    [AutoIncrement]
    [Alias("Id")]
    [IgnoreDataMember]
    public int Id { get; set; }

    public string ServiceName { get; set; }
}

[Alias("TokenServicesInstance")]
public class TokenServicesInstance
{
    [AutoIncrement]
    [Alias("Id")]
    public int Id { get; set; }

    [References(typeof(ServiceInstance))]
    [Alias("ServiceInstanceId")]
    public int ServiceInstanceId { get; set; }

    [References(typeof(Token))]
    [Alias("TokenId")]
    public int TokenId { get; set; }

    [Alias("Parameters")]
    public Dictionary<string, string> Parameters { get; set; }
}

我想做一个简单的查询，以找出令牌和ServiceInstance是否映射为特定的令牌TokenString和ServiceInstance中的特定ServiceName。

我有一个SqlExpression：

SqlExpression<TokenServicesInstance> expr = db.From<TokenServicesInstance>();
        expr.Join<TokenServicesInstance, Entities.Token>()
            .Join<TokenServicesInstance, Entities.ServiceInstance>()
            .Where<Entities.Token>(token => token.TokenString == tokenString)
            .And<Entities.ServiceInstance>(si => si.ServiceName == serviceName);

如果我从调试器中的表达式获取SQL：

SELECT "TokenServicesInstance"."Id", "TokenServicesInstance"."ServiceInstanceId", "TokenServicesInstance"."TokenId", "TokenServicesInstance"."Parameters"
FROM "TokenServicesInstance" 
INNER JOIN "Token"  ON ("Token"."Id" = "TokenServicesInstance"."TokenId") 
INNER JOIN "ServiceInstance"  ON ("ServiceInstance"."Id" = "TokenServicesInstance"."ServiceInstanceId")
WHERE ("Token"."TokenString" = 'B') AND ("ServiceInstance"."ServiceName" = 'A')

当我在Select中使用表达式时，它会生成行(如果有的话)：

db.Select<TokenServicesInstance>(expr);

但是我对行并不感兴趣，只是如果有行的话，所以我想使用现有的-方法，但是这会抛出一个异常，使用完全相同的表达式。

db.Exists<TokenServicesInstance>(expr)

例外细节：

An exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll but was not handled in user code 
Additional information: SQL logic error or missing database
no such column: Token.TokenString

有任何想法，为什么选择工作良好，并存在抛出异常？我的问题怎么样，看上去对吗？

溶液

作为对神话的回答的后续，我尝试使用Servicestack的预发行版4.0.23代替了我的4.0.22版本--现在代码没有任何问题。

窥探生成的SQL显示，0.22版本在计算行数时省略了查询的联接部分。

4.0.23：

DEBUG: SELECT COUNT(*) 
FROM "TokenServicesInstance" INNER JOIN "Token"  ON 
("Token"."Id" = "TokenServicesInstance"."TokenId") INNER JOIN "ServiceInstance"  ON 
("ServiceInstance"."Id" = "TokenServicesInstance"."ServiceInstanceId")
WHERE ("Token"."TokenString" = 'A') AND ("ServiceInstance"."ServiceName" = 'B')

4.0.22

DEBUG: SELECT COUNT(*) FROM "TokenServicesInstance" WHERE ("Token"."TokenString" = 'A')     AND ("ServiceInstance"."ServiceName" = 'B')

完整测试代码：

using System.Collections.Generic;
using System.Data;
using System.Runtime.Serialization;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.Logging;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;
using WAAPI.ApiToken.Data.OrmLite.Entities;

namespace Db.Tests
{
public class ExistsTests
{
    [Test]
    public void Can_Select_exists_on_JOIN_expression()
    {
        LogManager.LogFactory = new ConsoleLogFactory();
        var tokenString = "A";
        var serviceName = "B";

        var factory = SetupFactory();

        using(var db = factory.OpenDbConnection())
        { 
            db.DropAndCreateTable<Token>();
            db.DropAndCreateTable<ServiceInstance>();
            db.DropAndCreateTable<TokenServicesInstance>();

            var q = db.From<TokenServicesInstance>();
            q.Join<TokenServicesInstance, Token>()
                .Join<TokenServicesInstance, ServiceInstance>()
                .Where<Token>(token => token.TokenString == tokenString)
                .And<ServiceInstance>(si => si.ServiceName == serviceName);

            Assert.That(db.Select(q).Count, Is.EqualTo(0));
            Assert.That(db.Exists(q), Is.False);
        }
    }

    private OrmLiteConnectionFactory SetupFactory()
    {
        var factory = new OrmLiteConnectionFactory(":memory:", SqliteOrmLiteDialectProvider.Instance);
        using (var db = factory.OpenDbConnection())
        {
            CreateMissingTables(db);
        }
        return factory;
    }

    protected void CreateMissingTables(IDbConnection db)
    {
        db.CreateTable<Token>();
        db.CreateTable<ServiceInstance>();
        db.CreateTable<TokenServicesInstance>();
    }
}
}

namespace WAAPI.ApiToken.Data.OrmLite.Entities
{
[Alias("TokenServicesInstance")]
public class TokenServicesInstance
{
    [AutoIncrement]
    [Alias("Id")]
    public int Id { get; set; }

    [References(typeof(ServiceInstance))]
    [Alias("ServiceInstanceId")]
    public int ServiceInstanceId { get; set; }

    [References(typeof(Token))]
    [Alias("TokenId")]
    public int TokenId { get; set; }

    [Alias("Parameters")]
    public Dictionary<string, string> Parameters { get; set; }
}

[Alias("ServiceInstance")]
public class ServiceInstance
{
    public string ServiceName { get; set; }

    [AutoIncrement]
    [Alias("Id")]
    [IgnoreDataMember]
    public int Id { get; set; }
}

[Alias("Token")]
public class Token
{
    [AutoIncrement]
    [Alias("Id")]
    [IgnoreDataMember]
    public int Id { get; set; }

    [Index]
    public string TokenString { get; set; }

    [Alias("MasterTokenId")]
    [IgnoreDataMember]
    public int MasterTokenId { get; set; }

    [ServiceStack.DataAnnotations.Ignore]
    public bool IsMasterToken
    {
        get { return MasterTokenId == 0; }
    }
}
}

Answer 1

您的示例在此测试中没有问题：

[Test]
public void Can_Select_exists_on_JOIN_expression()
{
    LogManager.LogFactory = new ConsoleLogFactory();
    var tokenString = "A";
    var serviceName = "B";

    db.DropAndCreateTable<Token>();
    db.DropAndCreateTable<ServiceInstance>();
    db.DropAndCreateTable<TokenServicesInstance>();

    var q = db.From<TokenServicesInstance>();
    q.Join<TokenServicesInstance, Token>()
        .Join<TokenServicesInstance, ServiceInstance>()
        .Where<Token>(token => token.TokenString == tokenString)
        .And<ServiceInstance>(si => si.ServiceName == serviceName);

    Assert.That(db.Select(q).Count, Is.EqualTo(0));
    Assert.That(db.Exists(q), Is.False);
}

您能不能确保您已经更新到了最新版本的ServiceStack。如果这仍然是一个问题，您可以尝试最新版本的ServiceStack on MyGet，这将确保我们运行相同的版本。

如果仍然存在问题，可以使用生成的SQL更新您的问题，可以用它打印OrmLite：

LogManager.LogFactory = new ConsoleLogFactory();