I am trying to create the following Dynamic Relation but am getting an exception when I try to put a hard coded value in the OnClause predicate.

IPredicateExpression onClause = new PredicateExpression();
onClause.Add((EntityField2)firstEntity.GetEntityFactory().CreateFields()["FieldA"] ==
                         (EntityField2)secondEntity.GetEntityFactory().CreateFields()["FieldB"]);   
onClause.AddWithAnd((EntityField2)secondEntity.GetEntityFactory().CreateFields()["FieldC"] == 0);
DynamicRelation relation = new DynamicRelation(firstEntityType, JoinHint.Inner, secondEntityType, string.Empty, string.Empty, onClause);

I cannot just add a filter instead of adding to the join because I can have multiple fields on firstEntity joining to secondEntity based on the hardcoded filter. For example,

..
..
onClause.AddWithAnd((EntityField2)secondEntity.GetEntityFactory().CreateFields()["FieldC"] == 1);

AND

..
..
onClause.AddWithAnd((EntityField2)secondEntity.GetEntityFactory().CreateFields()["FieldC"] == 2);

Thanks.

Exception text and stack trace, please.

"An exception was caught during the execution of a retrieval query: Incorrect syntax near the keyword 'AND'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 132\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 790\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 3070\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 2956\r\n at ConsoleApp.Program.JoinCusttableToDimension() in D:\Other code\LLBLGen Pro Projects\AxProjConsoleApp\ConsoleApp\Program.cs:line 82\r\n at ConsoleApp.Program.Main(String[] args) in D:\Other code\LLBLGen Pro Projects\AxProjConsoleApp\ConsoleApp\Program.cs:line 26"

please enable DQE tracing and post the query generated

I used SQL Server Profiler and got this:

exec sp_executesql N'SELECT [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[ACCOUNTNUM] AS [a], [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[NAME] AS [b], [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DIMENSION] AS [c], [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[NUM] AS [d], [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DIMENSIONCODE] AS [e], [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DESCRIPTION] AS [f] FROM ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE]  LEFT JOIN [AX5_Atlas4_Dev].[dbo].[DIMENSIONS]  ON  ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DIMENSION] = [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[NUM] AND [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DATAAREAID] = [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DATAAREAID] AND )) WHERE ( ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DATAAREAID] = @p1))',N'@p1 nvarchar(4)',@p1=N'USA'

I will try to enable DQE tracing as well.

There is an extra 'AND' added to the end of the ON Clause predicate !!

We'll look into it.

(Edit) Which LLBLGen Pro runtime library version/build number are you using?

I am sorry, the extra AND was my mistake cause I was trying to do it with FieldCompareValuePredicate as well. I have edited the message. So now there is only an "empty" AND.

The 'AND' is still there (in the "SQL" of your message).

OK, now just to avoid confusion could you please post once more the code snippet and corresponding generated SQL Query.

ALso please post the LLBLGen Pro runtime library version/build number.

Thanks.

V 3.0 Final

var entityType = (EntityType)Enum.Parse(typeof(EntityType), "CusttableEntity");
            var myEntity = GeneralEntityFactory.Create(entityType);

            var dimensionEntityType = (EntityType)Enum.Parse(typeof(EntityType), "DimensionEntity");
            var dimensionEntity = GeneralEntityFactory.Create(dimensionEntityType);
            
            var fields = new ResultsetFields(6);
            fields.DefineField(myEntity.Fields["Accountnum"], 0, "a");
            fields.DefineField(myEntity.Fields["Name"], 1, "b");
            fields.DefineField(myEntity.Fields["Dimension"], 2, "c");
            fields.DefineField(dimensionEntity.Fields["Num"], 3, "d");
            fields.DefineField(dimensionEntity.Fields["Dimensioncode"], 4, "e");
            fields.DefineField(dimensionEntity.Fields["Description"], 5, "f");


            EntityField2 dataareaId = (EntityField2)myEntity.Fields["Dataareaid"];
            EntityField2 accountNum = (EntityField2)myEntity.Fields["Dimension"];


            DataAccessAdapter adapter = new DataAccessAdapter();
            DataTable dynamicList = new DataTable();
            RelationPredicateBucket filter = new RelationPredicateBucket(dataareaId == "USA");

            IPredicateExpression onClause = new PredicateExpression();
            onClause.Add((EntityField2)myEntity.GetEntityFactory().CreateFields()["Dimension"] ==
                         (EntityField2)dimensionEntity.GetEntityFactory().CreateFields()["Num"]);
            onClause.AddWithAnd((EntityField2)myEntity.GetEntityFactory().CreateFields()["Dataareaid"] ==
                                (EntityField2)dimensionEntity.GetEntityFactory().CreateFields()["Dataareaid"]);
            onClause.AddWithAnd((EntityField2)dimensionEntity.GetEntityFactory().CreateFields()["DimensionCode"] == 0);

            DynamicRelation relation = new DynamicRelation(entityType, JoinHint.Left, dimensionEntityType,
                                                           string.Empty, string.Empty, onClause);
            filter.Relations.Add(relation);

            adapter.FetchTypedList(fields, dynamicList, filter);

