How to work with Views

[Scott Davis]

I have used my own database layer for years and I am new to using an ORM such as OpenAccess, so please forgive my ignorance.  I have tried to find the answer to this on my own for a couple of days and am at a dead end.  I am sure that I what I am trying to do is simple.

I have a table named Prospect having various fields. I have created my OA data model from the database.  I can work with the Prospect object just fine.  I have another table named Attachment that I need to JOIN to and retrieve an additional field, let's just say the name of that field is AttachmentUrl.  I have written a view named vw_Prospect that does this JOIN and retrieves this field along with all of the fields of the Prospect table.  Ideally, I would like to add the AttachmentUrl property to the Prospect object (read only) and have my Prospect object use this view when retrieving Prospect objects from the database, but continue to Create, Update and Delete as normal.

I am unable to figure out the way this should work with an ORM such as OpenAccess. Using partial classes I have extended the Prospect class to have additional properties and methods that I do not want to duplicate in an entirely new object created from vw_Prospect.

If anyone can help me with the general way that views are handled with OpenAcess, I would greatly appreciate it.

Thank You!

[Kristian Nikolov]

Hi Scott,

To access views using Telerik OpenAccess ORM you need to first include them in your model using the Chose Database Items screen. Depending on whether you are initially creating your model from the database, or you are updating an existing model, you can find the mentioned screen in either Create Model Wizard or Update from Database Wizard.

Assuming you have already created a Domain Model, these would be the steps you need to follow to include and use your view:

1. Double-click on your .rlinq file to open Visual Designer.
2. Right-click on an empty space of the designer`s surface.
3. Chose Update From Database... and the wizard will appear.
4. Expand the Views node and chose the views you would like to add - vw_Prospect in your case.
5. Click Next and then Finish to complete the wizard.
6. You will notice that the view is now included among the classes displayed in Visual Designer.
7. Upon trying to build the project, a warning and an error will be displayed. They will notify you that the view`s class and table do not have a Primary Key. Double-click on the warning and the error and choose a primary key.
8. You can now read data from the view through your context just as you would a normal table:
   

   var prospects = context.vw_Prospect.ToList();

Please note that we recommend you to only use views to retrieve data and not attempt to execute CUD operations through them.

Regarding adding the view as a property to your Prospect class, I believe that is unnecessary assuming you have created a 1:m or 1:n association between Prospect and Attachment. Having such association will result in Navigation Properties which would allow you to access related entities.



Since you are new to OpenAccess ORM, I would suggest you take a look at the Getting Started section in our documentation. There you will find information on the different ways of creating a database model and then consuming it. You may also take a look at our Samples Kit to explore scenarios with various complexity.

I hope you will find this information helpful.

Regards,
Kristian Nikolov
Telerik

[Scott Davis]

Kristian...thanks for the reply.  I really appreciate it.

I had previously done what you prescribed.  The area where I ran into some confusion was the fact that my Prospect class which had already been created using the Update From Database wizard had been extended (using partial classes) to include some additional Properties.  These are calculated read only properties or properties that format some of the actual database fields in a certain consistent way for use across the entire application.

In creating the new class based on the view (vw_Prospect) using the Update From Database wizard, this new class did not have those properties.  I did not want to have to reproduce them in two separate classes and could not see an immediately obvious use of inheritance to accomplish this.

I suppose one thing I could do is put the calculated properties in the view class and remove them from the Prospect class. Then always use the view class for reading the objects from the database for display purposes and the Prospect class (based on the table) for CRUD.  However, I would like to avoid having to use two objects like this if possible.

Hopefully I am explaining my confusion in a non-confusing manner.  I have read the docs and taken a look at some samples, but have not seen exactly how to do this.  Hence, my post to the forums.

Thank You!

[Kristian Nikolov]

Hi Scott,

If I understood you correctly, your goal is to be able to access the AttachmentUrl for each Prospect object, through the Prospect object itself. If indeed that is the case, I can suggest you the following solution.

The fact that in your view, you are joining the Attachment and Prospect tables, tells me that they are in either 1:1 or 1:m relationship. That means that you can use the Navigation Properties generated by OpenAccess ORM to access the AttachmentUrl(s) through the Prospect object to which it is/they are related, without ever needing to use the view. This way you can still use your additional properties without the need to duplicate them or use two different types of objects.

