I have a query working properly in my code, however, I am running out of memory when it is run on a large record set because it needs to be in a single transaction. Currently, I am returning a collection using a GetMulti to then include ONE field from it to a new entity, then saving it. It seems like a huge waste to iterate through almost 5,000 records when the statement I need to execute can be done in a single call if I can get LLBLGen to work with it.

Here's what I would like to do:

INSERT INTO [Table] (Field1, Field2, Field3)
SELECT Field1, static_value, static_value FROM Other_Tables ....

Currently, my code does this:

Dim includedFields As New ExcludeIncludeFieldsList(False)
includedFields.Add(UsersFields.UserID) 'Only field I need from the Query

For Each user As UsersEntity In _userRepo.GetMulti(filter, relations, includedFields, trans)
    Dim posUser As New PositionUsersEntity() With {.PositionID = positionID, _
                                                 .UserID = user.UserID, _
                                                 .DateAddedUTC = timeStamp.ActionDate, _
                                                 .DateAddedTimeZoneLookupID = timeStamp.TimeZoneLookupID, _
                                                 .DateAddedByID = timeStamp.ActionBy, _
                                                 .IsAssigned = True}
    _posUserRepo.Save(posUser, trans) 'wrapper for the .Save method on the entity, trans is a Transaction object
Next

Is there a way to do this more efficiently? Would creating a PositionUsersCollection and saving at the end make it faster and/or use less memory?

Version 2.6, Self-Servicing

Some explanation on why this is not implemented (bulk insert): http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18851&StartAtMessage=0&#105881

If you just add entities to a collection and save the collection it would be almost the same in the sense of the queries sent to the DB server. So we always recommend to find other alternatives such programs and tools designed to do bulk record copies.

daelmo wrote:

Some explanation on why this is not implemented (bulk insert): http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18851&StartAtMessage=0&#105881

If you just add entities to a collection and save the collection it would be almost the same in the sense of the queries sent to the DB server. So we always recommend to find other alternatives such programs and tools designed to do bulk record copies.

Is there a way to get the T-SQL that will be executed by a .GetMulti based on the parameters passed to it? I could try manually building the bulk copy that way.

1. Get T-SQL generated by the .GetMulti
2. Append an "Insert Into " statement before the t-sql
3. Execute manually as SQL Command

In selfservicing this is very hard. The problem is that the method you'd need to produce a query from fields is private to DaoBase. The DynamicQueryEngine is also private, so you can't use that to call CreateSelectDQ to produce an IRetrievalQuery instance for you.

You can cheat and instantiate the DQE (DynamicQueryEngine) instance directly. Use the entity factory class of the entity you'd want to copy from to produce an entityfields collection. Fill in the filter stuff as you'd do normally. Then call CreateSelectDQ on the dqe to get an IRetrievalQuery object. Be sure to pass a valid connection object. You can obtain one from a new Transaction object. This IRetrievalQuery object is an object you can execute which will return a datareader.

You want to create an INSERT INTO, so you have to produce an action query. You can do that by using the entityfactory of the entity you want to copy TO to produce an entityfields collection and then call CreateInsertDQ on the DQE instance. This will return the INSERT statement. You then have to do some string concatenation and parameter copying, from IRetrievalQuery to IActionQuery and after that simply call the Execute method on the IActionQuery object.

Again, not easy, but not impossible.

Thanks for the suggestions. I was investigating the SqlBulkCopy class to see if I could pass an LLBLGen Transaction as the parameters to keep it all within one transaction and I came up with this implementation:

Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports SD.LLBLGen.Pro.ORMSupportClasses

Public Interface ISqlBulkCopyWrapper
    Sub WriteToServer(ByVal destTableName As String, ByVal table As DataTable, ByVal mappings As IEnumerable(Of SqlBulkCopyColumnMapping), ByRef tran As ITransaction)
End Interface

Public Class SqlBulkCopyWrapper
    Implements ISqlBulkCopyWrapper

    Public Sub WriteToServer(ByVal destTableName As String, ByVal table As DataTable, ByVal mappings As IEnumerable(Of SqlBulkCopyColumnMapping), ByRef tran As ITransaction) Implements ISqlBulkCopyWrapper.WriteToServer
        Dim copy As New SqlBulkCopy(CType(tran.ConnectionToUse, SqlConnection), _
                   SqlBulkCopyOptions.Default, _
                   CType(tran.PhysicalTransaction, SqlTransaction))

        If Not mappings Is Nothing AndAlso mappings.Count > 1 Then
            For Each map As SqlBulkCopyColumnMapping In mappings
                copy.ColumnMappings.Add(map)
            Next
        End If

        copy.DestinationTableName = destTableName
        copy.WriteToServer(table)
    End Sub

End Class

https://gist.github.com/743492

