NHibernate use .where() conditions in sql search or later using LINQ?

Question

I'm still learning and trying to make my code better so I've decided to use Interface and generics to make my CRUD code better.

Until now I've had a ton of repos for each table in my database. Now I'm trying to reduce that number by using a common repository.

The problem is that some of my repositories demand use of more specific conditions for seach than simple SELECT.

For example:

///<summary>
///Get handle for stanowisko where user defined that it should be used to count time of work for this machine
///</summary>
public HandleCzujnikiModel Select(string stanowisko, bool doObliczeniaCzasuPracy)
{
    using (ISession session = NHibernateHelper.OpenSession())
    {

        return obj = session.Query<HandleCzujnikiModel>()
            .Where(c => c.Stanowisko == stanowisko && c.DoObliczeniaCzasuPracy == doObliczeniaCzasuPracy).FirstOrDefault();

    }
}

NHibernate will create an SQL out of this.

Now my question is do I make lots of repositories that inherit after my Repository where I keep common SELECT, UPDATE, etc... and have extra methods for more specific actions.

or do I use simple SELECT and later use LINQ server side to get the results I want.

Both solutions in my opinion have their pros and cons.

1) If using only the ,,main" Repository I can substantially reduce the number of classes. 2) This can however result in worse performance ? Because I have to Select more records from database and whats more use extra code with .Where clauses using LINQ to get the results.

Now my knowledge is limited therefore I have to seek answers from StackOverflow gods :)

Cheers!

Have you thought about using a Query pattern? See Ayende's blog post here http://ayende.com/blog/3955/repository-is-the-new-singleton) and review the comments regarding the Query pattern. — David Osborne, Oct 27 '14 at 08:38
The option to permit consumers of your repository to provide an `IQueryable` expression tree (e.g. GenericRepository) is a [hotly debated one](http://programmers.stackexchange.com/questions/192044/should-repositories-return-iqueryable) - advocates of it will say that expression trees are an acceptable representation of Criteria / Specification Patterns, whereas detractors will demonstrate the leaky encapsulation and the inability to adequately test the usage. — StuartLC, Oct 27 '14 at 08:44

score 2 · Accepted Answer · edited May 23 '17 at 12:20

2

As a rule of thumb: if you can do the filtering on the database, do it there.

That means in your case: if you can generate a NHibernate query it is much better to do the filtering in the NHibernate query than getting all the rows and filtering them with Linq afterwards.

If you don't have many rows or you're using a local development database it doesn't seem to be a big deal, but if the connection to the database is slow(er) and you've got many rows in the database you are going to see the performance hit it takes to send all the rows to your client.

Another thing we've experienced: the more complicated your NHibernate Objects are getting (with fields from other tables) this problem gets worse and worse.

Most likely you are goint to experience the n+1 problem if you're designing your application like this: What is SELECT N+1?

edited May 23 '17 at 12:20

Community

1
1

answered Oct 27 '14 at 08:42

bernhardrusch

11,670
12
48
59

Actually I have a bit of both: 1) tables with thousands of records and possibly getting much bigger over time 2) tables up to 100 records and probably not getting bigger. Do based on your answer I guess the best practice would be to use conditions in NHibernate for big tables with lots of records and use LINQ for small tables ? In most cases in our projects we will be using local databases (MVC applications in intranet) – cah1r Oct 27 '14 at 08:58
No, I think it is better to filter directly on the database, regardless of database size. But you are going to experience the bad effects more with the big tables. – bernhardrusch Oct 27 '14 at 09:51

NHibernate use .where() conditions in sql search or later using LINQ?

1 Answers1