Hi,

We are trying to generally reduce SQL Server compile times of some very complex queries with large IN statements by generating SQL that uses a XML query against a single parameter instead of having many many parameters (which causes the high compile time).

I have successfully created a custom FieldCompareRange predicate which inherits from the regular one, but overrides the ToQueryText method. However we also want to have operator overloading (==) support using this new custom predicate. Accordingly I've altered the FieldCreationClasses and EntityFieldFactoryAdapter templates to create fields of a new custom field class. This custom field class simply inherits from EntityField2 and overloads the necessary == operator to use the new custom FieldCompareRangePredicate.

All of this seems to work, but I had to actually replace the templates in the SD.TemplateBindings.SharedTemplates binding. When I tried to put my custom templates into my own separate template binding, the FieldCreationClasses generated fine, but the EntityFieldFactoryAdapter generated duplicate code in the FieldFactory class.

Is there an alternative approach you would recommend?

Thanks.

Hi Jeff,

Did you try to override the normal template with yours in the "Template bindings" subTab (at generation window)?

Could you please your templates so we could figure out your configuration?

These are out two custom templates.

///////////////////////////////////////////////////////////////
// This is generated code. 
//////////////////////////////////////////////////////////////
// Code is generated using LLBLGen Pro version: <[LLBLGenVersion]>
// Code is generated on: <[Time]>
// Code is generated using templates: <[TemplateName]>
// Templates vendor: Solutions Design.
// Templates version: <[TemplateVersion]>
//////////////////////////////////////////////////////////////
using System;

using <[RootNamespace]>;
using <[RootNamespace]>.HelperClasses;

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace <[RootNamespace]>.FactoryClasses
{
    /// <summary>
    /// Factory class for IEntityField2 instances, used in IEntityFields2 instances.
    /// </summary>
    public <[If UsePartialClasses]>partial <[EndIf]>class EntityFieldFactory
    {
        /// <summary> Private CTor, no instantiation possible.</summary>
        private EntityFieldFactory()
        {
        }
<[Foreach Entity CrLf]>
        /// <summary> Creates a new IEntityField2 instance for usage in the EntityFields object for the <[CurrentEntityName]>Entity. Which EntityField is created is specified by fieldIndex</summary>
        /// <param name="fieldIndex">The field which IEntityField2 instance should be created</param>
        /// <returns>The IEntityField2 instance for the field specified in fieldIndex</returns>
        public static IEntityField2 Create(<[CurrentEntityName]>FieldIndex fieldIndex)
        {
            return new NSEntityField(FieldInfoProviderSingleton.GetInstance().GetFieldInfo("<[CurrentEntityName]>Entity", (int)fieldIndex));
        }<[NextForeach]>
<[Foreach TypedView CrLf]>
        /// <summary> Creates a new IEntityField2 instance for usage in the EntityFields object for the <[CurrentTypedViewName]> TypedView. Which EntityField is created is specified by fieldIndex</summary>
        /// <param name="fieldIndex">The field which IEntityField2 instance should be created</param>
        /// <returns>The IEntityField2 instance for the field specified in fieldIndex</returns>
        public static IEntityField2 Create(<[CurrentTypedViewName]>FieldIndex fieldIndex)
        {
            return new NSEntityField(FieldInfoProviderSingleton.GetInstance().GetFieldInfo("<[CurrentTypedViewName]>TypedView", (int)fieldIndex));
        }<[NextForeach]>

        /// <summary>Creates a new IEntityField2 instance, which represents the field objectName.fieldName</summary>
        /// <param name="objectName">the name of the object the field belongs to, like CustomerEntity or OrdersTypedView</param>
        /// <param name="fieldName">the name of the field to create</param>
        public static IEntityField2 Create(string objectName, string fieldName)
        {
            return new NSEntityField(FieldInfoProviderSingleton.GetInstance().GetFieldInfo(objectName, fieldName));
        }

        #region Included Code
<# Custom_EntityFieldFactoryAdapterTemplate #>
        #endregion
    }
}

and

///////////////////////////////////////////////////////////////
// This is generated code. 
//////////////////////////////////////////////////////////////
// Code is generated using LLBLGen Pro version: <[LLBLGenVersion]>
// Code is generated on: <[Time]>
// Code is generated using templates: <[TemplateName]>
// Templates vendor: Solutions Design.
// Templates version: <[TemplateVersion]>
//////////////////////////////////////////////////////////////
using System;
using SD.LLBLGen.Pro.ORMSupportClasses;
using <[RootNamespace]>.FactoryClasses;
using <[RootNamespace]>;