For the purpose of the example let us assume that you have a 1:m relationship between Prospect and Attachment. Then the model would look like that:



Notice, the properties under the Navigation node in each of the persistent classes. Through the Attachments property of each Prospect object, you can access all of its related Attachment objects. Through the Prospect property of each Attachment object you can access its related Prospect object.

Additionally you can tell OpenAccess to load the related Attachment entities at the same time the Prospect entities are loaded by using Fetch Plans. That way no additional queries to the database will be made when accessing the Attachment property.

If in your database, you have several Prospect entries, and Attachments entries are available for them, the following code will allow you to access the AttachmentUrl for each of the Prospect entries:

ProspectModelContext context = new ProspectModelContext();
List<Prospect> prospectObjects;

using (context)
{
    //define a fetch stragey
    FetchStrategy loadWithAttachments = new FetchStrategy();
    loadWithAttachments.LoadWith<Prospect>(prospect => prospect.Attachments);

    //set the fetch strategy to the context
    context.FetchStrategy = loadWithAttachments;

    //retrieve the required prospect objects
    //their attachments will be retrieved from the database at the same time
    prospectObjects = context.Prospects.Where(prospect=> prospect.ProspectId > 0).ToList();
}

//retrieve one of the related attachment urls
string prospect1AttachmentUrl1 = prospectObjects[0].Attachments[0].AttachmentUrl;

//retrieve all related attachment urls
List<String> prospect1AttachmentUrls = prospectObjects[0].Attachments.Select(attachment => attachment.AttachmentUrl).ToList();

I hope you find this solution applicable to your scenario.

Regards,
Kristian Nikolov
Telerik

[Scott Davis]

Thanks again Kristian. I learned something from your last post.

I have provided you with a very poor example of the issue that I am having I am afraid.  So, let me start again with a fresh example as I still have not found a solution that I am entirely happy with.

Let's say I have a Prospect table as follows:

PROSPECT TABLE
ProspectID int
OrganizationID int
FirstName varchar(50)
LastName varchar(50)
Height double

an Organization table as follows:
ORGANIZATION TABLE
OrganizationID int
OrganizationName varchar(50)

