Hi,

I'm testing how easy it is to use LLBLGenPro to pull a set of records from two tables that have no formal relationship defined between them (because that's how our legacy database is set up).

The following code compiles but throws an "InvalidCastException" when it gets to the "Foreach" statement:

Unable to cast object of type System.DateTime' to type 'db.EntityClasses.AttributeTypeEntity'.

(There are fields in both tables that are of type DateTime.)

Is there a simple way of doing this? I would prefer to use the Linq interface. I would rather not have to define every single field in both tables, and I would prefer to limit database accesses to at most two.

As I understand it, I cannot use "WithPath" because there is not a FK/PK definition in the database.

        protected void joinTest_pullBothTables()
        {
            using (DataAccessAdapter adp = new DataAccessAdapter(connectionString()))
            {
                LinqMetaData MD = new LinqMetaData(adp);

                var q =  from c in MD.AttributeType
                         join d in MD.Aos on c.AttrTypCd equals d.AosCtgCd
                         select new
                         {
                             cc = c,
                             dd = d
                         }; 

                foreach (object o in q)
                    Response.Write(String.Format("{0} <br>", q.ToString()));
            }
         }

Thanks!

You can create virtual relations (and virtual PK's at LLBLGenPro Designer. This way you could have your legacy DB and enjoy the paths and relation objects at code Please read this.

Adding a custom relation? I already tried this, but this wont work because it forces me to link with previously defined primary key fields. I don't always have this luxury in the legacy database I have to use. My questions still stand.

I have a PDF copy of the LLBLGenPro 2.6 documentation and there are 23 occurrances of the word "virtual," none of which refer to "virtual PKs." Is this is in a different document? Are you in fact referring to virtual Foreign Keys? Is it possble to define both virtual FKs and PKs? If not, is there a work-around?

Thanks

DynamicRelations allow you to join between any two fields on any two tables, using a predicate you specify for the join

DynamicRelation dr = new DynamicRelation(EntityType.TestTableEntity ,JoinHint.Inner,EntityType.Table2Entity ,"LeftTable","RightTable",(TestTableFields.Id==Table2Fields.Id ));

Matt

Yep, I've used DynamicRelation. It doen't satisfy all of the conditions that I'd like to satisfy, however, as stated in the original question.

I have a PDF copy of the LLBLGenPro 2.6 documentation and there are 23 occurrances of the word "virtual," none of which refer to "virtual PKs." Is this is in a different document? Are you in fact referring to virtual Foreign Keys? Is it possble to define both virtual FKs and PKs? If not, is there a work-around?

That's the ability to select any field in the designer and check the "Is Part of Primary Key" checkbox. This let you define the PK of an entity, in case the database table doesn't have a PK, which is your case, This allows you to define relations in the Designer.

Otherwise you would have to use Dynamic Relations in the code.

Yep, I've used DynamicRelation. It doen't satisfy all of the conditions that I'd like to satisfy, however, as stated in the original question.

Which conditions or requirements is not satisfied when you use DynamicRelations.

My (admittedly ideal) requirements are:

I would prefer to use the Linq interface. I would rather not have to define every single field in both tables, and I would prefer to limit database accesses to at most two.

Checking the "Is Part of Primary Key" checkbox doesn't get me to where I need to go because the tables already have primary keys defined --- I need to link on fields that are not defined as primary key fields. The field I'm trying to link on is not part of the other primary key fields and really should not be grouped in as such.

It's OK if LLBLGenPro cannot do this. I will eventually come up with a work-around, but before I do I just want to make sure I'm not missing something obvious (since I am new to LLBLGenPro).

Thanks

The initial query you posted doesn't result in the expected result because fetching entities is another pipeline than fetching a projection. (due to inheritance). This leads to a problem in your query because you have a projection of... entities, but these entities don't exist during projection, as it's either entities or projections.

I know this sounds like a lame excuse, though I wished it was easy to implement.

The question also is: what do you want to do with the entities? Is the requirement that you edit the entities and save them later? or do you want to have a readonly list?

what do you want to do with the entities? Is the requirement that you edit the entities and save them later? or do you want to have a readonly list?

About 90% of the time a readonly list is fine; about 10% of the time there will be updates.

The main reason why I ask is because some of the tables I work with have 50+ fields, and it is inconvenient to have to list each field.

I understand what you are saying about projections, but if the projection could be defined within Linq as a set of Entities that would be ideal.

You didn't answer the following question.

Quote: Yep, I've used DynamicRelation. It doen't satisfy all of the conditions that I'd like to satisfy, however, as stated in the original question.

Which conditions or requirements is not satisfied when you use DynamicRelations.

DynamicRelations don't use the Linq syntax.

vanekl wrote:

what do you want to do with the entities? Is the requirement that you edit the entities and save them later? or do you want to have a readonly list?

About 90% of the time a readonly list is fine; about 10% of the time there will be updates.

The main reason why I ask is because some of the tables I work with have 50+ fields, and it is inconvenient to have to list each field.

I can imagine

I understand what you are saying about projections, but if the projection could be defined within Linq as a set of Entities that would be ideal.

I know, however currently that's not possible, I'm afraid. For readonly lists, you have to specify the list of fields you want in the list. I don't think that will be all the fields in the entities for most occasions.

For the situations where you need to update the entities, fetch them separately, and filter on the related entity if required. That's the only way to get this done at the moment. If you then want to create a tuple from them, you can join the lists in-memory using linq to objects.