Hello,

Is there a way to create SQL INSERT INTO .. SELECT statements with LLBLgen 4.0. I need to population the n-n relation table between two entities. I have test a previous code (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15195) but it doesn't work with LLBLgen 4.0. The "CreateQueryFromElements" has probably changed.

Thanks,

Martin

It has changed, but it's not hard to do so I think? Have you looked at the reference manual?

I have look into the onlie document but i have found nothing.

Ok, if the involved entities are not part of an inheritance hierarchy, you can try using CreateSelectDQ method instead of CreateQueryFromElements.

Thank for your answer. I have tested but CreateSelectDQ thrown a NullReferenceException.

var addedElements = adapter.InsertMissingJoinTableEntitiesDirectly<ComponentGroupEntity>(
                ComponentGroupFields.GroupId == group.GroupId, 
                new FieldCompareSetPredicate(ComponentGroupFields.ComponentId, null, ComponentFields.ComponentId, null, SetOperator.In, predicate)
                );

         public int InsertMissingJoinTableEntitiesDirectly<T>(Predicate filter1, Predicate filter2) where T : IEntity2, new()
        {
            return InsertMissingJoinTableEntitiesDirectly(new T(), filter1, filter2);
        }

        public int InsertMissingJoinTableEntitiesDirectly(IEntity2 templateEntity, Predicate filter1, Predicate filter2)
        {
            if (templateEntity == null) throw new ArgumentNullException("templateEntity");
            if (filter1 == null && filter2 == null) throw new ArgumentException("At least one filter must be specified.");

            //IFieldPersistenceInfo[] selectPersistenceInfo;
            //IRetrievalQuery selectQuery;

            var relatedFields = new List<EntityField2>();
            var joinFields = new List<EntityField2>();
            var onClause = new PredicateExpression();

            // Parse the relations and build up the on clause and lists of PK fields, FK fields
            foreach (var relation in templateEntity.GetAllRelations())
            {
                if (relation.TypeOfRelation != RelationType.ManyToOne) continue;

                var pkField = (EntityField2)relation.GetPKEntityFieldCore(0);
                var fkField = (EntityField2)relation.GetFKEntityFieldCore(0);

                onClause.AddWithAnd(fkField == pkField);

                relatedFields.Add(pkField);
                joinFields.Add(fkField);
            }

            // Simple validation
            if (relatedFields.Count != 2 || relatedFields[0].ContainingObjectName == relatedFields[1].ContainingObjectName)
            {
                throw new InvalidOperationException("Must be exactly two FK relations each with a single field PK");
            }

            // Create a cross join relation between the non-join table tables
            var crossJoinRelation = new EntityRelation();
            crossJoinRelation.AddEntityFieldPair(relatedFields[0], relatedFields[1]);
            crossJoinRelation.HintForJoins = JoinHint.Cross;

            // Create a relation between the join table and the cross join
            // (we just use the first select field since we have to supply a pair)
            IEntityRelation joinTableRelation = new EntityRelation
            {
                CustomFilter = onClause,
                CustomFilterReplacesOnClause = true,
                StartEntityIsPkSide = true,
                HintForJoins = JoinHint.Left
            };
            joinTableRelation.AddEntityFieldPair(relatedFields[0], templateEntity.PrimaryKeyFields[0]);

            // Build a RelationPredicateBucket with our joins and filter
            var filterBucket = new RelationPredicateBucket(((EntityField2)templateEntity.PrimaryKeyFields[0]) == DBNull.Value & filter1 & filter2);
            filterBucket.Relations.Add(crossJoinRelation);
            filterBucket.Relations.Add(joinTableRelation);


            // Build select list fields (must use aliases but doesn't matter what they are)
            var selectFields = new EntityFields2(relatedFields.Count);
            for (var i = 0; i < relatedFields.Count; i++)
            {
                selectFields.DefineField(relatedFields[i], i, "f" + i);
            }

            // Create the select query
            var selectQuery = CreateSelectDQ(new QueryParameters(0, 0, 0, filterBucket));

            // Since the DynamicQueryEnginer doesn't support INSERT INTO SELECT
            // we build the query directly here
            var creator = CreateDynamicQueryEngine().Creator;
            var joinFieldPersistenceInfos = GetFieldPersistenceInfos(templateEntity);

            var queryText = new StringBuilder(DynamicQueryEngineBase.InsertQueryBufferLength);
            queryText.AppendFormat("INSERT INTO {0} (", creator.CreateObjectName(joinFieldPersistenceInfos[0]));

            for (var i = 0; i < joinFields.Count; i++)
            {
                IEntityFieldCore field = joinFields[i];
                var joinFieldPersistenceInfo = joinFieldPersistenceInfos[field.FieldIndex];

                if (i > 0) queryText.Append(", ");

                queryText.AppendFormat("{0}", creator.CreateFieldNameSimple(joinFieldPersistenceInfo, field.Name));
            }

            queryText.Append(")\r\n");
            queryText.Append(selectQuery.Command.CommandText);

            selectQuery.Command.CommandText = queryText.ToString();

            return ExecuteActionQuery(new ActionQuery(selectQuery.Connection, selectQuery.Command));
        }

