Using Fluent.NHibernate, how can I do an Insert based on a Select statement

Question

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

Answer 1

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