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.

KeithS · Accepted Answer · 2010-09-15 17:33:32Z

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.

answered Sep 15, 2010 at 17:33

KeithS

71.9k24 gold badges118 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Simon Lomax Over a year ago

Thanks Keith, I was aware that linq implementations reverse engineer expression trees and the like but I'm surprised linq to NHibernate couldn't handle this simple (as i see it) query. As I mentioned L2SQL handles it. I was hoping to avoid NHibernate's other querying strategies if I could help it. I may just have to bite the bullet and give you're suggestion a try. Thanks for your help.

Michael Maddox Over a year ago

The NH Linq provider in NH 3 translates queries into HQL, not Criteria (which is what the NH 2.1.2 Linq provider did).

KeithS Over a year ago

Good to know. My team's still using 2.x in our project.

Simon Lomax · Accepted Answer · 2010-09-15 18:36:18Z

0

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.

answered Sep 15, 2010 at 18:36

Simon Lomax

9,0128 gold badges47 silver badges79 bronze badges

Collectives™ on Stack Overflow

A simple Linq To NHibernate Query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related