Round trip scenario not working with some foreign keys

[Andrew]

I have an rlinq model that is giving me some trouble. I'd like to package my code so a developer can pull it down from source control, and create the database on their machine from the rlinq model directly by using update database from model.

To reproduce this I first generate the model from my existing database, and everything looks good (my foreign keys are recognized and proper navigation properties are made, etc.). If I immediately try and update the database from this model (no changes), there are a few foreign keys that it wants to remove. If I delete the database and have it create it from scratch it never even creates the foreign keys.

Even though there are appropriate associations shown in the diagram for these foreign keys, if I inspect the SQL it generates it creates an index for the foreign key fields but not the foreign key constraint itself.

Can you help me with this issue?

Thanks,
Andy

[Boyan]

Hello Andrew,

The behavior you are experiencing is certainly not expected given that you have uploaded the entire Telerik Data Access model project (not just the .rlinq file) and that you use the Update From Database Wizard. Could you please confirm that this is the case? If so could we kindly ask you to attach the.rlinq file with your next post so we could further investigate the cause of the issue? If not, please detail the step you took to reproduce the issue.

Similar behavior could occur if you use xml mapping and .rlinq model and you would like to update the schema by code using the Schema Handler. If this is indeed the case please let us know so we could provide you with information on how to proceed in this situation.

In order to allow you to attach files to this thread I will convert its thread type.

I am looking forward to hearing from you.

Regards,
Boyan
Telerik

 

 

[Andrew]

Hi Boyan,

If I understand your question, that is correct I have the rlinq model in its own project that gets referenced by the main web app. How do I upload a file? I don't see that option. Will the upload be publicly visible?

Thanks.

[Andrew]

OK, I think I've narrowed it down to a cause. It appears to happen when two tables have foreign keys to each other. It can be reproduced with the following steps:

1) Create Employee entity with Id (PK), DepartmentId, and Name properties
2) Create Department entity with Id (PK), ManagerEmployeeId, and Name properties
3) Make DepartmentId an association to the department entity
4) Make ManagerEmployeeId an association to the employee entity

Create the database using "update database from model" and notice no foreign keys are created. Let me know if there's something special I need to do in this case. Thanks.

[Boyan]

Hi Andrew,

Thank you for the detailed information. Using it we were able to reproduce the same set-up and behavior that you described (please excuse me for the misunderstanding and disregard my previous comment on uploading the .rlinq as it think it would not be needed now).

This is indeed excepted as with such schema you described each table is dependent on the other one and this would cause issues during run-time namely when inserting entities.

The recommended approach to model such relationship between tables would be to use a join table to create many-to-many relation. 

Please let us know if using a join table is a possible option in your situation so we could advice you on further steps in that case.

I am looking forward to your feedback.

Regards,
Boyan
Telerik

 

 

[Andrew]

I should be able to use a join table to model this scenario. The reason I went with this approach is that the foreign key allowed easy access to the related entity without having to go through a join table. The only reason it worked was because both foreign keys were nullable and I could insert one record at a time. I think I can work around this now, although a suggestion for a future enhancement of the product would be to give a warning in the output window when the model contains such a scenario.

Thanks for the help.

[Boyan]

Hello Andrew,

I am glad that you have found the suggested solution feasible with your scenario.

Thank you for your suggestion. Regarding it, if you find such logging enhancements useful and you would like to see it in Telerik Data Access we would really appreciate if you post a feature request at our idea and feedback portal describing it in detail. Voting for it will help us prioritize its eventual implementation.

Do not hesitate to contact us if you have any more questions.

Regards,
Boyan
Telerik