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;
On Tuesday, July 31, 2012 1:44:14 AM UTC-6, Jochen Jonckheere wrote:
>
> 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.
>
>
> On Monday, July 30, 2012 10:08:47 PM UTC+2, Jason Meckley wrote:
>>
>> +1 for Darren. this is an ETL operation, ORM is not the right tool for
>> this context.
>>
>> On Monday, July 30, 2012 12:37:32 PM UTC-4, Darren Kopp wrote:
>>>
>>> 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.
>>>
>>> On Monday, July 30, 2012 8:31:15 AM UTC-6, Jochen Jonckheere wrote:
>>>>
>>>> Unfortunately the number of records can be very high (+100k), a single
>>>> query is a must or maybe multiple queries like batching per 1000.
>>>>
>>>>
>>>> On Monday, July 30, 2012 3:55:34 PM UTC+2, Jason Meckley wrote:
>>>>>
>>>>> 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.
>>>>>
>>>>> On Monday, July 30, 2012 9:02:19 AM UTC-4, Jochen Jonckheere wrote:
>>>>>>
>>>>>> 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
>>>>>>
>>>>>>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/XWD1cY_bj8EJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
