Hi,

I am having some trouble converting SQL into LLBLGen. This may be rather lengthly but please bear with me as I will try to give you as much info as possible. Also, I am really new to LLBLGen .

We are using .NET 2.0, Adapter, designer version 1.0.2005.1, SQL Server 2005

We have a multi-lingual set up that allows us to define strings for tables / objects for different languages. (I would post a diagram, but don’t have access to the site right now). For Example, a Product name may have 3 different strings; one for English, Spanish and French. The Product name is identified by Product.NameStringID.

These strings are defined in a separate table (StringItem) and contain StringItemID (PK), StringGroupID, LanguageID (FK) and String (the actual string value). The StringGroupID is the identifier for a string (NameStringID). StringGroupID is NOT unique, in this example there will be 3 rows in StringItem with StringGroupID being the same, the difference would be the languageID.

How we use this: Get the languageId selected by the user and use the Product.NameStringID + languageId to get the actual string value.

So here is the problem. Using Adapter how do I load all the strings for an object (Product)?

Using SQL a solution (perhaps not the best) would be:

SELECT P.*, NameString.String AS [Name], ShortDescription.String AS ShortDescription FROM Product P
JOIN StringItem AS NameString ON P.NameStringID = NameString.StringGroupID AND NameString.LanguageID = @languageId
JOIN StringItem AS ShortDescription ON P.ShortDescriptionStringID = ShortDescription.StringGroupID AND ShortDescription.LanguageID = @languateId

Where @languageId = 1

I cannot, for the life of me figure out how to get something like this to work with Adapter. With Self-Serving it is pretty easy; I can avoid the joins because a simple load on demand for the string works great.

We have tried numerous things such as setting up a UC on Product.NameStringID and making a relationship to StringItem.StringGroupID, which works fine SQL 2005 but LLBLGen won’t recognize. I have tried adding custom relationships in both the designer and in code, neither of which worked.

Thanks for the help,

Joe

<<EDIT>> If this helps, here is another way to do in SQL:

SELECT P.*, 
    (SELECT StringItem.String FROM StringItem WHERE StringItem.StringGroupId = P.NameStringID AND StringItem.LanguageID = 1) AS [Name],
    (SELECT StringItem.String FROM StringItem WHERE StringItem.StringGroupId = P.ShortDescriptionStringID AND StringItem.LanguageID = 1) AS ShortDescription
FROM Product P

Ok First of all you need LLBLGen pro to recognize the relation between the Product Entity & the StringItem Entity (Product.NameStringID = StringItem .StringGroupID)

And since StringGroupID is not the PK and it's not Unique, then a relation can't be defined in the Designer, but you can define it manually in your code.

public EntityRelation(
   IEntityField2 primaryKeyField,
   IEntityField2 foreignKeyField,
   RelationType typeOfRelation
);

You can the use add you relation to the RelationPredicateBucket object associated with the Fetch method.

Hi Walaa,

Thanks for the reply.

I created the realtionship in code as you indicated. Do I not need a prefetchpath to fill the StringItemEnitity? How do I add a prefetchpath from a custom relation to populate StringItemEntity and how do I get the data out of StringItemEntity once it has been populated?

Here is what I have so far:

EntityCollection productCollection = new EntityCollection(new ProductEntityFactory());

IRelationPredicateBucket filter = new RelationPredicateBucket();
EntityRelation nameStringRelation = new EntityRelation(ProductFields.NameStringId, StringItemFields.StringGroupId, RelationType.OneToMany);

filter.Relations.Add(nameStringRelation);
filter.PredicateExpression.Add(StringItemFields.LanguageId == 1);

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductEntity);
        
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
            adapter.FetchEntityCollection(productCollection, filter, prefetchPath);
}

I was reading through the docs about how to extend the framework through inheritance and I assume this is what I am going to have to do. But one thing at a time

Sorry for the complete noob questions. I come from a straight SQL background and am having trouble with the mind-shift to LLBLGen.

Joe

I think you should be adding a PrefetchPathElement to the PrefetchPath object. Please check the LLBLGen Pro Reference Manual for the

public PrefetchPathElement(
   IEntityCollection retrievalCollection,
   IEntityRelation relation,
   int destinationEntityType,
   int toFetchEntityType,
   int maxAmountOfItemsToReturn,
   ISortExpression sorter,
   IPredicateExpression filter,
   IRelationCollection filterRelations,
   string propertyName,
   RelationType typeOfRelation
);

Although as you said, the best implementation is to Inherit Entities (for EachLanguage)from the String Table.

Or to have database Views for each Language. And map them to Entities which will have direct relation with your Product Entity (StringGroupId will be Unique in Every View)

