Mixing Linq with HQL

[Jochen Jonckheere]

Hi,

For a project I'm working on I want to do some DLM stuff with NHibernate, I know not the best option. But anyway I'm stuck on complex where statement.

On the screen a user can select multiple parameters to do a search and in code this generates some linq-code that is than executed via NHibernate.
Example: _session.Query<Book>().Where(b => b.ISBN.StartsWith("10025") && b.Pages > 100 && b.Author.Name == "Jochen" && ...)

But now I want to use that Where part in combination with INSERT INTO. An insert into that only works via hql or sql.

Example: _session.CreateQuery("insert into MyBooks (Id, NumberOfPages) select b.Id, b.Pages where ... (and here should the linq part be inserted)

Is it possible to mix Linq and Hql? Or is there a better solution to this problem.

Regards,

Jochen

[Jason Meckley]

what you are asking for, no I don't think that's possible. you are mixing object graphs with sql statements and they get's real messy real fast. Instead I would manage this through objects and let NH deal with the sql statements.

how about:

  var books = session.Query<Book>().Where(...).Futures();

  foreach(var book in books)

  {

       var mybook = new MyBook(book...);

       session.Save(mybook);

  }

//tx.Commit();

Depending on your POID this will batch the write statements together. If you are using HiLo or guid as ids the saves are batched together as a single db call. If you are using Identity or another DB-centric POID than the statements are executed individually. Depending on the number of records expected to return from the books query individual insert statements may or may not be acceptable.

[Jochen Jonckheere]

Unfortunately the number of records can be very high (+100k), a single query is a must or maybe multiple queries like batching per 1000.

[Darren Kopp]

If you are dealing w/ 100k records, you should really be thinking about doing this outside of nhibernate. There are a lot of problems associated with large result sets like that (mainly memory but also easily cpu / network). Depending on how many parameters there are, it would probably not be that complicated of a stored procedure. 

[Jason Meckley]

+1 for Darren. this is an ETL operation, ORM is not the right tool for this context.

[Jochen Jonckheere]

Darren is absolutely correct, my first idea was to create a Stored Procedure and pass all the search parameters. And thus use the power of the database instead of the ORM.

But one of these parameters is an array and to pass that to a sproc I would need to use the Table Value Parameters of SQL Server 2008.

Two problems with that, first I need to support SQL Server 2005 also and an even bigger problem I needed to support Oracle as well.

In the end, here's the solution I came up with:

• Use the ORM to select only the Id's of the entities
• Do a foreach on all those Guids and insert each one in a temporary table
• Then call a Stored Procedure that will use the temp table to do all the full inserts

Step 2 has been optimized to use a for loop with a step of 1000 in combination with .SetParameterList(). This will generate one insert query every 1000 ids instead of 1000 inserts.

It's a compromise, but one I can live with for the moment.

[Darren Kopp]

Use an xml. Not as good as table parameter, but it's works well and was how we did it before table parameters. I'm sure oracle has an equivalent, but I don't know it. Example for sql server:

DECLARE @XmlData nvarchar(max) = N'<v><i>1</i><i>2</i></v>';

DECLARE @XMLDocPointer int;

/* Prepare XML */ 

EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XmlData; 

WITH Source AS (

SELECT XmlTable.Id

FROM OPENXML(@XMLDocPointer, '/v/i', 2) 

WITH (Id int '.') XmlTable 

)

INSERT INTO mybooks(id, numpages)

SELECT id, numpages

FROM  books

WHERE publisherid IN (SELECT id FROM source);

/* Clean Up */ 

EXEC sp_xml_removedocument @XMLDocPointer;