Hi, I'm working with the latest build and ORACLE 10 ODP.

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll ProductVersion : 2.6.08.0911 Assembly Version : 2.6.0.0

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll, ProductVersion : 2.6.08.0911 . Assembly Version : 2.6.0.0

There is a problem in add created relations to bucket , generated from linq

case 1 ( Correct)

The linq query contains relations into ( working case ):

linq :

    var query2 = (
                        from entity in myMeta.AmitAmit 
                      where
                      (
                         entity.AmitKod == 22 &&
                         entity.AmitNetunimzmaniyimleimut.IrgunmenahelKod == 71
                      )
                      select entity);

        AmitAmitEntity requestEntity2 = query2.First();

The result bucket consists of :

relations :

( AmitNetunimzmaniyimleimutEntity LPA_L1 RIGHT JOIN AmitAmitEntity LPA_L2 ON LPA_L1.AdamzmaniKod=LPA_L2.AdamzmaniKod )

+ predicate expression ( ( ( ( ( LPA_L2.[AmitKod] = @AmitKod1 ) AND ( LPA_L1.[IrgunmenahelKod] = @IrgunmenahelKod2 ) ) ) ) )

after call to .FetchEntityCollection the generated SQL query is correct

CASE 2 ( WRONG SQL QUERY )

the linq query : linq : var simpleQuery = ( from entity in myMeta.AmitAmit where ( entity.AmitKod == 22 ) select entity );

The result bucket consists of :

PREDICATE EXPRESSION :

( ( ( ( LPA_L1.[AmitKod] = @AmitKod1 ) ) ) )

Now I want to add relation to the result bucket .

Assume , original bucket names "filterBucket"

I create the relation with predicate expression and custom filter as

IEntityRelation entityRelationToAdd = new EntityRelation(); // predicate
IPredicateExpression customFilter = new PredicateExpression(); customFilter.ObjectAlias = filterBucket.SelectListAlias; customFilter.Add(AmitNetunimzmaniyimleimutFields.IrgunmenahelKod == 71); entityRelationToAdd.CustomFilter = customFilter;

//add relations
filterBucket.Relations.Add(entityRelationToAdd);

the relation into result bucket is RELATIONS : ( AmitNetunimzmaniyimleimutEntity INNER JOIN AmitAmitEntity ON AmitNetunimzmaniyimleimutEntity.AdamzmaniKod=AmitAmitEntity.AdamzmaniKod AND (

    (
        AmitNetunimzmaniyimleimutEntity.[IrgunmenahelKod] = @IrgunmenahelKod1
    )
)

)

( without aliases , comparing to case 1 )

AFTER Call TO .FetchEntityCollection(...) is raised exception about wrong SQL Query syntax

What the way to resolve this issue ?

Best regards,

Alexander Elin

you're using an outdated runtime library build (of more than a year old ). Please download the latest runtime lib build from the customer area and verify if that fixes your problem.

Hi,

I've get the latest version of runtime library from "Customer area" :

SD.LLBLGen.Pro.DQE.Oracle.NET20.dll ProductVersion : 2.6.09.0203 Assembly Version : 2.6.0.0

SD.LLBLGen.Pro.DQE.Oracle10g.NET20.dll ProductVersion : 2.6.09.0203 Assembly Version : 2.6.0.0

SD.LLBLGen.Pro.DQE.OracleMS.NET20.dll ProductVersion : 2.6.09.0203 Assembly Version : 2.6.0.0

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll ProductVersion : 2.6.09.0729 Assembly Version : 2.6.0.0

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll ProductVersion : 2.6.09.0903 Assembly Version : 2.6.0.0

the relation I've used just exists into entity class , only I add the custom filter ( see above)

result bucket : PREDICATE EXPRESSION ( after linq generation): ( ( ( LPA_L1.[AmitKod] = @AmitKod1 ) ) )

Relation ( after add custom filter to existing relation) ( AmitNetunimzmaniyimleimutEntity INNER JOIN AmitAmitEntity ON AmitNetunimzmaniyimleimutEntity.AdamzmaniKod=AmitAmitEntity.AdamzmaniKod AND (

    (
        AmitNetunimzmaniyimleimutEntity.[IrgunmenahelKod] = @IrgunmenahelKod1
    )
)

)

after call to .FetchEntityCollection , there is a Oracle exception : "ORA-00904: "LPA_L1"."AMIT_KOD": invalid identifier"