exec sp_executesql N'SELECT [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[ACCOUNTNUM] AS [a], [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[NAME] AS [b], [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DIMENSION] AS [c], [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[NUM] AS [d], [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DIMENSIONCODE] AS [e], [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DESCRIPTION] AS [f] FROM ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE]  LEFT JOIN [AX5_Atlas4_Dev].[dbo].[DIMENSIONS]  ON  ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DIMENSION] = [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[NUM] AND [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DATAAREAID] = [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DATAAREAID] AND )) WHERE ( ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DATAAREAID] = @p1))',N'@p1 nvarchar(4)',@p1=N'USA'

I cant reproduce the "AND )" but I get other errors using your same code against Northwind database.

It's weird the way the code is setup. Could you please post your desire approximate sql code?

Basically, it is missing the last clause in code related to DimensionCode.

My desired SQL is:

SELECT [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[ACCOUNTNUM] AS [a], 
[AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[NAME] AS [b], 
[AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DIMENSION] AS [c], 
[AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[NUM] AS [d], 
[AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DIMENSIONCODE] AS [e], 
[AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DESCRIPTION] AS [f] 
FROM ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE] LEFT JOIN [AX5_Atlas4_Dev].[dbo].[DIMENSIONS] 
ON ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DIMENSION] = [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[NUM] 
AND [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DATAAREAID] = [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DATAAREAID] 
AND [AX5_Atlas4_Dev].[dbo].[DIMENSIONS].[DIMENSIONCODE] = 1)) 
WHERE ( ( [AX5_Atlas4_Dev].[dbo].[CUSTTABLE].[DATAAREAID] ='USA'))

daelmo wrote:

It's weird the way the code is setup. Could you please post your desire approximate sql code?

Why is the code weird? Is what I am doing incorrect? Are there other ways of doing it?

nw wrote:

daelmo wrote:

It's weird the way the code is setup. Could you please post your desire approximate sql code?

Why is the code weird? Is what I am doing incorrect? Are there other ways of doing it?

I think he refers to the fact you use strings to refer to elements. Are these from a search form or something?

Anyway, it's indeed odd that you get a dangling AND, as the last predicate simply has to be emitted. We'll look into it.

Otis wrote:

nw wrote:

daelmo wrote:

It's weird the way the code is setup. Could you please post your desire approximate sql code?

Why is the code weird? Is what I am doing incorrect? Are there other ways of doing it?

I think he refers to the fact you use strings to refer to elements. Are these from a search form or something?

Anyway, it's indeed odd that you get a dangling AND, as the last predicate simply has to be emitted. We'll look into it.

If what you meant is the hardcoded value in the 3rd OnClause ([DIMENSIONCODE] = 1), it is just how the database has been designed. There could be multiple fields from a single table that references another table and it is the value of DIMENSIONCODE that makes the join from the different fields unique.

For example, (Field A and DimensionCode = 0) makes 1 join and (Field B and DimensionCode = 1) makes another join into the same table. It acts like an Array index into the other table.

Is it possible for me to setup the above relationship using the LLBLGen UI?

nw wrote:

Is it possible for me to setup the above relationship using the LLBLGen UI?

Yes, just create a relationship in the designer, as long as the pk side indeed has PK fields and not a UC.

Otis wrote:

nw wrote:

Is it possible for me to setup the above relationship using the LLBLGen UI?

Yes, just create a relationship in the designer, as long as the pk side indeed has PK fields and not a UC.

Then I am guessing I can't, since 3 fields form a PK and one of the field requires a hardcoded string value while the designer only allows the selection of FK field names. Are there any other ways of doing this other than using a Dynamic Relation?

