ormlite-servicestack - 为什么 Linq (Expression>) 在泛型类中生成不正确的 Where 子句？

Question

我有一个用于引用数据项的简单界面:

public interface IReferenceItem
{
    int Id { get; set; }
    string Name { get; set; }
}

我曾希望能够拥有一个ReferenceItemRepository<T> where T : IReferenceItem能够从数据库中选择任何此类项目，如下所示:

T item = db.Select<T>(s => s.Name == item.Name).FirstNonDefault<T>();

但是，假设我使用 IReferenceItem 的实现称为 Market和一个 ReferenceItemRepository<Market> ，此调用生成如下 SQL:

SELECT "MarketId" ,"Name"  
FROM "Market"
WHERE ("Name" = "Name")

因此，它正确地解析了表的名称及其列，但是 Where 子句变成了“Name”=“Name”，这导致它返回该表中的所有行。

如果我对一个 MarketRepository 做同样的事情非泛型类:

Market item = db.Select<Market>(s => s.Name == item.Name).FirstNonDefault<Market>();

我得到了正确的 SQL:

SELECT "MarketId" ,"Name"  
FROM "Market"
WHERE ("Name" = 'Chicago')

这是 ServiceStack.OrmLite 中的一个错误(我用 3.9.49 测试过)，还是我做错了什么，或者这在 OrmLite 的实现中是不可能的？

编辑:

这似乎是一个特定于使用 Linq 表达式的问题；如果我将语句切换为以下内容，它将正常工作:

T item = db.QuerySingle<T>("Name = @name", new { Name = item.Name });

另一个编辑:

如果我通过 IReferenceItem item 也有效进入我的 repo 方法而不是 T item .但这不有效:

public T Get(T item)
{
   return db.Select<T>(s => s.Name == item.Name).FirstNonDefault<T>();
}

请注意，您需要在此表中包含多个项目才能使失败变得明显，并且您要查找的记录必须不是查询所有记录时返回的第一个；否则，您将完全出于偶然取回您正在寻找的那个。

Answer 1

我还没有测试 Select 方法，我使用 SelectParam 并且一切正常。下面我将我的通用存储库模式与 OrmLite 放在一起(也许它会对您有所帮助)- 它与规范模式配合得很好。

public class GenericRepository<T> : IRepository<T>
    where T : class, new()
{
    private readonly IDbConnectionFactory dbConnectionFactory;

    public GenericRepository(IDbConnectionFactory dbConnectionFactory)
    {
        this.dbConnectionFactory = dbConnectionFactory;
    }

    public IEnumerable<T> FindAll()
    {
        return dbConnectionFactory.OpenDbConnection().Select<T>();
    }

    public IEnumerable<T> FindBy(Expression<Func<T, bool>> predicate)
    {
        return dbConnectionFactory.OpenDbConnection().SelectParam<T>(predicate);
    }

    public T FindById(int id)
    {
        return dbConnectionFactory.OpenDbConnection().GetById<T>(id);
    }

    public void Update(T entity)
    {
        dbConnectionFactory.OpenDbConnection().UpdateParam(entity);
    }

    public void Remove(T entity)
    {
        dbConnectionFactory.OpenDbConnection().Delete(entity);
    }

    public T FirstOrDefault(Expression<Func<T, bool>> predicate)
    {
        return dbConnectionFactory.OpenDbConnection().FirstOrDefault(predicate);
    }

    public void Insert(T entity)
    {
        dbConnectionFactory.OpenDbConnection().InsertParam(entity);
    }

编辑:好的，我举了例子。代码并不完美，但我只有 10 分钟的休息时间。如果要执行此代码，则:1) 创建控制台应用程序项目 2) 添加对 ServiceStack.OrmLite 的引用 - 我使用了 nuget 并且有 3.9.49.0 版本。希望对您有所帮助。

class Program
{
    static void Main(string[] args)
    {
        //connection
        var dbFactory = new OrmLiteConnectionFactory(@"Server=.\dev;Database=survey;Trusted_Connection=True;", SqlServerDialect.Provider);

        //open connection
        IDbConnection db = dbFactory.OpenDbConnection();

        db.DropAndCreateTable<Market>();

        //create item
        var newMarket = new Market() { Id = 1, Name = "Shop", LongName = "Big Shop" };

        //add item to database
        db.InsertParam<Market>(newMarket);

        //retrive using standard way
        Console.WriteLine("Standard way");
        ShowResult(db.Select<Market>(x => x.Name == "Shop"));

        //retrive using generic repository with passing predicate to repository method
        Console.WriteLine("Generic repository with passing predicate");
        var genericRepository = new GenericRepository<Market>(dbFactory);
        ShowResult(genericRepository.FindBy(x => x.Name == "Shop"));


        //retrive using generic repository with passing specyfic value to repository method
        Console.WriteLine("Generic repository with passing specyfic value to repository method");
        var genericRepositoryWithHardcodedStatments = new GenericRepositoryWithHardcodedStatments<Market>(dbFactory);
        ShowResult(genericRepositoryWithHardcodedStatments.Find("Shop"));


        Console.WriteLine("Generic repository with passing T object to repository method");
        var genericRepositoryWithPassingT = new GenericRepositoryWithPassingT<Market>(dbFactory);
        ShowResult(genericRepositoryWithPassingT.Find(new Market()
        {
            Name = "shop"
        }));
    }

    private static void ShowResult(IEnumerable<Market> markets)
    {
        foreach (var market in markets)
        {
            Console.WriteLine(value: string.Format("{0} - {1} - {2}", market.Id, market.Name, market.LongName));
        }
    }
}

public class GenericRepository<T> where T : class, new()
{
    private readonly IDbConnectionFactory dbConnectionFactory;

    public GenericRepository(IDbConnectionFactory dbConnectionFactory)
    {
        this.dbConnectionFactory = dbConnectionFactory;
    }

    public IEnumerable<T> FindBy(Expression<Func<T, bool>> predicate)
    {
        return dbConnectionFactory.OpenDbConnection().SelectParam<T>(predicate);
    }
}

public class GenericRepositoryWithHardcodedStatments<T> where T : IReferenceItem, new()
{
    private readonly IDbConnectionFactory dbConnectionFactory;

    public GenericRepositoryWithHardcodedStatments(IDbConnectionFactory dbConnectionFactory)
    {
        this.dbConnectionFactory = dbConnectionFactory;
    }

    public IEnumerable<T> Find(string name)
    {
        return dbConnectionFactory.OpenDbConnection().SelectParam<T>(x => x.Name == name);
    }
}

public class GenericRepositoryWithPassingT<T> where T : IReferenceItem, new()
{
    private readonly IDbConnectionFactory dbConnectionFactory;

    public GenericRepositoryWithPassingT(IDbConnectionFactory dbConnectionFactory)
    {
        this.dbConnectionFactory = dbConnectionFactory;
    }

    public IEnumerable<T> Find(T item)
    {
        return dbConnectionFactory.OpenDbConnection().SelectParam<T>(x => x.Name == item.Name);
    }
}


public interface IReferenceItem
{
    int Id { get; set; }
    string Name { get; set; }
}

public class Market : IReferenceItem
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string LongName { get; set; }
}