wrong SQL generated is :

Query: SELECT DISTINCT "LPA_L1"."AMIT_KOD" AS "AmitKod", "LPA_L1"."YESHUT_KOD" AS "YeshutKod", "LPA_L1"."CHESHBONOSH_KOD" AS "CheshbonoshKod", "LPA_L1"."TAARICHKARTISMEYDA" AS "Taarichkartismeyda", "LPA_L1"."HAZMANAKARTISMEYDA" AS "Hazmanakartismeyda", "LPA_L1"."CREATIONDATE" AS "Creationdate", "LPA_L1"."CREATIONUSER" AS "Creationuser", "LPA_L1"."UPDATEDATE" AS "Updatedate", "LPA_L1"."UPDATEUSER" AS "Updateuser", "LPA_L1"."ISDELETED" AS "Isdeleted", "LPA_L1"."DELETEDDATE" AS "Deleteddate", "LPA_L1"."ISHASAVA" AS "Ishasava", "LPA_L1"."KVUTZATSHYUCH_KOD" AS "KvutzatshyuchKod", "LPA_L1"."ISKAYAMMANUYINTERNET" AS "Iskayammanuyinternet", "LPA_L1"."TAARICHHAFSAKATPEILUT" AS "Taarichhafsakatpeilut", "LPA_L1"."SIBATHAFSAKATINTERNET_KOD" AS "SibathafsakatinternetKod", "LPA_L1"."ISKAYAMIKULLEAMIT" AS "Iskayamikulleamit", "LPA_L1"."ADAMZMANI_KOD" AS "AdamzmaniKod", "LPA_L1"."ISDOARCHOZER" AS "Isdoarchozer", "LPA_L1"."ISTUYAV" AS "Istuyav", "LPA_L1"."SUGHATZHARA_KOD" AS "SughatzharaKod", "LPA_L1"."HACHELMECHODESH" AS "Hachelmechodesh", "LPA_L1"."TARICHIDKUNHTZHARA" AS "Tarichidkunhtzhara", "LPA_L1"."SUG_LAKOAH" AS "SugLakoah", "LPA_L1"."SIVUG_UCHLUSIA_NAAMAN_KOD" AS "SivugUchlusiaNaamanKod", "LPA_L1"."ZIHUY_NAAMAN_KOD" AS "ZihuyNaamanKod", "LPA_L1"."SHNAT_HATZHARA" AS "ShnatHatzhara", "LPA_L1"."MATZHIR_KOD" AS "MatzhirKod", "LPA_L1"."TAARICH_TCHILAT_TOKEF" AS "TaarichTchilatTokef", "LPA_L1"."TAARICH_SIUM_TOKEF" AS "TaarichSiumTokef", "LPA_L1"."MAKOR_DIVUAH_KOD" AS "MakorDivuahKod", "LPA_L1"."HEAROT" AS "Hearot", "LPA_L1"."TAARICH_DIVUACH_ACHARON" AS "TaarichDivuachAcharon" FROM ( "KUPAG_B_DEV1"."AMIT_NETUNIMZMANIYIMLEIMUT"  INNER JOIN "KUPAG_B_DEV1"."AMIT_AMIT"  ON  "KUPAG_B_DEV1"."AMIT_NETUNIMZMANIYIMLEIMUT"."ADAMZMANI_KOD"="KUPAG_B_DEV1"."AMIT_AMIT"."ADAMZMANI_KOD" AND ( ( "KUPAG_B_DEV1"."AMIT_NETUNIMZMANIYIMLEIMUT"."IRGUNMENAHEL_KOD" = :IrgunmenahelKod1))) WHERE ( ( ( ( "LPA_L1"."AMIT_KOD" = :AmitKod2))))
Parameter: :IrgunmenahelKod1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 71.
Parameter: :AmitKod2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 22.

The problem still exists

Any help appreciated

Best regards,

Alexander

just to understand the context the error occurs in: you first write a linq query and when it gets executed on the adapter, you add a filter? Or is the linq query just to illustrate what you're trying to achieve with normal query api elements?

Hi,

first of all I call to linq.ToList() -> get bucket with predicate expression

and AFTER THAT try to add relation to this bucket ( just before call to .FetchEntityCollection())

Probably you have the example the way as achieve this ?

Best regards,

Alexander

alexdummy wrote:

Hi,

