NHibernate QueryOver with Fetch resulting multiple sql queries and db hits

by

I would say, that this behaviour is what we should expect. Let’s have a scenario, in which we have in the system 2 users and 2 roles

User1 - Role1 // has only Role1
User2 - Role1 // now we see that Role2 has more then User1
User2 - Role2

Let’s say, that the first query, will retrieve only User1 and its many-to-many relation Role1. What we have in the ISession at the moment is only User1, so the set of Users for Role1 is incomplete (we cannot reuse objects loaded into ISession at the moment). But how should one know where we are? That all data loaded for Role1 is or is not in the session?

New query, loading the data for Role1 must be issued. And this way, we can at the end have dosens of these queries…

What I see as the best solution (I am using it in almost all scenarios) is the batch-size setting: 19.1.5. Using batch fetching

HasManyToMany(x => x.UsersInRole)
  ...
  .BatchSize(25)

Mark all your collection maps with .BatchSize(25) and do that even for the Class map as well. That will cause more then 1 SQL Script, but at the end not more then 1 + (2-4) dependent on the batch-size and page size.

More Related Contents:

  1. NHibernate – Many to Many Query using Junction/Joiner Table
  2. Call the maximum from SQL table to textbox
  3. Store value in Yes/No field in Access using C#
  4. How to save leap date ’02/29/2006′ to sql server database? [duplicate]
  5. how to get date difference between two dates using DateDiff Excluding the weekends [duplicate]
  6. Fastest Way of Inserting in Entity Framework
  7. C# Data Connections Best Practice?
  8. Execute Insert command and return inserted Id in Sql
  9. Is there a good LINQ way to do a cartesian product?
  10. Adding multiple parameterized variables to a database in c#
  11. Read SQL Table into C# DataTable
  12. LINQ to SQL – Left Outer Join with multiple join conditions
  13. Difference between Parameters.Add(string, object) and Parameters.AddWithValue
  14. How can I log the generated SQL from DbContext.SaveChanges() in my Program? [duplicate]
  15. SQL injection on INSERT
  16. Minimal and correct way to map one-to-many with NHibernate
  17. SqlConnection.Close() inside using statement
  18. The C# using statement, SQL, and SqlConnection
  19. How to tell Fluent NHibernate not to map a class property
  20. C# constructing parameter query SQL – LIKE %
  21. Generate class from database table
  22. ASP.NET ODBC Query with parameters
  23. What is passing parameters to SQL and why do I need it?
  24. The variable name ‘@’ has already been declared. Variable names must be unique within a query batch or stored procedure
  25. Calling a SQL User-defined function in a LINQ query
  26. Enabling Foreign key constraints in SQLite
  27. no overload for method “ToString” takes 1 arguments
  28. Mapping columns in a DataTable to a SQL table with SqlBulkCopy
  29. Querying data using Entity Framework from dynamically created table
  30. SQL connection string for microsoft access 2010 .accdb

Leave a Comment