为什么 Linq (表达式<Func<T，bool>>) 在泛型类中生成不正确的 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的一个bug吗?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

我还没有测试选择方法，我使用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; }
}