Ok, I have decided to go with the inheritance route. But instead of inherting new StringItems because languages can be added via our admin tool and then I would have to create a new language, recomplie etc. I will inherit from ProductEnitity to make a CustomProductEntity.

CustomProductEntity will expose a property NameStringItems (EntityCollection of StringItems).

So in order to do this I have created a few new classes; CustomProductEntity, CustomProductEntityFactory and CustomProductRelations.

Let me know if I am on the right track here. Here is the code:

public class CustomProductFactory : ProductEntityFactory
{
    public CustomProductFactory() { }

    public override IEntity2 Create()
    {
        return base.Create();
    }

    public override IEntity2 Create(IEntityFields2 fields)
    {
        return base.Create(fields);
    }
}
public class CustomProductEntity : ProductEntity
{
    public CustomProductEntity() : base() { }
    public CustomProductEntity(Guid productId) : base(productId) { }
    public CustomProductEntity(IEntityFields2 fields) : base(fields) { }

    public EntityCollection NameStringItems
    {
        // Return StringItems for NameStringId
        get { return null; }
    }

    public static CustomProductRelations Relations
    {
        get { return new CustomProductRelations(); }
    }


    public static IPrefetchPathElement2 PrefetchPathNameStringItems
    {
        get
        {
            return new PrefetchPathElement2(new EntityCollection(new StringItemEntityFactory()),
                CustomProductEntity.Relations.StringItemsUsingNameStringId,
                (int)Pointfolio.LLBLGen.EntityType.ProductEntity, (int)Pointfolio.LLBLGen.EntityType.StringItemEntity, 0, null, null, null, null, "StringItem", RelationType.OneToMany);
        }
    }
}
public class CustomProductRelations : Pointfolio.LLBLGen.RelationClasses.ProductRelations
{
    public virtual IEntityRelation StringItemsUsingNameStringId
    {
        get
        {
            IEntityRelation relation = new EntityRelation(RelationType.OneToMany);
            relation.StartEntityIsPkSide = true;
            relation.AddEntityFieldPair(EntityFieldFactory.Create(ProductFieldIndex.NameStringId), EntityFieldFactory.Create(StringItemFieldIndex.StringGroupId));
            relation.InheritanceInfoPkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("ProductEntity", true);
            relation.InheritanceInfoFkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("StringItemEntity", false);
            return relation;
        }
    }
}

All this allows me to do this:

 EntityCollection productCollection = new EntityCollection(new CustomProductFactory());
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(CustomProductEntity.Relations.StringItemsUsingNameStringId);
        
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductEntity);
prefetchPath.Add(CustomProductEntity.PrefetchPathNameStringItems);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
     adapter.FetchEntityCollection(productCollection, filter, prefetchPath);
}

A couple things though: 1. For the EntityType in PrefetchPath2((int)EntityType.ProductEntity) do I leave it as this or do I have to create a new EntityType index? 2. How do I populate a custom property that is an EntityCollection?

Thanks,

Joe

Earlier when I spoke about Inheritance, I meant the option available by the LLBLGen Pro Designer to create a SubType Entity from another Entity.

1. For the EntityType in PrefetchPath2((int)EntityType.ProductEntity) do I leave it as this or do I have to create a new EntityType index?

Polymorphic Prefetch Paths should work (generally were used with SubTypes generated by LLBLGen Pro)

Although you could have inserted your custom code in the generated Product Entity class without inheriting from it (I didn't see a need for that inheritance).

You don't have to write any manual code. There's a problem though, your relation between product name and the actual set of strings isn't setup properly as in: it doesn't have a PK side. So creating a collection inside the product entity for the names in various languages is a bit of a problem.

As a lot of strings probably have a translated version, I don't think it's useful to create a lot of subtypes on the StringItems table, as that would create perhaps thousands of entities if your project grows bigger.

What could help is this: Create a StringType table: TypeID (int, PK) Description (varchar, not that important but has to be there).

The value of Product.NameStringID is a value of TypeID, so NameStringID is an FK to StringType.TypeID.

Then, in StringItems, the StringGroupID is an FK to StringType.TypeID as well. This gives you the path to load strings for a given stringtype in an entity. So to load a product and the namestring for English, you'd do: create a prefetch path like (pseudocode):

path.Add(ProductEntity.PrefetchPathNameStringStringType ).SubPath.Add(StringType.PrefetchPathStringItems, [b]language filter...[/b])

You can then traverse the string in memory and display it. It won't be as nice as expected perhaps, but it's the easiest way to fetch the data and most maintainable. The bold language filter is a predicate on StringItems.LanguageID, so you can fetch more than one string for a productname if you want to or just 1.

If you're displaying products in a read-only fashion, you can then also add the properties to a typed list, as this way you can set it up easily in the designer.

In your model, you dropped StringType. This then causes the designer not to allow you the relation between Product.NameStringID and StringItem.StringGroupID, as no side is a PK.

Hi Otis,

We acutally have a setup like this but I was trying to avoid the extra relationship and I kept having trouble getting the designer to recognize it. Until now.

I had a UC on Product.NameStringID and was trying to use that as the PK to StringGroup (which contains a StringGroupID and Name). This table is used for our GUI multi-lingual controls so we can attach a custom name property instead of a GUID. Anyway, apparently I had the relationship backwards. I did what you recommened, tied the PK of StringGroup to the NameStringID of Product (still a UC) and the designer picks it up. So now there is a 1:1 from Product to StringGroup and 1:n from StringGroup to StringItem.

Implemented this code:

EntityCollection productCollection = new EntityCollection(new ProductEntityFactory());

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(ProductEntity.Relations.StringGroupEntityUsingNameStringId);
filter.Relations.Add(StringGroupEntity.Relations.StringItemEntityUsingStringGroupId);

filter.PredicateExpression.Add(StringItemFields.LanguageId == 1);

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductEntity);
prefetchPath.Add(ProductEntity.PrefetchPathNameStringGroup).SubPath.Add(StringGroupEntity.PrefetchPathStringItem);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(productCollection, filter, prefetchPath);
}

