A simple Linq To NHibernate Query

Question

What I'm trying to achieve must be simple but I just can't get it to work. I have a simple table that contains an id and a description field.

Something like so:

 Medium
 ----------- 
 Id     Description
 ----------
 1      Watercolour
 2      Oil
 3      Etching
 4      Other

Because a user will have the opportunity to pick any of the items from a grid. I need to issue a Linq to NHibernate query to fetch the items the user picked.

I have the following test:

[Test]
public void LinqToNHibernate_Queries_Test()
{

    InSession(session =>
    {
        _mediumRepository = new NHibernateRepository<Medium>(session);

        Action<string> insertMedium = (description) =>
        {
            var medium = new Medium
            {
                Id = 0,
                MediumDescription = description
            };

            _mediumRepository.SaveOrUpdate(medium);
        };

        insertMedium("Watercolours");
        insertMedium("Oils");
        insertMedium("Etchings");
        insertMedium("Other");

        var itemIds = new[] {  "2" , "4"  }.Aggregate((x, y) => x + "," + y); 
        //var itemIds = new ItemId[] {new ItemId {Id = 2}, new ItemId {Id = 4}};
        //var items = _mediumRepository.GetAll().Where(med => itemIds.Any(i => i == med.Id));
        var items = _mediumRepository.GetAll().Where(med => itemIds.Contains(med.Id.ToString()));

        //var mediums = _mediumRepository.GetAll();

        //var items = mediums.Join(itemIds, m => m.Id, i => i.Id, (m, i) => m); 

        Assert.AreEqual(2, items.Count());
    });

I've tried a number of different methods including the Any operator but to no avail. The error I get with this particular attempt is a System.NotSupportedException : System.String ToString()

I guess all I'm trying to do is fetch a number of records where the Id exists in a subquery. In this example I want all the records from the mediums table where the id is 2 or 4.

I'm using Linq To NHibernate v3.0 against a SQLite database for my tests and SQL 2005 in production. Incidently using the same query with Linq To SQL and SQL 2005 works.

As I said this type of query must be common place and I'm sure I'm going to have a "DOH!!!" moment when one of you clever folks provides the answer.

Answer 1

Method calls and iterative constructs are generally hit-or-miss in most Linq to ORM implementations including Linq2NH. This is because in most cases the framework doesn't actually run the lambdas; it instead examines the IL of each expression node's lambda, and reverse-engineers the condition into a SQL expression (in Linq2NH it actually converts into NH criteria) by matching based on known patterns. If the lambda includes a method, even if the server-side code has access to that method, the framework often barfs because it can't translate a method call into SQL.

I would not use Linq for this particular query; try the ICriteria setup, and add an Expression.InG(med.Id.ToString(), itemIds) to the criteria.

Answer 2

The Linq2NH implimentation was indeed barfing on the ToString method as KeithS suggested. If I change my query to

var itemIds = new[] { 2, 4 }; 
var items = _mediumRepository.GetAll().Where(med => itemIds.Contains(med.Id));
Assert.AreEqual(2, items.Count());

Then my test passes.