We have a Db with fields that resolve to a dual key lookup table. The 2nd part of the key is a languageID which is determined at runtime. We are trying to come up with a clean & clever way to use the Entity as designed, but have these lookups resolved when loaded based on the Language ID.

E.g. Table1: Field1: TableID int Field2: Name int (LookupType)

TableLookup: Field1: LookupType int Field2: LanguageID int Field3: Value nvarchar

We would like to have the Entity be able to do something like:

Table1.NameResolved (which is Field2 resolved to the Value field in TableLookup with a prespecified LanguageID).

This is made even more complicated by the fact that the lookup value for that LanguageID may not always exist and thus will need to fall back on a different default LanguageID. There will be many tables using this convention.

Maybe there is a simple solution, but this seems like a lot of work to implement.

Edit: I am thinking of editing the entity template and changing the property get accessor to perform the lookup. I need to be able to check the sql type of the field and verify that it is a sql custom type (which designates the lookup). Is this possible?

When dealing with multiple languages, I seperate the language specific content into a seperate table. So Product (1:m) ProductDetails

e.g

Table Product - ProductId (PK) - Color (FK) etc

Table ProductDetails - ProductId (PK) - LanguageCode (PK) - Description

It really just an entity split across two tables but LLBLGEN doesn't support this, so you would need to create a ProductManager (or similar) object to encapsulate the insert, updates, deletes that take place on the underlying Entity Objects.

In order to select the information out you can use a query with the following structure, which basically says, "if its available in the target language return that, else return the default language"

Note: I use stored procs because I'd already written them but in theory you should be able to LLBLGEN it.

CREATE PROCEDURE [dbo].[SelectProduct] 

    @Target_Language char(2), 
    @Default_Language char(2),
    @Product_Key int

AS 

BEGIN

    SELECT 
                    P.*, PD.* 
    FROM 
                    Product P 
        
    INNER JOIN 
            ProductDetails PD 

        ON      
                P.Product_Key = PD.ProductDetails_Key 
            
        AND         (PD.ProductDetails_LanguageCode = @Target_Language  OR PD.ProductDetails_LanguageCode = @Default_Language) 
    
        AND     P.Product_key = @Product_Key
        
    LEFT JOIN 
            ProductDetails PD2 

        ON      
                P.Product_Key = PD2.ProductDetails_Key 
                
        AND         PD2.ProductDetails_LanguageCode = @Target_Language 

    WHERE 
                
        PD2.ProductDetails_LanguageCode IS NULL OR PD.ProductDetails_LanguageCode = @Target_Language 



END

Hope it helps and if you do LLBLGEN it, it would be great if you could post it

Cheers,

Pete

lab4bear said:

It really just an entity split across two tables but LLBLGEN doesn't support this

It does support that using Inheritance

Actually, your right, I should have been more clear. Multiple language support using inheritance is possible but when i discussed it with Frans in another post I think we determined it wasn't viable when the number of languages is dynamic.

See the bottom of this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5020&HighLight=1

Cheers, Pete

Is there a way in the template to check the sql type of a field and compare it as a custom sql server type? This would allow me to modify the entity template and create alternate get accessors that resolve the multilingual value.

If not, any thoughts on a work around? Any recommended (free) code gen system that would provide access to the custom sql server type?

What exactly do you mean with custom sql server type? A userdefinedtype in sqlserver2000 or a UDT in sqlserver2005?

Otis wrote:

What exactly do you mean with custom sql server type? A userdefinedtype in sqlserver2000 or a UDT in sqlserver2005?

Apologies - Yes, UDT.

Ideally, I would want to modify the template as such: psuedo: <[ if FieldTypeIsUDT && FieldType == "MyUDT" ]> my_csharp_code <[ endif ]>

Although maybe it should just be accomplished like this:

if (<[ FieldType ]> == "MyUDT") { my_csharp_code }

I guess maybe I answered my own Q - Will have to try this later.

Well, I took the "tedious" approach and did it with custom properties. Even though I will have to add a custom prop to hundreds of fields, at least I generated the code exactly how I want:

        <[ Foreach CustomProperty EntityField]><[If StringValueEquals CustomPropertyName "IsMultiLingual"]>public string <[EntityFieldName]>Localized

The more I think abstractly the more I realize we can do.