first of all I call to linq.ToList() -> get bucket with predicate expression

and AFTER THAT try to add relation to this bucket ( just before call to .FetchEntityCollection())

Probably you have the example the way as achieve this ?

I still doesn't understand the context of what you are trying to do. linq.ToList() will fetch the stuff. You can't add relations after the query was executed. Please elaborate more.

Hi,

The workflow is :

1) linq query ->

2) query.ToList() ->

internal class TestDataAccessAdapter : DataAccessAdapter:

3) //add relations to ->

.....

4) base.FetchEntityCollection(...)

If it's possible to add relations in step 3) ?

Probably It's possible to "Copy" bucket to another new bucket with all predicate expressions but without aliases ?

In this case it is a solution

Best regards,

Alexander

So you need to add a built-in join/relation, that's why you are deriving from the DataAccessAdapter.

But take care this would add the relation/join to every query you make, do you want that?

For this I think you should override CreateSelectDQ(). You can find some examples in the forums if you serach for: CreateSelectDQ

But if you explain why you need this, maybe we can help you find a better or easier solution.

Hi, thanks for your responce

I need to do this for the follow reason :

For each linq query called from application we need to add some custom filer , based on

relations for filtering the Fetch result due to security reasons .

Example :

we need to add filter for userID

the main entity not contains field "UserId" , so we find this field in one of entity relations

Thus, we get from appication some bucket with predicate expression for the main entity

and in overloaded method FetchEntityCollection () need to add relation ( contains the filed "UserId" with custom filter just before call to base.FetchEntityCollection(...)

In any case I'll try to use the approach you've recomend with other overloaded method ,

but I still hope exists solution for my approach

Best regards,

Alexander

You have to take into account that RelationsCollection might also have its SelectListAlias set, and if so, you have to use that alias in your relations. This is done by the linq provider.

Hi ,

I still have problems with "add on the fly" relations

The code :

linq : 1) var simpleQuery = ( from entity in myMeta.AmitAmit where ( entity.AmitKod == 22 ) select entity );

        List<AmitAmitEntity> requestEntity3 = simpleQuery.ToList();

2) internal class TestDataAccessAdapter : DataAccessAdapter { 2.1 public override void FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, int maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, int pageNumber, int pageSize) { // get filter bucket try {

          base.FetchEntityCollection(collectionToFill, filterBucket, maxNumberOfItemsToReturn, sortClauses, prefetchPath, excludedIncludedFields, pageNumber, pageSize);
    }
    catch(){}       
} ->

protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{
    // add field to query
        // for example , the linq were with AmitAmitEntity 
        IEntity2 entity = new AmitAmitEntity();
        List<IEntityRelation> entityRelationCollection = entity.GetAllRelations();

     IEntityRelation relationToAdd = entityRelationCollection[0];

        relationsToWalk.Add(relationToAdd); // add first of entity relations

// ----- psevdo code for relation collection---------- /// ( // AmitAmitEntity INNER JOIN AmitAmitbecheshbonEntity ON AmitAmitEntity.AmitKod=AmitAmitbecheshbonEntity.AmitKod // )

//================= LINE WHERE FAILED :

        IRetrievalQuery query2 = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);

//============================****

    GEt Exception : "Object reference not set to an instance of an object."


}

}

Where I am wrong ?

Would you post the exception stack trace.

Hi,

Thanks for your response

This is a Exception stack trace you've asked :

at SD.LLBLGen.Pro.DQE.Oracle.OracleSpecificCreator.CreateObjectName(IFieldPersistenceInfo persistenceInfo) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PrepareJoinableFragments(Int32& uniqueMarker, StringBuilder queryText, IEntityRelation relationAsEntityRelation, IDynamicRelation relationAsDynamicRelation, Boolean relationIsDynamicRelation, Boolean& isSingleOperandDynamicRelation, String& pkElement, String& fkElement, String& aliasPKSide, String& aliasFKSide, String& pkElementReference, String& fkElementReference) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Int32& uniqueMarker, Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText(Int32& uniqueMarker) at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, 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, IDbConnection 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, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at WinTestLLblGen.TestDataAccessAdapter.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in c:\ClientControlsQuickStarts\testLLBLGEN\TestDataAccessAdapter.cs:line 165 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at WinTestLLblGen.TestDataAccessAdapter.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) in c:\ClientControlsQuickStarts\testLLBLGEN\TestDataAccessAdapter.cs:line 23