namespace <[RootNamespace]>.HelperClasses
{<[Foreach Entity CrLf]>
    /// <summary>Field Creation Class for entity <[CurrentEntityName]>Entity</summary>
    public <[If UsePartialClasses]>partial <[EndIf]>class <[CurrentEntityName]>Fields
    {<[Foreach EntityField IncludeInherited]>
        /// <summary>Creates a new <[CurrentEntityName]>Entity.<[EntityFieldName]><[If EntityFieldIsOverriden]><[If Not EntityFieldContainedInCurrentEntity]>_<[EntityFieldEntityName]><[EndIf]><[EndIf]> field instance</summary>
        public static NSEntityField <[EntityFieldName]><[If EntityFieldIsOverriden]><[If Not EntityFieldContainedInCurrentEntity]>_<[EntityFieldEntityName]><[EndIf]><[EndIf]>
        {
            get { return (NSEntityField)EntityFieldFactory.Create(<[CurrentEntityName]>FieldIndex.<[EntityFieldName]><[If EntityFieldIsOverriden]><[If Not EntityFieldContainedInCurrentEntity]>_<[EntityFieldEntityName]><[EndIf]><[EndIf]>);}
        }<[NextForeach]>
    }<[NextForeach]>
    
<[Foreach TypedView CrLf]><[If HasFields]>
    /// <summary>Field Creation Class for typedview <[CurrentTypedViewName]>TypedView</summary>
    public <[If UsePartialClasses]>partial <[EndIf]>class <[CurrentTypedViewName]>Fields
    {<[Foreach TypedViewField CrLf]>
        /// <summary>Creates a new <[CurrentTypedViewName]>TypedView.<[TypedViewFieldName]> field instance</summary>
        public static NSEntityField <[TypedViewFieldName]>
        {
            get { return (NSEntityField)EntityFieldFactory.Create(<[CurrentTypedViewName]>FieldIndex.<[TypedViewFieldName]>);}
        }<[NextForeach]>
    }<[EndIf]><[NextForeach]>
}

When I added them to our custom template binding in Template Studio and then changed the tasks on the Generator to use these two custom templates, we got duplicate classes emitted in the EntityFieldFactory file.

When I pasted our custom template contents into the actual entityFieldFactoryAdapter.template and fieldCreationClassesAdapter.template files and then removed them from our template binding things worked fine.

Then, our custom classes are:

[Serializable]
    public class NSEntityField : EntityField2, IEntityField2 {
        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="fieldInfo">The field info.</param>
        public NSEntityField(IFieldInfo fieldInfo)
            : base(fieldInfo) {
        }


        /// <summary>
        /// Initializes a new instance of the <see cref="NSEntityField"/> class.
        /// </summary>
        /// <param name="name">Name</param>
        /// <param name="objectAlias">The object alias.</param>
        /// <param name="dataType">.NET Type of the data.</param>
        /// <remarks>Use this CTor for fields mapped onto a derived table with the alias objectAlias</remarks>
        public NSEntityField(string name, string objectAlias, Type dataType)
            : base(name, null, AggregateFunction.None, dataType, objectAlias, 0, 0, 0) {
        }


        /// <summary>
        /// Initializes a new instance of the <see cref="NSEntityField"/> class.
        /// </summary>
        /// <param name="name">Name</param>
        /// <param name="objectAlias">The object alias.</param>
        /// <param name="dataType">.NET Type of the data.</param>
        /// <param name="maxLength">The maximum length of the field or 0 if not relevant</param>
        /// <param name="precision">The precision to use or 0 if not relevant.</param>
        /// <param name="scale">The scale to use or 0 if not relevant.</param>
        /// <remarks>Use this CTor for fields mapped onto a derived table with the alias objectAlias</remarks>
        public NSEntityField(string name, string objectAlias, Type dataType, int maxLength, byte precision, byte scale)
            : base(name, null, AggregateFunction.None, dataType, objectAlias, maxLength, precision, scale) {
        }


        /// <summary>
        /// Initializes a new instance of the <see cref="EntityField"/> class.
        /// </summary>
        /// <param name="name">The name of the field. Has to be specified</param>
        /// <param name="expressionToApply">The expression to apply.</param>
        public NSEntityField(string name, IExpression expressionToApply)
            : base(name, expressionToApply, AggregateFunction.None, null, string.Empty, 0, 0, 0) {
        }


        /// <summary>
        /// Initializes a new instance of the <see cref="EntityField"/> class.
        /// </summary>
        /// <param name="name">The name of the field. Has to be specified</param>
        /// <param name="expressionToApply">The expression to apply.</param>
        /// <param name="dataType">.NET Type of the data.</param>
        public NSEntityField(string name, IExpression expressionToApply, Type dataType)
            : base(name, expressionToApply, AggregateFunction.None, dataType, string.Empty, 0, 0, 0) {
        }


        /// <summary>
        /// Initializes a new instance of the <see cref="EntityField"/> class.
        /// </summary>
        /// <param name="name">The name of the field. Has to be specified</param>
        /// <param name="expressionToApply">The expression to apply.</param>
        /// <param name="aggregateFunctionToApply">The aggregate function to apply.</param>
        public NSEntityField(string name, IExpression expressionToApply, AggregateFunction aggregateFunctionToApply)
            : base(name, expressionToApply, aggregateFunctionToApply, null, string.Empty, 0, 0, 0) {
        }


        /// <summary>
        /// Initializes a new instance of the <see cref="EntityField"/> class.
        /// </summary>
        /// <param name="name">The name of the field. Has to be specified</param>
        /// <param name="expressionToApply">The expression to apply.</param>
        /// <param name="aggregateFunctionToApply">The aggregate function to apply.</param>
        /// <param name="dataType">.NET Type of the data.</param>
        public NSEntityField(string name, IExpression expressionToApply, AggregateFunction aggregateFunctionToApply, Type dataType) :
            base(name, expressionToApply, aggregateFunctionToApply, dataType, string.Empty, 0, 0, 0) {
        }



        /// <summary>
        /// Initializes a new instance of the <see cref="NSEntityField"/> class.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="expressionToApply">The expression to apply.</param>
        /// <param name="aggregateFunctionToApply">The aggregate function to apply.</param>
        /// <param name="dataType">Type of the data.</param>
        /// <param name="objectAlias">The object alias.</param>
        /// <param name="maxLength">The maximum length of the field or 0 if not relevant</param>
        /// <param name="precision">The precision to use or 0 if not relevant.</param>
        /// <param name="scale">The scale to use or 0 if not relevant.</param>
        protected NSEntityField(string name, IExpression expressionToApply, AggregateFunction aggregateFunctionToApply, Type dataType,
                    string objectAlias, int maxLength, byte precision, byte scale)
            : base(name, expressionToApply, aggregateFunctionToApply, dataType, objectAlias, maxLength, precision, scale) {
        }


        /// <summary>
        /// Operator overload for the '==' operator to produce a FieldCompareRangePredicate. 
        /// </summary>
        /// <param name="field">Field to compare</param>
        /// <param name="values">the values to compare with.</param>
        /// <returns>A NSFieldCompareRangePredicate</returns>
        public static Predicate operator ==(EntityField2 field, IList values) {
            return new NSFieldCompareRangePredicate(field, null, values);
        }


        /// <summary>
        /// Operator overload for the '!=' operator to produce a FieldCompareRangePredicate. 
        /// </summary>
        /// <param name="field">Field to compare</param>
        /// <param name="values">the values to compare with.</param>
        /// <returns>A NSFieldCompareRangePredicate which is negated</returns>
        public static Predicate operator !=(EntityField2 field, IList values) {
            return new NSFieldCompareRangePredicate(field, null, true, values);
        }

    }

and:

[Serializable]
    public class NSFieldCompareRangePredicate : SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareRangePredicate {
        private IEntityFieldCore _field;

        // Summary:
        //   CTor
        public NSFieldCompareRangePredicate() { }
        //
        // Summary:
        //   CTor. Creates Field IN (values) clause
        //
        // Parameters:
        //   field:
        //   Field used in the comparison expression
        //
        //   values:
        //   Value range to set for the IN clause. Specify any range of values.  If a
        //   single array is passed or an ArrayList, this will be converted to a range
        //   of values.
        public NSFieldCompareRangePredicate(IEntityField field, params object[] values) : base(field, values) { _field = field; }
        //
        // Summary:
        //   CTor. Creates Field IN (values) clause
        //
        // Parameters:
        //   field:
        //   Field used in the comparison expression
        //
        //   negate:
        //   Flag to make this expression add NOT to itself
        //
        //   values:
        //   Value range to set for the IN clause. Specify any range of values.  If a
        //   single array is passed or an ArrayList, this will be converted to a range
        //   of values.
        public NSFieldCompareRangePredicate(IEntityField field, bool negate, params object[] values) : base(field, negate, values) { _field = field; }
        //
        // Summary:
        //   CTor. Creates Field IN (values) clause
        //
        // Parameters:
        //   field:
        //   Field used in the comparison expression
        //
        //   objectAlias:
        //   Alias for the object the field belongs to. Used to identify which entity
        //   to use when the entity is present multiple times in a relation collection.
        //   Alias has to match an alias specified in the relation collection or should
        //   be left empty if no alias is specified (or no relation collection is used).
        //   In that case, use another overload.
        //
        //   values:
        //   Value range to set for the IN clause. Specify any range of values.  If a
        //   single array is passed or an ArrayList, this will be converted to a range
        //   of values.
        public NSFieldCompareRangePredicate(IEntityField field, string objectAlias, params object[] values) : base(field, objectAlias, values) { _field = field; }
        //
        // Summary:
        //   CTor. Creates Field IN (values) clause
        //
        // Parameters:
        //   field:
        //   Field used in the comparison expression
        //
        //   persistenceInfo:
        //   The persistence info object for the field
        //
        //   values:
        //   Value range to set for the IN clause. Specify any range of values.  If a
        //   single array is passed or an ArrayList, this will be converted to a range
        //   of values.
        public NSFieldCompareRangePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, params object[] values) : base(field, persistenceInfo, values) { _field = field; }
        //
        // Summary:
        //   CTor. Creates Field IN (values) clause
        //
        // Parameters:
        //   field:
        //   Field used in the comparison expression
        //
        //   objectAlias:
        //   Alias for the object the field belongs to. Used to identify which entity
        //   to use when the entity is present multiple times in a relation collection.
        //   Alias has to match an alias specified in the relation collection or should
        //   be left empty if no alias is specified (or no relation collection is used).
        //   In that case, use another overload.
        //
        //   negate:
        //   Flag to make this expression add NOT to itself
        //
        //   values:
        //   Value range to set for the IN clause. Specify any range of values.  If a
        //   single array is passed or an ArrayList, this will be converted to a range
        //   of values.
        public NSFieldCompareRangePredicate(IEntityField field, string objectAlias, bool negate, params object[] values) : base(field, objectAlias, negate, values) { _field = field; }
        //
        // Summary:
        //   CTor. Creates Field IN (values) clause
        //
        // Parameters:
        //   field:
        //   Field used in the comparison expression
        //
        //   persistenceInfo:
        //   The persistence info object for the field
        //
        //   negate:
        //   Flag to make this expression add NOT to itself
        //
        //   values:
        //   Value range to set for the IN clause. Specify any range of values.  If a
        //   single array is passed or an ArrayList, this will be converted to a range
        //   of values.
        public NSFieldCompareRangePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, bool negate, params object[] values) : base(field, persistenceInfo, negate, values) { _field = field; }
        //
        // Summary:
        //   CTor. Creates Field IN (values) clause
        //
        // Parameters:
        //   field:
        //   Field used in the comparison expression
        //
        //   persistenceInfo:
        //   The persistence info object for the field
        //
        //   objectAlias:
        //   Alias for the object the field belongs to. Used to identify which entity
        //   to use when the entity is present multiple times in a relation collection.
        //   Alias has to match an alias specified in the relation collection or should
        //   be left empty if no alias is specified (or no relation collection is used).
        //   In that case, use another overload.
        //
        //   values:
        //   Value range to set for the IN clause. Specify any range of values.  If a
        //   single array is passed or an ArrayList, this will be converted to a range
        //   of values.
        public NSFieldCompareRangePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string objectAlias, params object[] values) : base(field, persistenceInfo, objectAlias, values) { _field = field; }
        //
        // Summary:
        //   CTor. Creates Field IN (values) clause
        //
        // Parameters:
        //   field:
        //   Field used in the comparison expression
        //
        //   persistenceInfo:
        //   The persistence info object for the field
        //
        //   objectAlias:
        //   Alias for the object the field belongs to. Used to identify which entity
        //   to use when the entity is present multiple times in a relation collection.
        //   Alias has to match an alias specified in the relation collection or should
        //   be left empty if no alias is specified (or no relation collection is used).
        //   In that case, use another overload.
        //
        //   negate:
        //   Flag to make this expression add NOT to itself
        //
        //   values:
        //   Value range to set for the IN clause. Specify any range of values.  If a
        //   single array is passed or an ArrayList, this will be converted to a range
        //   of values.
        public NSFieldCompareRangePredicate(NSEntityField field, IFieldPersistenceInfo persistenceInfo, string objectAlias, bool negate, params object[] values)
            : base(field, persistenceInfo, objectAlias, negate, values) {
            _field = field;
        }

        public override string ToQueryText(ref int uniqueMarker, bool inHavingClause) {
            string setting = ConfigurationManager.AppSettings["UseXmlINQueries"];
            bool useXmlQuery = true;
            if (!string.IsNullOrEmpty(setting)) {
                bool.TryParse(setting, out useXmlQuery);
            }
            if (useXmlQuery && this.Values.Count > 1) {
                if (_field == null) {
                    return "";
                }

                if (this.Values.Count <= 0) {
                    return "";
                }

                if (base.DatabaseSpecificCreator == null) {
                    throw new System.ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.");
                }

                this.Parameters.Clear();

                StringBuilder queryText = new StringBuilder(128);
                queryText.Append(this.DatabaseSpecificCreator.CreateFieldName(_field, this.PersistenceInfo, _field.Name, this.ObjectAlias, ref uniqueMarker, inHavingClause));
                queryText.Append(" ");

                if (_field.ExpressionToApply != null) {
                    for (int i = 0; i < _field.ExpressionToApply.Parameters.Count; i++) {
                        this.Parameters.Add(_field.ExpressionToApply.Parameters[i]);
                    }
                }

                if (base.Negate) {
                    queryText.Append("NOT ");
                }

                System.Xml.XmlDocument document = new System.Xml.XmlDocument();
                System.Xml.XmlElement itemsElement = document.CreateElement("items");

                for (int i = 0; i < this.Values.Count; i++) {
                    System.Xml.XmlElement itemElement = document.CreateElement("i");
                    itemElement.InnerXml = this.Values[i].ToString();
                    itemsElement.AppendChild(itemElement);
                }
                document.LoadXml(itemsElement.OuterXml);

                System.Data.IDataParameter parameter = this.DatabaseSpecificCreator.CreateParameter(_field.Alias, System.Data.ParameterDirection.Input,
                    new System.Data.SqlTypes.SqlXml(System.Xml.XmlNodeReader.Create(new System.IO.StringReader((itemsElement.OuterXml)))));
                parameter.ParameterName += uniqueMarker.ToString();
                parameter.DbType = System.Data.DbType.Xml;

                queryText.AppendFormat("IN (SELECT Entity.ID.value('.', '{0}') AS ID FROM {1}.nodes('/items/i') AS Entity(ID))", (SqlDbType)this.PersistenceInfo.SourceColumnDbType, parameter.ParameterName);

                this.Parameters.Add(parameter);

                return queryText.ToString();
            } else {

                // use default LLBLGen implementation
                return base.ToQueryText(ref uniqueMarker, inHavingClause);
            }
        }
    }

Thanks for your assistance.

Hi Jeff,

I didn't explain correctly, sorry. Please attach (in a zip) the templates, tasks, binding files and presets you created. This way we could use them and test the configuration. Thanks.

Your own templates either REPLACE existing templates (so they have to be bound to the same TEMPLATE_ID but in a separate bindings file which is placed above the one which contains the bindings you're overriding at tab 2 in the generator config dialog), or they are include templates bound to templateids which aren't in use currently.

If it's the former, be sure you bind to the same templateid's as the ones you're overriding, if it's the latter, be sure that you don't introduce duplicated code which is already there through other includes or through the template you're including the template in.

So it seems you're overriding the entityfieldfactory, then be sure your template is bound to the same templateid as the template you're replacing AND the templatebindings file is placed above the one which contains the binding with the standard template.

Thanks. By using the same Template ID I was able to prevent the dupe generation...when I used my own template ID and changed the ID of the task I got the dupe generation again....This is ok.

Unfortunately I ran into another problem with the operator overloading of the Field. Since the interface for IEntityField2 says SetObjectAlias must return an EntityField2, we started seeing the problem that PredicateExpressions using the object aliases would get the wrong operator overload called.

We ended up just changing the source's FieldCompareRangePredicate and that seems to work great.