Hi,

How do I create a dynamic list (Typed list) joining two entities not related? I have this two tables CONTXT and OBJ_MAPPING_NX but they are not related, that is, no FK relations exists between CONTXT.contxt_id (numeric datatype) and OBJ_MAPPING_NX.obj_id (char datatype).

CONTXT table is part of an international military organisation standard, so there are parts of the design we cannot change. I could create a view and wrap the statement into a TypedView, but the statement below is a special case checking an erroneous situation, so it is only implemented in an isolated location. Also we are a fairly large organisation which leads to long turn-around time (approvement - implementation - new LLBLGen generation...) before a new view is accessible for the developers.

I would like to produce following simple statement:

SELECT   CONTXT.contxt_id, OBJ_MAPPING_NX.obj_id, OBJ_MAPPING_NX.graph_id
FROM         CONTXT LEFT OUTER JOIN
                      OBJ_MAPPING_NX ON CONTXT.contxt_id = OBJ_MAPPING_NX.obj_id
WHERE    (CONTXT.tactical_layer_id = 'I')

Claus

This SQL Query is not doable in the database without converting Char to int or vice versa. Most probably you will get a converting syntax error.

So I think a view is the best possible solution.

Walaa wrote:

This SQL Query is not doable in the database without converting Char to int or vice versa. Most probably you will get a converting syntax error.

So I think a view is the best possible solution.

Yes, the SQL executes fine without no data conversion (SQLServer 2000). But I read your reply as this is not possible using LLBLGen.

Implementing a view for this is an overkill I think. So I have to do this using .NET SqlCommand, SqlDataReader... etc.

Thanks for your reply.

There is a way, but you have to implement IExpression to execute teh convert function. See: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829 for ideas how to do that.

Then, create an instance of that IExpression implementation and assign it to the field in the EntityRelation object which has to be converted and pass a convert / cast function so you'll get the convert/cast SQL generated into the query.