retrofitting a new relationship into an existing lightspeed codebase

hello

I have an existing project with Lightspeed entities that back onto sql tables. (although I notice there is no lsmodel file in the project)

I wish to establish a relationship (referential integrity) between two tables but this isn’t currently possible because of the schema

“Table A”

Id (PK)

DocumentId

“Table B”

DocumentIdSource

DoucmentIdDestination

Category

DocumentId isn’t a PK in Table A so I can’t add a FK to Table B. and I want to join to multiple rows in table A

Can anyone suggest what my options are here?

My objective is to be able to filter my Lightspeed entities for “Table A” where they relate to “Table B” and have a specified Category In one query.

thanks, Andrew

We require every table to have an Id column so that looks problematic for Table B.

Outside of that you can just do a manual query to find all Table A's given either a source/destination and a specific category by joining Table B to Table A on DocumentIdSource/DocumentIdDestination -> DocumentId and then with an QueryExpression of

Entity.Attribute("Category") == "specified Category";

Jeremy

thanks for the info, Jeremy. I was also thinking of the option of a database View (instead of table B) and back the lightspeed entity onto that, and have Table A relate to that instead?

Yes that would work, presuming you wanted to handle both relationships you could have 2 views where one had DocumentIdSource as the identity and the other had DocumentIdDestination as the identity.

You could then form a relationship from A with DocumentId as the foreign key field.

Jeremy

hi - one more question. how will Lightspeed assume a relationship between Table A and the View if views can't have primary or foreign keys? thanks