nw wrote:

Otis wrote:

nw wrote:

Is it possible for me to setup the above relationship using the LLBLGen UI?

Yes, just create a relationship in the designer, as long as the pk side indeed has PK fields and not a UC.

Then I am guessing I can't, since 3 fields form a PK and one of the field requires a hardcoded string value while the designer only allows the selection of FK field names. Are there any other ways of doing this other than using a Dynamic Relation?

No, then you need a dynamic relation.

I'll try to reproduce your problem now, to see where the dangling 'and' is coming from as it should work according to your code.

I can't reproduce it with the latest build on Adventureworks:

var entityType = (EntityType)Enum.Parse(typeof(EntityType), "SalesOrderDetailEntity");
var salesOrderDetail = GeneralEntityFactory.Create(entityType);

var dimensionEntityType = (EntityType)Enum.Parse(typeof(EntityType), "SpecialOfferProductEntity");
var specialOfferProduct = GeneralEntityFactory.Create(dimensionEntityType);

var fields = new ResultsetFields(4);
fields.DefineField(salesOrderDetail.Fields["SalesOrderDetailId"], 0, "a");
fields.DefineField(salesOrderDetail.Fields["SalesOrderId"], 1, "b");
fields.DefineField(specialOfferProduct.Fields["ProductId"], 2, "d");
fields.DefineField(specialOfferProduct.Fields["SpecialOfferId"], 3, "e");

EntityField2 dataareaId = (EntityField2)salesOrderDetail.Fields["OrderQty"];

DataAccessAdapter adapter = new DataAccessAdapter();
DataTable dynamicList = new DataTable();
RelationPredicateBucket filter = new RelationPredicateBucket(dataareaId == 25);

IPredicateExpression onClause = new PredicateExpression();
onClause.Add((EntityField2)salesOrderDetail.GetEntityFactory().CreateFields()["ProductId"] ==
             (EntityField2)specialOfferProduct.GetEntityFactory().CreateFields()["ProductId"]);
onClause.AddWithAnd((EntityField2)salesOrderDetail.GetEntityFactory().CreateFields()["SpecialOfferId"] ==
                    (EntityField2)specialOfferProduct.GetEntityFactory().CreateFields()["SpecialOfferId"]);
onClause.AddWithAnd((EntityField2)specialOfferProduct.GetEntityFactory().CreateFields()["ModifiedDate"] == DateTime.Now);

DynamicRelation relation = new DynamicRelation(entityType, JoinHint.Left, dimensionEntityType,
                                             string.Empty, string.Empty, onClause);
filter.Relations.Add(relation);
adapter.FetchTypedList(fields, dynamicList, filter);

I used the exact code you posted and created the query. It generated:

SELECT  [AdventureWorks].[Sales].[SalesOrderDetail].[SalesOrderDetailID] AS [a], 
        [AdventureWorks].[Sales].[SalesOrderDetail].[SalesOrderID] AS [b], 
        [AdventureWorks].[Sales].[SpecialOfferProduct].[ProductID] AS [d], 
        [AdventureWorks].[Sales].[SpecialOfferProduct].[SpecialOfferID] AS [e] 
FROM ( [AdventureWorks].[Sales].[SalesOrderDetail]  LEFT JOIN [AdventureWorks].[Sales].[SpecialOfferProduct]  
        ON  ( [AdventureWorks].[Sales].[SalesOrderDetail].[ProductID] = 
                [AdventureWorks].[Sales].[SpecialOfferProduct].[ProductID] 
            AND [AdventureWorks].[Sales].[SalesOrderDetail].[SpecialOfferID] = 
                [AdventureWorks].[Sales].[SpecialOfferProduct].[SpecialOfferID] 
            AND [AdventureWorks].[Sales].[SpecialOfferProduct].[ModifiedDate] = @p1)) 
WHERE ( ( [AdventureWorks].[Sales].[SalesOrderDetail].[OrderQty] = @p2))

Which ran fine.

So if you can create a small repro case (db schema DDL SQL plus a small (!) piece of code which reproduces the problem, it would be great. Please try to keep the repro case as small as possible, e.g. only the generated code + a console app. Also include the .llblgenproj file and type converters / enum type imports used, if any. Thanks.

I have attached the code to reproduce the problem.

1) VS solution in llblgen\AxProjConsoleApp\ConsoleApp folder (ConsoleApp.sln) 2) LLBLGen project in llblgen folder (AxProj.llblgenproj) 3) DDL script in llblgen folder (DDLSQL_CreateScript_20100629-092215.sql)