This looks great, nice neat and simple. One problem however; When I execute the code I get a Missing Method Exception in ProductEntity.

System.MissingMethodException was unhandled by user code
  Message="Method not found: 'Void SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.OnEntityAfterSave(System.Object, System.EventArgs)'."
  Source="Pointfolio.LLBLGen.DatabaseGeneric"
  StackTrace:
       at Pointfolio.LLBLGen.EntityClasses.ProductEntity.SetupSyncNameStringGroup(IEntity2 relatedEntity)
       at Pointfolio.LLBLGen.EntityClasses.ProductEntity.set_NameStringGroup(StringGroupEntity value) in C:\Documents and Settings\jyoung\My Documents\LLBLGen Pro Projects\Pointfolio_v2\DatabaseGeneric\EntityClasses\ProductEntity.cs:line 1553
       at Pointfolio.LLBLGen.EntityClasses.ProductEntity.SetRelatedEntityProperty(String propertyName, IEntityCore entity) in C:\Documents and Settings\jyoung\My Documents\LLBLGen Pro Projects\Pointfolio_v2\DatabaseGeneric\EntityClasses\ProductEntity.cs:line 286
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.MergeNormal(IEntityCollection2 rootEntities, IPrefetchPathElement2 currentElement, Boolean rootEntitiesArePkSide)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchParameterisedPrefetchPath(IEntityCollection2 rootEntities, Int64 maxNumberOfItemsToReturn, IPrefetchPath2 prefetchPath)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, IPrefetchPath2 prefetchPath)
       at _Default.BindData() in c:\Documents and Settings\jyoung\My Documents\Visual Studio 2005\WebSites\Testing\LLBLGenTest\Default.aspx.cs:line 48
       at _Default.Page_Load(Object sender, EventArgs e) in c:\Documents and Settings\jyoung\My Documents\Visual Studio 2005\WebSites\Testing\LLBLGenTest\Default.aspx.cs:line 29
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

It looks to me like it is trying to setup a callback after the NameStringGroup is saved. All StringGroups and StringItems that are tied to an Entity such as this need only to be readonly. Saving the strings is done manualy. We are trying to stay away from Views as much as possible.

Can't thank you and Walaa enough for your help.

Thanks, Joe

Missing method exceptions is commonly caused by the fact that you compile against vX, but in the runtime folder of your application (e.g. the asp.net bin folder) an old version of the ormsupportclasses is located. OR, you referenced the .NET 1.0 ormsupportclasses dll and you're using .net 1.1 (sometimes happens, they're close together in the reference select dialog).

Could you check that please?

The Bin folder contains ORMSupportClasses.NET20.dll and DQE.SqlServer.NET20.dll. I am using Visual Studio 2005 so all code should be using .NET 2.0.

I double checked the references for DatabaseGeneric and DBSpecific and they have NET20 as well.

jyoung wrote:

The Bin folder contains ORMSupportClasses.NET20.dll and DQE.SqlServer.NET20.dll. I am using Visual Studio 2005 so all code should be using .NET 2.0.

I double checked the references for DatabaseGeneric and DBSpecific and they have NET20 as well.

Ok, but check the builddate of the ORMSupportclasses dll. Rightclick on the dll in the bin folder in explorer -> properties -> version tab. You're likely using an old version, as that method is in the base class since the RTM of 1.0.2005.1 if I'm not mistaken ,but was in the generated code during the beta.

You are right!

My referenced dlls were for .NET 1.1. I got the new dlls and no error!

I now have a working ProductEntity.

Thanks a TON for all the help.

Joe