The field Height is stored in inches in the database as one value.  However, it is always displayed in the UI as feet and inches.  To accomplish this I have partial class file for Prospect with a property defined called HeightDisplay which calculates the feet and inches and returns a formatted string (ie...the value 70 in the Height field would be returned as 5'10" by the HeightDisplay property).  In addition, I define some other properties in this partial class that return just the number of feet in Height and just the number of left over inches in Height.  These properties are named HeightFt and HeightIn.  I could have other such properties as well such as FullName which will return the Prospects FirstName and LastName fields formatted together in one string.

The point is, I have defined quite a few "calculated" properties in the partial class file for Prospect.  They are defined as properties so that I can easily bind to them in something such as a DataGrid, just by retrieving a collection of Prospect objects.

Now, I decide that I also have a need to display the OrganizationName that the Prospect belongs to.  The Prospect is related to the Organization table through OrganizationID.  Let's assume that the Organization table has many fields (I have only shown two above).  All I need for my purposes with Prospect is the OrganizationName.  So I define a view called ProspectView which joins to the Organization table and returns all of the fields of Prospect along with the OrganizationName.

I now update my model from the database which creates an object named ProspectView (I get everything working as you described in your very first reply).  I want to bind a DataGrid to ProspectView which shows the following information:

FullName
HeightDisplay
OrganizationName

Now the problem that I have is that the properties that I created in the partial Prospect class are not available to me in the new ProspectView class.  So I don't have a FullName and HeightDisplay property to work with.  I have to redefine them in the ProspectView class, thus creating duplicate code, which I would prefer to avoid.

I know that I can access the OrganizationName through the Organization navigational property on the Prospect object rather simply and do away with the need for ProspectView in this example.  However, let's assume that the information I need in my view requires a more complex JOIN than that, which is my real world case, and requires that view.

I have these properties that are defined in the Prospect partial class that really apply to the Prospect object and to the ProspectView object and my goal is to not duplicate them in code.

I have tried defining an abstract class with these properties defined and implemented in the abstract class and deriving the Prospect and ProspectView objects from the abstract class.  However, the abstract would require access to fields that are defined in the Prospect and ProspectView generated classes (ie....HeightDisplay requires access to the Height field).  Since I can't modify the generated classes, this solution doesn't work because I need to define, for instance, the Height property in my abstract class and then I would need to use the override or new keyword on the Height property in the generated classes...which I can't modify.

Hopefully, I have done a better job of explaining my situation.  I was trying to keep the example simple, but I think that may have just made it look like I didn't know the very basics of using OA.

[Kristian Nikolov]

Hi Scott,

Thank you for the detailed explanation. If I have understood correctly your needs, I can suggest you two approaches that may help you resolve the situation.

The first approach is to create a Prospect object for each ProspectView object you wish to display. This way you will be able to retrieve your data through your view, yet take advantage of the calculated properties defined in Prospect without duplicating them. To be able to do that, you will need to add the following in your partial class:

1. Add a constructor without arguments
2. Add a constructor which takes as an argument a ProspectView object. Here you should initialize the Prospect object with the respective values from the passed argument.
3. Add a field to hold the ProspectView object.
4. Duplicate the required specific properties of the ProspectView object. As an alternative you can expose the ProspectView through a property if that is acceptable for your binding scenario.

Here is a sample code for the partial Prospect class:

public partial class Prospect

{

    private ProspectView prospectView;

 

    public Prospect()

    {

    }

 

    public Prospect(ProspectView prospectView)

    {

        this._prospectId = prospectView.ProspectId;

        this._firstName = prospectView.FirstName;

        this._lastName = prospectView.LastName;

        this._height = prospectView.Height;

        this.OrganizationId = prospectView.OrganizationId;

 

        this.prospectView = prospectView;

    }

 

    //calculated properties

    public string DisplayHeight

    {

        get

        {

            return "Display height in custom format: " + this.Height;

        }

    }

 

    public string FullName

    {

        get

        {

            return this.FirstName + " " + this.LastName;

        }

    }

 

 

    //duplicate view`s properties

    public string OrganizationName

    {

        get

        {


                 if(this.prospectView!=null)
                 {
                      return this.prospectView.OrganizationName;
                 }


        }

    }

 

    //or expose the view itself

    public ProspectView ProspectView

    {

        get

        {

            return this.prospectView;

        }

    }

}

And here is a usage sample of the above code:

ProspectContext context;

List<Prospect> prospects = new List<Prospect>();

using (context = new ProspectContext())
{
          var prospectViewEntries = context.ProspectViews.Where(org=> org.OrganizationId > 0).ToList();

          foreach (ProspectView view in prospectViewEntries)
          {
              prospects.Add(new Prospect(view));
          }
}

Prospect p1 = prospects[0];

string fullName = p1.FullName;
string displayHeight = p1.DisplayHeight;
string organizationName = p1.OrganizationName;

Note that the Prospect objects created out of a ProspectView objects are not connected to the OpenAccess context - you wont be able to update or delete entries with them.

The second approach is to create an association between the ProspectView and Prospect based on ProspectId and define a Fetch Strategy, requiring the related Prospect object to be loaded immediately.



This way, using a navigation property in the ProspectView objects you will be able to access the calculated properties of the Prospect object associated with the current ProspectView. The drawback is that you will be loading the data for the Prospect objects two times - one for the view itself and one for its Prospect navigation property. Therefore this approach becomes less applicable if your data volumes are higher. 

ProspectContext context;

List<ProspectView> viewEntries;

using (context = new ProspectContext())
{
    FetchStrategy loadWithProspect = new FetchStrategy();
    loadWithProspect.LoadWith<ProspectView>(view => view.Prospect);
    context.FetchStrategy = loadWithProspect;

    viewEntries = context.ProspectViews.Where(view => view.OrganizationId > 0).ToList();
}

ProspectView viewEntry = viewEntries[0];

string fullName = viewEntry.Prospect.FullName;
string displayHeight = viewEntry.Prospect.DisplayHeight;
string organizationName = viewEntry.OrganizationName;

I hope these approaches will help you with your scenario.

Regards,
Kristian Nikolov
Telerik

[Scott Davis]

Thanks again for the reply.  I was hoping there would be a more elegant solution than having to load up both objects one way or another.  If I come up with one, I will post it here.