Best regards,

Alexander

Hi Alexander,

I think the problem is that your new relation hasn't persistence info. Try this:

...
IEntityRelation relationToAdd = entityRelationCollection[0];
relationsToWalk.Add(relationToAdd); // add first of entity relations
InsertPersistenceInfoObjects(relationsToWalk);
...

BTW, I notice these two things in your code. These are just notes...: 1. You are instantiating a new AmitAmitEntity in your DataAccessAdapter. That means you are adding a DBGeneric reference into your DBSpecific project. That isn't a best practice. Anyway, it's just a note. You can use IEntityFieldsToFetch to get the object name and then use Activator to create the entity.

1. The code doesn't work for all entities. That will cause you problems I think.

2. When the entity is in inheritance, I think you will have troubles as well, as at that point, inheritance relations were added, so your relations[0] will result in a relation that already was added.

Hi , Daelmo

Thanks for your advice

But, unfortunately , the problem still exists

The fixed code is :

   protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
    {

        IRetrievalQuery originalQuery = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);

        // add field to query
        // for example , the linq were with AmitAmitEntity 
        IEntity2 entity = new AmitAmitEntity();
        List<IEntityRelation> entityRelationCollection = entity.GetAllRelations();

        IEntityRelation relationToAdd = entityRelationCollection[0];

        relationsToWalk.Add(relationToAdd); // add first of entity relations
        InsertPersistenceInfoObjects(relationsToWalk); // by your advice


        IRetrievalQuery query2 = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);

        return query2;
    }

the original query is : SELECT "LPLA_1"."AMIT_KOD" AS "AmitKod", "LPLA_1"."YESHUT_KOD" AS "YeshutKod", "LPLA_1"."CHESHBONOSH_KOD" AS "CheshbonoshKod", "LPLA_1"."TAARICHKARTISMEYDA" AS "Taarichkartismeyda", "LPLA_1"."HAZMANAKARTISMEYDA" AS "Hazmanakartismeyda", "LPLA_1"."CREATIONDATE" AS "Creationdate", "LPLA_1"."CREATIONUSER" AS "Creationuser", "LPLA_1"."UPDATEDATE" AS "Updatedate", "LPLA_1"."UPDATEUSER" AS "Updateuser", "LPLA_1"."ISDELETED" AS "Isdeleted", "LPLA_1"."DELETEDDATE" AS "Deleteddate", "LPLA_1"."ISHASAVA" AS "Ishasava", "LPLA_1"."KVUTZATSHYUCH_KOD" AS "KvutzatshyuchKod", "LPLA_1"."ISKAYAMMANUYINTERNET" AS "Iskayammanuyinternet", "LPLA_1"."TAARICHHAFSAKATPEILUT" AS "Taarichhafsakatpeilut", "LPLA_1"."SIBATHAFSAKATINTERNET_KOD" AS "SibathafsakatinternetKod", "LPLA_1"."ISKAYAMIKULLEAMIT" AS "Iskayamikulleamit", "LPLA_1"."ADAMZMANI_KOD" AS "AdamzmaniKod", "LPLA_1"."ISDOARCHOZER" AS "Isdoarchozer", "LPLA_1"."ISTUYAV" AS "Istuyav", "LPLA_1"."SUGHATZHARA_KOD" AS "SughatzharaKod", "LPLA_1"."HACHELMECHODESH" AS "Hachelmechodesh", "LPLA_1"."TARICHIDKUNHTZHARA" AS "Tarichidkunhtzhara", "LPLA_1"."SUG_LAKOAH" AS "SugLakoah", "LPLA_1"."SIVUG_UCHLUSIA_NAAMAN_KOD" AS "SivugUchlusiaNaamanKod", "LPLA_1"."ZIHUY_NAAMAN_KOD" AS "ZihuyNaamanKod", "LPLA_1"."SHNAT_HATZHARA" AS "ShnatHatzhara", "LPLA_1"."MATZHIR_KOD" AS "MatzhirKod", "LPLA_1"."TAARICH_TCHILAT_TOKEF" AS "TaarichTchilatTokef", "LPLA_1"."TAARICH_SIUM_TOKEF" AS "TaarichSiumTokef", "LPLA_1"."MAKOR_DIVUAH_KOD" AS "MakorDivuahKod", "LPLA_1"."HEAROT" AS "Hearot", "LPLA_1"."TAARICH_DIVUACH_ACHARON" AS "TaarichDivuachAcharon" FROM "KUPAG_B_DEV"."AMIT_AMIT" "LPLA_1" WHERE ( ( ( ( "LPLA_1"."AMIT_KOD" = :AmitKod1)))) - syntax OK