We will see into that code to see whether it's refactoreable to v4.x. I will back to you this weekend.

I don't really know how to use that code, but since the CreateQueryFromElements method has changed, please try the following:

void CreateQueryFromElementsCore(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket,
                                    int maxNumberOfItemsToReturn,
                                    ISortExpression sortClauses, bool allowDuplicates, IGroupByCollection groupByClause,
                                    int pageNumber, int pageSize,
                                    out IFieldPersistenceInfo[] persistenceInfo, out IRetrievalQuery selectQuery)
{
    var mi = typeof(DataAccessAdapterBase).GetMethod("CreateQueryFromElements", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Static);

    persistenceInfo = null;
    selectQuery = null;

    var parameters = new QueryParameters()
    {
        FieldsForQuery = fieldCollectionToFetch,
        FilterToUse = filterBucket.PredicateExpression,
        RelationsToUse = filterBucket.Relations,
        RowsToSkip = 0,
        RowsToTake = maxNumberOfItemsToReturn,
        SorterToUse  = sortClauses,
        AllowDuplicates = allowDuplicates,
        GroupByToUse = groupByClause        
    };


        
    mi.Invoke(this, new object[] { parameters, selectQuery  });
    persistenceInfo = parameters.FieldPersistenceInfosForQuery;
}

I got the following exception when using your code on the mi.Invoke(this, new object[] { parameters, selectQuery });

System.Reflection.TargetParameterCountException: Parameter count mismatch.
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)

The methods have been made private because it would otherwise not allow us to make changes to them (which we had to do for the async api for example).

INSERT INTO SELECT is easy to create, use the following: * first create the INSERT statement. It's a two step process: - get the persistence info for the fields: var persistenceInfoObjects = GetFieldPersistenceInfos(entityToSave, typeOfHierarchy); - then call CreateInsertDQ: CreateInsertDQ(entityToSave, persistenceInfoObjects);

• then create the SELECT statement.
  • get the persistence info for the fields: var persistenceInfoObjects = GetFieldPersistenceInfos(entityToFetch, typeOfHierarchy);
  • then call CreateSelectDQ:

IRetrievalQuery selectQuery = CreateSelectDQ(new QueryParameters(0, 0, 0) 
                                                { 
                                                    FilterToUse = predicateExpressionToUse, RelationsToUse = filter.Relations, 
                                                    AllowDuplicates=true, FieldsForQuery= fieldsToFetch, 
                                                    FieldPersistenceInfosForQuery= persistenceInfos, IsLocalCopy = true
                                                });

(see DataAccessAdapterBase source, this method: private bool FetchEntityUsingFilter(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfos, IRelationPredicateBucket filter)

for details)

