version 1.0.2005.1 final (self-servicing) VS2005 asp.net 2.0

hiya,

I have some code that successfully returns product info if I supply the categoryId:

IPredicateExpression filtProductCategory = new PredicateExpression();
filtProductCategory.Add(CskStoreProductCategoryMapFields.CategoryId == 1);
    
dalHamilton.TypedListClasses.TListPromoProductsTypedList tListPromoProducts = new TListPromoProductsTypedList();
    
IRelationCollection relationsToUse = tListPromoProducts.BuildRelationSet();
        relationsToUse.Add(dalHamilton.EntityClasses.CskStoreProductEntity.Relations.CskStoreProductCategoryMapEntityUsingProductId);
        
TypedListDAO dao = DAOFactory.CreateTypedListDAO();
dao.GetMultiAsDataTable(tListPromoProducts.BuildResultset(), tListPromoProducts, 3, null, filtProductCategory, relationsToUse, false, null, null, 0, 0);    

Good so far, however, I want to find the categoryId using the categoryGUID of tblCategory.

Schema:

CSK_Store_Category categoryId PK categoryGUID

CSK_Store_Product_Category_Map productID PK categoryId PK

Can I do the above with the code that I have, or do I have to make an additional call to the database, so that I can obtain the "categoryId" via the categoryGUID arg?

please let me know if I should clarify anything.

cheers,

yogi

Hi,

I think you only need to extend your relation set by providing the second relation between categorymap and category.

Then change your predicate to match the guid.

The second inner join will get added as expected.

Basically, you're not limited to a single level of inner joins. You may navigate amongst many table's relations. The only restriction is that if you pass several times through the same table, then you need to specify corresponding object aliases.

thanks bud.

I think you only need to extend your relation set by providing the second relation between categorymap and category.

I think I am struggling even at the first hurdle :-(

This is my current relation:

IRelationCollection relationsToUse = tListPromoProducts.BuildRelationSet();   
     relationsToUse.Add(dalHamilton.EntityClasses.CskStoreProductEntity.Relations. CskStoreProductCategoryMapEntityUsingProductId);

I am not sure which entity I should add to the relation??Is it this?

relationsToUse.Add(dalHamilton.EntityClasses.CskStoreCategoryEntity. Relations.CskStoreProductCategoryMapEntityUsingCategoryId);

I ask because I don't actually KNOW the categoryID yet..I need to pass the categoryGUID so that I can obtain the categoryID.

I hope this makes sense.

many thanks,

yogi

The code should look like the following:

IPredicateExpression filtProductCategory = new PredicateExpression();
filtProductCategory.Add(CskStoreCategoryFields.categoryGUID == "ANY_VALUE");
        
IRelationCollection relationsToUse = tListPromoProducts.BuildRelationSet();
relationsToUse.Add(dalHamilton.EntityClasses.CskStoreProductEntity.Relations. CskStoreProductCategoryMapEntityUsingProductId).SubPath( dalHamilton.EntityClasses.CskStoreProductCategoryMapEntity.Relations. CskStoreCategoryEntityUsingCategoryId);

cheers Walaa,

I'm getting an error here:

relationsToUse.Add(dalHamilton.EntityClasses.CskStoreProductEntity.Relations. CskStoreProductCategoryMapEntityUsingProductId).SubPath(dalHamilton.EntityClasses. CskStoreProductCategoryMapEntity.Relations.CskStoreCategoryEntityUsingCategoryId);

error: CS0117: 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityRelation' does not contain a definition for 'SubPath'

I have looked in objectBrowser and see that “SubPath” is a method in << SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPathElement>>

This is all quite new to me.Do you know where I could be going wrong?

many thanks,

yogi

I'm very sorry, I didn't had my cop of coffee this morning. I mixed up Relateions with PrefetchPaths, SubPath is used with prefetchPaths only.

Try the following code instead:

IPredicateExpression filtProductCategory = new PredicateExpression();
filtProductCategory.Add(CskStoreCategoryFields.categoryGUID == "ANY_VALUE");
        
IRelationCollection relationsToUse = tListPromoProducts.BuildRelationSet();
relationsToUse.Add(dalHamilton.EntityClasses.CskStoreProductEntity.Relations.CskStoreProductCategoryMapEntityUsingProductId);
relationsToUse.Add(dalHamilton.EntityClasses.CskStoreProductCategoryMapEntity.Relations.CskStoreCategoryEntityUsingCategoryId);

hiya Walaa,

thanks, I think i am almost there:

IPredicateExpression filtProductCategory = new PredicateExpression();
        
        filtProductCategory.Add(dalHamilton.HelperClasses.CskStoreCategoryFields.CategoryGuid == categoryGUID);
        
        
IRelationCollection relationsToUse = tListPromoProducts.BuildRelationSet();
        relationsToUse.Add(dalHamilton.EntityClasses.CskStoreProductEntity.Relations. CskStoreProductCategoryMapEntityUsingProductId);
        relationsToUse.Add(dalHamilton.EntityClasses.CskStoreProductCategoryMapEntity. Relations.CskStoreCategoryEntityUsingCategoryId);
        
TypedListDAO dao = DAOFactory.CreateTypedListDAO();

good so far.However, I get an error here:

dao.GetMultiAsDataTable(tListPromoProducts.BuildResultset(), tListPromoProducts, 3, null, filtProductCategory, relationsToUse, false, null, null, 0, 0);

<<Failed to convert parameter value from a String to a Guid>>

I think that it is struggling with the GUID, but I can't work out why.Is it a size issue?

sorry to ask.

yogi

Hi,

can you post the full stack trace ?

hiya,

of course:

" at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)\r\n at System.Data.SqlClient.SqlParameter.GetCoercedValue()\r\n at System.Data.SqlClient.SqlParameter.Validate(Int32 index)\r\n at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)\r\n at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase. ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IEntityFields fieldsToReturn)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize)\r\n at dalHamilton.DaoClasses.TypedListDAO.GetMultiAsDataTable(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize) in D:\\dalHamShopEquip\\DaoClasses\\TypedListDAO.cs:line 55\r\n at Catalog.BindProductList() in d:\\yogiHamFromLibrary\\Catalog.aspx.cs:line 125\r\n at Catalog.LoadPage() in d:\\yogiHamFromLibrary\\Catalog.aspx.cs:line 102\r\n at Catalog.Page_Load(Object sender, EventArgs e) in d:\\yogiHamFromLibrary\\Catalog.aspx.cs:line 62\r\n at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)\r\n at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)\r\n at System.Web.UI.Control.OnLoad(EventArgs e)\r\n at System.Web.UI.Control.LoadRecursive()\r\n at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)"

cheers,

yogi

filtProductCategory.Add(dalHamilton.HelperClasses.CskStoreCategoryFields.CategoryGuid == categoryGUID);

Would you please verify the Type of the generated field "CskStoreCategoryFields.CategoryGuid" ?

What's the type of "categoryGUID"? would you please post the code of its creation?

cheers folks,

I had to convert categoryGUID (which was a string, to a GUID)..

It then worked.

many thanks,

yogi