6

I have a fairly complex piece of logic that generates a IQueryable for me that I use to return data from my database using Fluent.NHibernate.

However, I need to be able to store the results of this query back into the database (just the primary keys really but that is kind of a side issue)

How can I generate an insert statement based on the IQueryable I already have to get SQL like the example below generated:

INSERT INTO MySavedResults (Id, FirstName, LastName)
SELECT Id, FirstName, LastName FROM Member
WHERE 
FirstName = 'John' and LastName ='Snow' and ...-- more conditions
Rand Random
  • 7,300
  • 10
  • 40
  • 88
Daniel van Heerden
  • 836
  • 1
  • 7
  • 25

1 Answers1

3

I'm not sure there is a way to do this directly in NHibernate without using raw sql.

You can of course use the IQueryable you already have and generate nhibernate entities from this IQueryable. You can then take these entities and use a combination of IStatelessSession and batching to improve performance for large result sets.

Here is a good example of this:

Speed up bulk insert operations with NHibernate

I would also look at the other answers for additional speed improvements.

Edit:

Another possible way that this could be done would be to convert the linq query to raw sql using something like this:

How can I have NHibernate only generate the SQL without executing it?

Take the raw sql and prepend it with the insert statement you want to create and execute this sql using something like this:

Executing Sql statements with Fluent NHibernate

Community
  • 1
  • 1
Cole W
  • 15,123
  • 6
  • 51
  • 85
  • 1
    This is exactly what I'm doing now. Was hoping for a more effective solution. Starting to think I'm going to have to write an NHibernate extension. – Daniel van Heerden Mar 29 '16 at 04:21
  • Just a note that speed isn't my issue. It is disk IO usage killing everything else my DB is going. – Daniel van Heerden Mar 29 '16 at 04:22
  • @DanielvanHeerden Made another edit. Didn't realize you were already doing this. – Cole W Mar 29 '16 at 11:28
  • Mmm, I'll have a look. I believe the SQL generated is in a paramerized format. Which is why I haven't tried this approach before. Will give it a go and let you know. – Daniel van Heerden Mar 29 '16 at 12:09
  • I have converted this sql before from parameterized to non-parameterized for logging purposes. See here: http://stackoverflow.com/questions/11015005/execute-nhibernate-generated-prepared-statements-in-sql-server-management-studio/ – Cole W Mar 29 '16 at 12:15