Note it's key you specify the fields (and the persistence info objects per field!) in the same order as the fields in the insert statement, which are in the order you specified them in the designer. If you didn't give an order in the designer (field ordering is off), they're alphabetical)

so now you have two query objects, one action query object with an insert statement and one select statement.

The insert statement, remove the string from VALUES ... , then append the select statement to it, make sure you remove the parameters from the select DbCommand and add them to the insert DbCommand and execute the insert statement using ExecuteActionQuery.

The advantage to do it this way is that it's more solid: you're using methods which we also use for our query generation, and you don't rely on reflection.

I get always an exception "Object reference not set to an instance of an object." hwen creaeting the selecte Query. I have tested just with the id I'm interrested in as weel as with all fileds sa compomented.

   at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateFieldName(IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean appendAlias, String containingObjectName, String actualContainingObjectName)
   at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.ConvertFieldToRawName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction)
   at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction)
   at SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareSetPredicate.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText()
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendWhereClause(IPredicate filter, QueryFragments destination, IQuery query)
   at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(QueryParameters parameters)

      public int InsertMissingJoinTableEntitiesDirectly(ComponentGroupEntity entityToSave, Predicate filterPredicate) 
        {
            // first create the INSERT statement. It's a two step process:

            // get the persistence info for the fields:
            var persistenceInfoObjectsSave = GetFieldPersistenceInfos(entityToSave);
            // then call CreateInsertDQ:
            var insertQuery = CreateInsertDQ(entityToSave, persistenceInfoObjectsSave);

            // then create the SELECT statement. 

            // get the persistence info for the fields:
            var filterBucket = new RelationPredicateBucket(filterPredicate);
            var fetchEntity = new ComponentEntity();
           // var fields = fetchEntity.Fields;
            var fields = new EntityFields2(1) { ComponentFields.ComponentId };
            var persistenceInfoObjectsFetch = GetFieldPersistenceInfos(fields);
            var selectQuery = CreateSelectDQ(new QueryParameters(0, 0, 0)
            {
                FilterToUse = filterPredicate,
                RelationsToUse = filterBucket.Relations,
                AllowDuplicates = true,
                FieldsForQuery = fields,
                FieldPersistenceInfosForQuery = persistenceInfoObjectsFetch,
                IsLocalCopy = true,
                
            });

            var command = insertQuery.Command.CommandText;

            return 0;

        }

Can you help me and tell me what I am doing wrong.

It's very likely that some persistence info is null, please check with the debugger that you pass in arrays with IFieldPersistenceInfo objects.

I've attached my current DataAccessAdapter custom code in case it helps you guys out.

Here is a sample of how I'm using it. (Tag is a join table with SourceID and RoleID columns and links LegalBody and Role tables)

        public static string SetTagIDsForLegalBody(int legalBodyID, byte[] roleIDs)
        {
            using (var adapter = new DataAccessAdapter())
            {
                // Remove any existing that are not in the list
                adapter.DeleteEntitiesDirectly(typeof(TagEntity), new RelationPredicateBucket(TagFields.SourceID == legalBodyID & TagFields.RoleID != roleIDs));

                // Add those that don't already exist
                adapter.InsertMissingJoinTableEntitiesDirectly<TagEntity>(LegalBodyFields.ID == legalBodyID, RoleFields.ID == roleIDs);
            }

            return GetSortedTagDescriptionList(roleIDs);
        }

Cheers Simon

Thanks Simon for your help. As I don't have your reflectionhelper class, I have used the standard .NET reflection methods (see CreateQueryFromElementsCore methods).

Otis, you told me to use the CreateSelectDQ. But each time I call the method (see commented line 160) , I get an "Object reference not set to an instance of an object" exception with the same call stack as in my previous post. Inform me if you find a solution to use the proper way (and not a reflection).

Thanks,

Martin

The exception likely is caused by the fact that the IFieldPersistenceInfo objects are null, please check whether you pass these properly with a debugger.