I think found your error. Is in this line:

onClause.AddWithAnd((EntityField2)dimensionEntity.GetEntityFactory().CreateFields()["DimensionCode"] == 0);

Do you see it? CusttableEntity doesn't have any field with name "DimensionCode" you mistyped the field name. As the field doesn't exist it is not added to the predicate expression tree. Then LLBLGen emits the sql code but there is a missing element, that's why Sql connector complains.

It should be Dimensioncode (as you write few lines before). If you change that line this way:

onClause.AddWithAnd((EntityField2)dimensionEntity.GetEntityFactory().CreateFields()["Dimensioncode"] == 0);

...everything work as expected.

I found that because there was too much code so I first rewrote it:

// my dynList
var fields = new ResultsetFields(6);
fields.DefineField(CusttableFields.Accountnum, 0, "a");
fields.DefineField(CusttableFields.Name, 1, "b");
fields.DefineField(CusttableFields.Dimension, 2, "c");
fields.DefineField(DimensionFields.Num, 3, "d");
fields.DefineField(DimensionFields.Dimensioncode, 4, "e");
fields.DefineField(DimensionFields.Description, 5, "f");

// ON CLAUSE
IPredicateExpression onClause = new PredicateExpression();
onClause.Add(CusttableFields.Dimension == DimensionFields.Num);
onClause.AddWithAnd(CusttableFields.Dataareaid == DimensionFields.Dataareaid);
onClause.AddWithAnd(DimensionFields.Dimensioncode == 0);

// the dynRelation
DynamicRelation relation = new DynamicRelation(EntityType.CusttableEntity, JoinHint.Left, EntityType.DimensionEntity,
                                               string.Empty, string.Empty, onClause);
// the filter
RelationPredicateBucket filter = new RelationPredicateBucket(CusttableFields.Dataareaid == "USA");
filter.Relations.Add(relation);

// fetch
DataAccessAdapter adapter = new DataAccessAdapter();
DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, filter);

So. as a personal recommendation I suggest to use directly the Field properties instead of abuse of string-base accessors, that is not friendly when you need to make entity field name changes and regenerate code. It's just a recommendation as I don't know the situation and requirements of your project.

Hope helpful

daelmo wrote:

I think found your error. Is in this line:

onClause.AddWithAnd((EntityField2)dimensionEntity.GetEntityFactory().CreateFields()["DimensionCode"] == 0);

Do you see it? CusttableEntity doesn't have any field with name "DimensionCode" you mistyped the field name. As the field doesn't exist it is not added to the predicate expression tree. Then LLBLGen emits the sql code but there is a missing element, that's why Sql connector complains.

It should be Dimensioncode (as you write few lines before). If you change that line this way:

onClause.AddWithAnd((EntityField2)dimensionEntity.GetEntityFactory().CreateFields()["Dimensioncode"] == 0);

...everything work as expected.

What a silly mistake!! So sorry for wasting your time!

daelmo wrote:

So. as a personal recommendation I suggest to use directly the Field properties instead of abuse of string-base accessors, that is not friendly when you need to make entity field name changes and regenerate code. It's just a recommendation as I don't know the situation and requirements of your project.

Hope helpful

The reason I need to use strings is because I only know the field names during runtime for doing dynamic queries (unless you have a better suggestion). However, instead of using

myEntity.GetEntityFactory().CreateFields()["MyField"]

I have been using

(EntityField2)myEntity.Fields["MyField"]

Are there any differences between the two? Which is better? I am assuming both uses reflection internally to create the field?

Once again, thanks for all the help from the support team!

(EntityField2)myEntity.Fields["MyField"]

Please use the above code.

Btw, no reflection is used. This is a string indexer which fetches the numeric index of the field from an internal Dictionary<string, int>. And then the numeric index is used to access the item inside the EntityFileds List.

Walaa wrote:

(EntityField2)myEntity.Fields["MyField"]

Please use the above code.

Btw, no reflection is used. This is a string indexer which fetches the numeric index of the field from an internal Dictionary<string, int>. And then the numeric index is used to access the item inside the EntityFileds List.

Thanks for the reply.