The query generated after relation added( query2) is :

SELECT "LPA_L1"."AMIT_KOD" AS "AmitKod", "LPA_L1"."YESHUT_KOD" AS "YeshutKod", "LPA_L1"."CHESHBONOSH_KOD" AS "CheshbonoshKod", "LPA_L1"."TAARICHKARTISMEYDA" AS "Taarichkartismeyda", "LPA_L1"."HAZMANAKARTISMEYDA" AS "Hazmanakartismeyda", "LPA_L1"."CREATIONDATE" AS "Creationdate", "LPA_L1"."CREATIONUSER" AS "Creationuser", "LPA_L1"."UPDATEDATE" AS "Updatedate", "LPA_L1"."UPDATEUSER" AS "Updateuser", "LPA_L1"."ISDELETED" AS "Isdeleted", "LPA_L1"."DELETEDDATE" AS "Deleteddate", "LPA_L1"."ISHASAVA" AS "Ishasava", "LPA_L1"."KVUTZATSHYUCH_KOD" AS "KvutzatshyuchKod", "LPA_L1"."ISKAYAMMANUYINTERNET" AS "Iskayammanuyinternet", "LPA_L1"."TAARICHHAFSAKATPEILUT" AS "Taarichhafsakatpeilut", "LPA_L1"."SIBATHAFSAKATINTERNET_KOD" AS "SibathafsakatinternetKod", "LPA_L1"."ISKAYAMIKULLEAMIT" AS "Iskayamikulleamit", "LPA_L1"."ADAMZMANI_KOD" AS "AdamzmaniKod", "LPA_L1"."ISDOARCHOZER" AS "Isdoarchozer", "LPA_L1"."ISTUYAV" AS "Istuyav", "LPA_L1"."SUGHATZHARA_KOD" AS "SughatzharaKod", "LPA_L1"."HACHELMECHODESH" AS "Hachelmechodesh", "LPA_L1"."TARICHIDKUNHTZHARA" AS "Tarichidkunhtzhara", "LPA_L1"."SUG_LAKOAH" AS "SugLakoah", "LPA_L1"."SIVUG_UCHLUSIA_NAAMAN_KOD" AS "SivugUchlusiaNaamanKod", "LPA_L1"."ZIHUY_NAAMAN_KOD" AS "ZihuyNaamanKod", "LPA_L1"."SHNAT_HATZHARA" AS "ShnatHatzhara", "LPA_L1"."MATZHIR_KOD" AS "MatzhirKod", "LPA_L1"."TAARICH_TCHILAT_TOKEF" AS "TaarichTchilatTokef", "LPA_L1"."TAARICH_SIUM_TOKEF" AS "TaarichSiumTokef", "LPA_L1"."MAKOR_DIVUAH_KOD" AS "MakorDivuahKod", "LPA_L1"."HEAROT" AS "Hearot", "LPA_L1"."TAARICH_DIVUACH_ACHARON" AS "TaarichDivuachAcharon" FROM ( "KUPAG_B_DEV"."AMIT_AMIT" INNER JOIN "KUPAG_B_DEV"."AMIT_AMITBECHESHBON" ON "KUPAG_B_DEV"."AMIT_AMIT"."AMIT_KOD"="KUPAG_B_DEV"."AMIT_AMITBECHESHBON"."AMIT_KOD") WHERE ( ( ( ( "LPA_L1"."AMIT_KOD" = :AmitKod1))))

so we have the exception : wrong sql query : "ORA-00904: "LPA_L1"."AMIT_KOD": invalid identifier"

I think , the problem is in aliases after linq

Probably there is the way "to restore" original table names instead of "LPA_L1" alias in original query?

Best regards,

Alexander

You should make use of the relationsToWalk.SelectListAlias

A good example was posted here (please inspect the code carefully): http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15230

Hi,

I have a small question :

What the way to change alias in predicate expression like "WHERE ( ( ( ( "LPA_L1"."AMIT_KOD" = :AmitKod1)))) to true DataBase.DataTAble name such as "WHERE ( ( ( ( "DEV.AMIT_AMIT"."AMIT_KOD" = :AmitKod1))))"

