Why does Linq (Expression<Func<T,bool>>) generate incorrect Where clauses in a generic class?

Question

I have a simple interface for reference data items:

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

I had hoped to be able to have a ReferenceItemRepository<T> where T : IReferenceItem that was able to select any such item from the database, like this:

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

However, supposing I use an implementation of IReferenceItem called Market and a ReferenceItemRepository<Market>, this call generates SQL like this:

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

So, it's correctly resolving the name of the table and its columns, but the Where clause is turning into "Name" = "Name", which causes it to return all rows in this table.

If I do the same thing with, say, a MarketRepository non-generic class:

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

I get the right SQL:

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

Is this a bug in ServiceStack.OrmLite (I tested with 3.9.49), or am I doing something wrong, or is this just not possible given OrmLite's implementation?

Edit:

This appears to be an issue specific to the use of a Linq expression; it works properly if I switch the statement to the following:

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

Another Edit:

It also works if I pass IReferenceItem item into my repo method instead of T item. But this does not work:

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

Note that you need to have more than one item in this table in order for the failure to be apparent, and the record you're looking for has to be not the first one that would be returned when querying all records; otherwise you'll retrieve the one you were looking for out of sheer chance.

Answer 1

I haven't tested Select method I use SelectParam and everything works perfectly. Below I put my generic repository pattern with OrmLite(Maybe it will help you) - It works great with Specification pattern.

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);
    }

EDIT: Ok I made example. Code isn't perfect but I had only 10 minut break in work. If you want to execute this code then: 1) create console application project 2) add reference to ServiceStack.OrmLite - I used the nuget and there is 3.9.49.0 version. I hope it will help you.

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; }
}