Works like a charm!!!!!

Otis wrote:

In selfservicing this is very hard. The problem is that the method you'd need to produce a query from fields is private to DaoBase. The DynamicQueryEngine is also private, so you can't use that to call CreateSelectDQ to produce an IRetrievalQuery instance for you.

You can cheat and instantiate the DQE (DynamicQueryEngine) instance directly. Use the entity factory class of the entity you'd want to copy from to produce an entityfields collection. Fill in the filter stuff as you'd do normally. Then call CreateSelectDQ on the dqe to get an IRetrievalQuery object. Be sure to pass a valid connection object. You can obtain one from a new Transaction object. This IRetrievalQuery object is an object you can execute which will return a datareader.

You want to create an INSERT INTO, so you have to produce an action query. You can do that by using the entityfactory of the entity you want to copy TO to produce an entityfields collection and then call CreateInsertDQ on the DQE instance. This will return the INSERT statement. You then have to do some string concatenation and parameter copying, from IRetrievalQuery to IActionQuery and after that simply call the Execute method on the IActionQuery object.

Again, not easy, but not impossible.

I ended up having to use this method to get the query working, however I have a question when building the IEntityCoreField and IFieldPersistenceInfo arrays to pass to the CreateSelectDQ method:

My query needs to have static values in the select part of the query:

INSERT INTO PositionUsers (PositionID, UserID, DateAddedByID, DateAddedUTC, DateAddedTimeZoneLookupID, IsAssigned)
SELECT 8, UserID, 1, '12/12/2010 13:30', 4, 1
FROM Users INNER JOIN ...

My code so far:

        Dim selectFields As New ResultsetFields(1)
        selectFields.Add(UsersFields.UserID)

        Dim createFieldsVariable As IEntityFields = (New UsersEntityFactory).CreateFields
        Dim selectPersists As New List(Of IFieldPersistenceInfo)
        Dim persist As IFieldPersistenceInfo() = createFieldsVariable.GetAsPersistenceInfoArray()
        selectPersists.Add(persist.First)

        Dim retQ As IRetrievalQuery = dqe.CreateSelectDQ(selectFields.ToArray, _
                                          selectPersists.ToArray, _
                                          trans.ConnectionToUse, _
                                          filter, _
                                          0, _
                                          Nothing, _
                                          relations, _
                                          False, _
                                          Nothing, _
                                          True, _
                                          False)

        Dim insertFields As New ResultsetFields(6)
        insertFields.Add(PositionUsersFields.PositionID)
        insertFields.Add(PositionUsersFields.UserID)
        insertFields.Add(PositionUsersFields.DateAddedByID)
        insertFields.Add(PositionUsersFields.DateAddedUTC)
        insertFields.Add(PositionUsersFields.DateAddedTimeZoneLookupID)
        insertFields.Add(PositionUsersFields.IsAssigned)

        Dim insert As IActionQuery = dqe.CreateInsertDQ(insertFields.ToArray, trans.ConnectionToUse)

        insert.Execute()

I was able to get this to return the query without the static values that need to be added to the selectFields.

Questions: 1. How do I add a static value to the select statement portion of the query? Do I need to add a new EntityField with a static value? if so, how? 2. How do I add the persistence information for these static value columns to selectPersists? Do I even need to do these 2 steps if I'm going to alter the SQL anyway?

Additionally: When I try to instantiate an IActionQuery object using the DQE, i am getting an ORMQueryConstructionException: The insert query doesn't contain any fields.

How do you specify the insert query?

Just to change focus slightly - is there a particular reason that you want to do this using LLBLGen code...? There are some things (bulk inserts being one of them) where LLBLGen is not the ideal solution, and when you get to the point where you are trying to bend the LLBLGen code to the extent that you are, that it may be worth questioning if this is the most sensible option.

Would it not be simpler just to directly code this section of your application using straight ADO.Net...?

Matt

MTrinder wrote:

Just to change focus slightly - is there a particular reason that you want to do this using LLBLGen code...? There are some things (bulk inserts being one of them) where LLBLGen is not the ideal solution, and when you get to the point where you are trying to bend the LLBLGen code to the extent that you are, that it may be worth questioning if this is the most sensible option.

Would it not be simpler just to directly code this section of your application using straight ADO.Net...?

Matt

I would love to use straight up ADO.NET for this. However, the application has become so entrenched with returning relations (from more than 22 places) for the joins to work that I cannot rebuild the query in a reasonable amount of time. I wanted to reuse the query since it is correct and already being built for me.

I think you should have entityfields created and have their IsChanged property set to true, and passed to the CreateInsertDQ() method. Hint: use the EntityField CTor to instantiate new entityFields.

Anyway, I second Matt, that you are not taking the best possible route. For instance I'd have used a Stored Procedure in his particular case.