reverse mapping - foreign key

i have two tables: one talbe is aspnetUser table with UserID, another table is called administrator with a foreign key called UserID.

these two table is connected by using the UserID.

now how can i generate the c# cs files from OpenAccess. The UserID is not generated at all, and i am not sure how to do it.

please help.
thanks

Hi Jaimie,

With Telerik OpenAccess ORM you could either have a foreign key field (named UserID in your administrator persistent class) or make use of a reference to an object of aspnetUser type.
If you really need the UserID field in your administrator class, you should remove the reference. You could do that from the “Advanced View (Treeview)” tab of the Reverse Mapping wizard. Currently having a foreign key field and a reference at the same time is not directly supported. It will be supported in the upcoming 2009 Q3 release which will be available in a few weeks.

If you really want to have both a field and a reference in the class, you may find the following Knowledge Base article useful: How to make direct access to foreign key fields of persistent classes. It describes a workaround that can be used until the Q3 release.
I hope that helps.

All the best,
Semo
the Telerik team

hey. Thanks for the response, but i am still having no clue how to do it. Let me explain a bit more.

I have 3 tables.

Table 1 is aspnet_Users ( ApplicationId, UserId [pk], Username, etc)
Table 2 is aspnet_Membership (ApplicationId, UserId [pk, fk], Password, Email, etc)
Table 3 is customized table called "Administrator" with a field called UserID. The UserId is a filed used to connect table 1 and table 2 by using UserId (Administrator_Id [pk], UserId [fk], Administrator_FirstName, Administrator_LastName, etc)

The thing i want to achieve is
1. for login screen, i want to get user name , password, email from the login control, return error message if login fails, otherwise, login the user and redirect to home page.
2. In the home page, super administrator could get into the administrator management page to create, update, insert, delete website administrators  (use RadGrid Control, OpenAccessDataSource)

Here are the questions:
1. What is the best and quickest project structure (tier) could i adopt to achieve scenario like this?
2. For 1:1 relationship like this, how can i generate persistant classes from Open Access?
3. Could you provide a small project for this with the sample of the functions i required.

Thanks

Hi Jaimie,

We would like to recommend you to read the following article: Northwind N-Tier Web Demo Application about n-tier web application, which discusses handling CRUD operations with Telerik OpenAccess ORM from our knowledge base articles. You could find the code examples useful as well – Northwind N-Tier Web Demo Application  , CRUD example, Implementing cascading delete with Telerik OpenAccess ORM.

To achieve the CRUD operations in the administrators management page you should use database view for the aspnet_Users, aspnet_Membership, Administrator tables and stored procedures which handle these operations. You should use stored procedures because the view gathers information from three tables and is not updatable. In order to achieve this goal you should follow the steps bellow:

1.    Create the AdministratorView and the necessary stored procedures from the sql.sql file;
2.    Run the Reverse Mapping Wizard for the Data Access Layer project;
3.    Go to the Advanced View tab and locate the Views node from the treeview;
4.    Click on the AdministratorView:
-    Expand the AdministratorView node and click on the UserId. Then check the PrimaryKey checkbox on the right;
-    Choose the Class option and Use Stored Procedures on the right;
-    Inside the Stored Procedure Mapping box you should select the stored procedures for the delete, insert and update operations. In front of the DELETE you should pick the delete stored procedure from the MappedParameter dropdown. Do the same for the insert and the update operations;
5.    Click on the Generate and Save Config button;

After you complete these steps OpenAccessDataSource will use the selected stored procedures for the specified operations;

We changed the type of your thread from Forum to Product Feedback in order to be able to attach a sample solution that you can use as a reference. Steps [2 – 5] are not required for the sample project we provide to you, because they are already executed.

I hope that will help you solving your issue.

All the best,
Damyan Bogoev
the Telerik team

Hello,

I currently downloaded the Q3 2009 OpenAccess ORM and I am having trouble trying to get something that worked pretty simple in Linq to Sql.

I have a Table named Logisitics and Logisitics Assignment:

Logistics:
ID
Title
Job
...

LogisiticsAssignment:
ID
LogisticID
AssignDate
....

When I do the following I get an error:
var logistic = new Logistic { Title = "Some Title", Job = 12345 };
var logisticAssign = new LogisticAssignment { AssignDate = DateTime.Now };

logisitic.LogisticsAssignments.Add(logisticAssign);

scope.Transaction.Begin();
scope.Add(logistic);
scope.Commit();

The Error I get is LogisticsAssignments' LogisticID cannot be null. Shouldnt adding the LogisticsAssignments List do this for me? If I also do the following:
logisticAssign.Logisitic = logistic;
Then it works. However, having to set both sides of the relationship for how I need to do this would be really cumbersome. Is there something I am missing? I am using the Q3 version, and showing the IDs and the References (the default generation on Reverse Mapping).

Hello Corey Gaudin,

To achieve this, you have to enable one option as follows:
1.    Open the Forward Mapping Wizard
2.    Locate and expand the Logistic class node
3.    Select the logisticsAssignment field
4.    On the right side, enable the Manage Collection option
5.    Click on the Done button

Now the LogisticID will be automatically set for each LogisticAssignment object added to the logisitic.LogisticsAssignments collection.

All the best,
Damyan Bogoev
the Telerik team

Damyan,

It would be nice if we had a  Webinar talking about Linq to OpenAccess on Querying, Inserting, Deleting, and Updating in a varying amount of things, like 1 to many, many to many, 1 to 1, in terms of the basic CRUD.

I couldnt find any documentation to tell me to start with a Reverse-Mapping and then do a Forward-Mapping for a vaste amout of new options (I mean it make sense when I did this - but it never occured to me to try that).

Also, is there any way to do updates without the Transaction Begin/Commit? In Linq-to-SQL you have the ability to query out the object, make changes, and do a SubmitChanges on the Context. From the example code that comes with it, it only looks possible with the Transaction.

This is definately not a bad scenario as this ties closely to the Database side of things, but how would I get an Original Object, and the Modified Object and tell my scope to auto-update the object based on its changes? i.e. a Merge changes between original and modified object.

This becomes very necessary with technologies with RIA Services where you will get the Original Object and Modified Object back from the client, and I dont want have to write code to copy each property by hand into a queried out original object from the scope in a transaction (plus this defeats the purpose of the Version on the original object and not have to use Last one wins approach).

Also, when are you planning support for RIA Services? I noticed that the Entity Set returned from a query is not compatible with RIA Services. I know RIA Services is Beta/CTP, but we are putting alot of our weight behind this technology currently, and the ORM that will drive it is also being looked at.

Hello Corey Gaudin,

In fact there is a way to do updates without the Transaction Begin/Commit. To achieve this goal you should use the ITransactionProperties AutomaticBegin property. When set to true, the transaction will automatically started during a Commit or Rollback. Additionally, the transaction is immediately made active when required. When set to false, the transaction is not automatically starting (any more) and the application must make calls to Begin.


We will provide support for RIA Services in the near future.
I hope the provided information will be helpful for you.

Regards,
Damyan Bogoev
the Telerik team