I want to do this into override FetchEntityCollection / or into override CreateSelectDQ

Best regards,

Alexander

I think that is not as trivial as you think, depending on the complexity of the predicate expression, if it belongs to inheritance, and if you put on fetchentitycollection or selectdq it will affect all your queries. LLBLGenPro uses a specialized routine to put those aliases and get all queries working perfectly, What do you want to change it?

Hi,

I want to change the alias to the "true" name of DB Table only in some cases into predicate expression , or "copy" this bucket.predicate expression to another predicate expression , using field names .

I try to get field names from predicate expression like this :

   foreach (IPredicateExpressionElement element in filter)
        {
            if (element.Type == PredicateExpressionElementType.Predicate)
            {
                object content = element.Contents;

                FieldCompareValuePredicate fcp = element.Contents as FieldCompareValuePredicate;
                FieldLikePredicate flp = element.Contents as FieldLikePredicate;
                FieldCompareSetPredicate fcs = element.Contents as FieldCompareSetPredicate;
                FieldCompareExpressionPredicate fce = element.Contents as FieldCompareExpressionPredicate;

                IPredicate p = element.Contents as Predicate;
               // string paramName = p.Parameters[0];
            }

        }

only variablev "p" is not null, so casting not worked . Thus I can't get the field name/names

from predicate expression

What the way of reslving this issues ?

Best regards,

Alexander

The way to do what you want is to set the ObjectAlias in fields and predicates to "". But! I really REALLY wouldn't do this, as this is not really going to work in practise as you're modifying objects produced by the linq provider.

The problem is that if you proceed with this and it fails, someone (perhaps you) will post here that some query crashes, we'll spend a lot of time finding the problem (likely debugging the linq provider) which results in wasted time for nothing.

I.o.w.: what you want to do, thus WHY you want to do this has to be done either TOTALLY with linq (so only linq statements) or the query should be written with our own query api so you don't need this complex code as well.

Hi Otis,

Thanks a lot for your response.

If is exists a way to "build" linq query in inherited adapter class and add some relations into ?

The general purpose of my "exersices" is :

1) add filter to some fields not only into main entity but also to the same fields in entity relations ( in case if these fields are not exist in main entity,but exist in some of relations)

Probably you know the right way to do this ?

For example , I want to add predicate expression such as "UserName = "AAA", but field "UserName" dasn't exists in main enity , but in some relation of this entity ( in entity which is corresponds to relation entity collection)

Best regards,

Alexander

So back to square one:

The workflow is : 1) linq query -> 2) query.ToList() -> internal class TestDataAccessAdapter : DataAccessAdapter: 3) //add relations to -> ..... 4) base.FetchEntityCollection(...)

If it's possible to add relations in step 3) ? Probably It's possible to "Copy" bucket to another new bucket with all predicate expressions but without aliases ? In this case it is a solution

You may just try to override the CreateSelectDQ() as shown here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15230

Hi,

Thanks for your response

I have some questions :

There are two scenario's :

case1: I Build bucket as

        RelationPredicateBucket bucket = new RelationPredicateBucket();
                EntityField2 fld2 = GetEntityFieldByName(collectionNonGeneric, "AmitKod");

                bucket.PredicateExpression.Add(MyEntity.Fields.MyField == 155252); // business 

The generated Sql Query** NOT CONTAINS ALIASES**

case2: I build Linq query , the generated SQL Query CONTAINS ALIAS ( for example "LPLA_1")

1)Why exists the difference between case1 and case2 ( in Aliases usage) ?

2) If exists the way to generate SQl Query after linq **WITHOUT ALIASES **?

Best regards,

Alexander

Walaa wrote:

You may just try to override the CreateSelectDQ() as shown here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15230

In the above link, the implementation code uses the alias of the passed in relations collection. Which is available in the SelectListAlias property of the relations collection. Please re-examine the code in the above link closely.

Hi,

Thanks for your response

Unfortunately , the example you've get advice about not suit me.

In my situation all queryes are** LINQ **queryes ,

so when I try to override CreateSelectDQ() I just have the bucket with alias ("LPLA_1")

and cannot change this alias to "true" name of Datatable ( Probably I am wrong ? )

Besides I wish to know the answer on my first and second question in short instead of

some link showing some other case ( not my)

Best regards,

Alexander