Several questions about best pratices using OpenAccess

[Manuel]

Hi telerik,

I want question you about best pratices of using your ORM. This is because my company bought your components and ORM and we have some problems of performance in this silverlight application, and I wondering if there is some problem of our implementation.

One of the problems is the time that application takes to load the data. Here is what iḿ doing in each silverlight usercontrol.

 1- In constructor of my usercontrol I instanciate my context (database object), and using this same context in all usercontrol. Some problem here? I read about it, and find that the good is use the context inside using statment, but I can implement that, because i need first implement some interface, is that true? If yes? you can provide some example?

2- The second thing, is load the data from database. Another doubt here. Imagine that in my usercontrol I want to show a list of clients and the companies. For this create to different methods like LoadClient and LoadCompanies and in this methods I load the data from database using loadOperation? This is the best way? Because for two list I go two times to database and load all table for application?

3 - Another question is about FetchStrategy. For default ORM use lazy fetch, that is a good thing. But, If I want change some table to eager, where I cant do this? In Model Class that ORM generate?

4- ManyToOne. Where I can specify the type of relationship? In the presistence class? Because Imagine that in my client class I have a list of companies. When I load the client class, iḿ loading to the companies objects, and this can be huge. Where i can define when load clients + companies and only clients for example.

Sorry for the big text, but i need an answers. If you can provide examples too, I apreciate.

Thanks in advance,

[Ivailo]

Hello Manuel,

First I would like to recommend you to download and refer to our SDK application - it contains plenty of samples that might prove useful for your scenario. One of the samples - "Sofia Car Rental - RIA services" - is for Silverlight project and it is demonstrating the usage of RIA services for providing the data to the user interface layer. In case you are using WCF Data Services, you can take a look at the WPF project "Sofia Car Rental - WCF Data Services" for a reference how to use them.

To get to your questions:

1) What is the user control platform - is it Silverlight? If it is, you have to consume a service in order to bring the data to your user interface, which means that you don't have to manage the context but only to make service calls for each operation. If the user control is an ASP.NET control, you can apply the best practices for web applications that we are showing in the SDK example "Managing OpenAccess Context" within the ASP.NET category. In order to access this sample, please download the latest internal build of the SDK, called Telerik-OpenAccess-SDK-2011.3.1320.1.zip (you can find it here). 

2) You can check out this article for some details about handling related data. 

3) Currently the Fetch Strategies are defined on context level, which means that if you want a certain query to load eagerly some related objects, you can do so for the entire context and then remove the definition, as shown here. In the upcoming Q1 2012 release of OpenAccess ORM you will be able to define different plans for each query as well.

4) This relationship should be defined in the Domain Model - as shown in this article. Loading data is actually controlled by the Fetch plans so you can refer to (3) for details - just make sure the proper associations are set already in the model.

Do not hesitate to get back to us if you need any clarifications.

All the best,
Ivailo
the Telerik team

[Manuel]

Hi,

Thanks for the reply. However I still have some questions.

I´ll show you in attach an example of my application, and want i´m doing to retrieve the data.

In this image, I have one RadDataForm, and RadDataGrids inside RadTabs.

And to fill my dataForm I have one class "REUNIOES" than means "MEETINGS" that are relationated with ESTADOS_REUNIAO, TIPOS_REUNIAO.

For this information, I have the follow model class, generated by ORM.

namespace SGA.Web    
{
    [Table("REUNIOES")]
    
    [KeyGenerator(KeyGenerator.Autoinc)]
    public partial class Reunioes
    {
        private long _Id;
        [Column("ID", OpenAccessType = OpenAccessType.Decimal, IsBackendCalculated = true, IsPrimaryKey = true, Length = 22, SqlType = "NUMBER")]
        [Storage("_Id")]
        public virtual long IdProp
        {
            get
            {
                return this._Id;
            }
            set
            {
                this._Id = value;
            }
        }
        
        private long _Organizacaoid;
        [Column("ORGANIZACAO_ID", OpenAccessType = OpenAccessType.Decimal, Length = 22, SqlType = "NUMBER")]
        [Storage("_Organizacaoid")]
        public virtual long OrganizacaoIdProp
        {
            get
            {
                return this._Organizacaoid;
            }
            set
            {
                this._Organizacaoid = value;
            }
        }
        
        private long _Tiporeuniaoid;
        [Column("TIPO_REUNIAO_ID", OpenAccessType = OpenAccessType.Decimal, Length = 22, SqlType = "NUMBER")]
        [Storage("_Tiporeuniaoid")]
        public virtual long TipoReuniaoIdProp
        {
            get
            {
                return this._Tiporeuniaoid;
            }
            set
            {
                this._Tiporeuniaoid = value;
            }
        }
        
        private long _Estadoreuniaoid;
        [Column("ESTADO_REUNIAO_ID", OpenAccessType = OpenAccessType.Decimal, Length = 22, SqlType = "NUMBER")]
        [Storage("_Estadoreuniaoid")]
        public virtual long EstadoReuniaoIdProp
        {
            get
            {
                return this._Estadoreuniaoid;
            }
            set
            {
                this._Estadoreuniaoid = value;
            }
        }
        
        private short _Ano;
        [Column("ANO", OpenAccessType = OpenAccessType.Decimal, Length = 4, SqlType = "NUMBER")]
        [Storage("_Ano")]
        public virtual short AnoProp
        {
            get
            {
                return this._Ano;
            }
            set
            {
                this._Ano = value;
            }
        }
        
        private short _Numero;
        [Column("NUMERO", OpenAccessType = OpenAccessType.Decimal, Length = 4, SqlType = "NUMBER")]
        [Storage("_Numero")]
        public virtual short NumeroProp
        {
            get
            {
                return this._Numero;
            }
            set
            {
                this._Numero = value;
            }
        }
        
        private string _Localreuniao;
        [Column("LOCAL_REUNIAO", OpenAccessType = OpenAccessType.Varchar, Length = 400, SqlType = "NVARCHAR2")]
        [Storage("_Localreuniao")]
        public virtual string LocalReuniaoProp
        {
            get
            {
                return this._Localreuniao;
            }
            set
            {
                this._Localreuniao = value;
            }
        }
        
        private string _Obs;
        [Column("OBS", OpenAccessType = OpenAccessType.Varchar, IsNullable = true, Length = 2000, SqlType = "NVARCHAR2")]
        [Storage("_Obs")]
        public virtual string ObsProp
        {
            get
            {
                return this._Obs;
            }
            set
            {
                this._Obs = value;
            }
        }
        
        private DateTime _Datahoraprevistainicio;
        [Column("DATA_HORA_PREVISTA_INICIO", OpenAccessType = OpenAccessType.Date, SqlType = "DATE")]
        [Storage("_Datahoraprevistainicio")]
        public virtual DateTime DataHoraPrevistaInicioProp
        {
            get
            {
                return this._Datahoraprevistainicio;
            }
            set
            {
                this._Datahoraprevistainicio = value;
            }
        }
        
        private DateTime? _Datahorainicio;
        [Column("DATA_HORA_INICIO", OpenAccessType = OpenAccessType.Date, IsNullable = true, SqlType = "DATE")]
        [Storage("_Datahorainicio")]
        public virtual DateTime? DataHoraInicioProp
        {
            get
            {
                return this._Datahorainicio;
            }
            set
            {
                this._Datahorainicio = value;
            }
        }
        
        private short? _Duracaoprevista;
        [Column("DURACAO_PREVISTA", OpenAccessType = OpenAccessType.Decimal, IsNullable = true, Length = 4, SqlType = "NUMBER")]
        [Storage("_Duracaoprevista")]
        public virtual short? DuracaoPrevistaProp
        {
            get
            {
                return this._Duracaoprevista;
            }
            set
            {
                this._Duracaoprevista = value;
            }
        }
        
        private short? _Duracao;
        [Column("DURACAO", OpenAccessType = OpenAccessType.Decimal, IsNullable = true, Length = 4, SqlType = "NUMBER")]
        [Storage("_Duracao")]
        public virtual short? DuracaoProp
        {
            get
            {
                return this._Duracao;
            }
            set
            {
                this._Duracao = value;
            }
        }
        
        private short _Sit;
        [Column("SIT", OpenAccessType = OpenAccessType.Decimal, Length = 1, SqlType = "NUMBER")]
        [Storage("_Sit")]
        public virtual short SitProp
        {
            get
            {
                return this._Sit;
            }
            set
            {
                this._Sit = value;
            }
        }
        
        private string _Insuser;
        [Column("INS_USER", OpenAccessType = OpenAccessType.Varchar, Length = 40, SqlType = "NVARCHAR2")]
        [Storage("_Insuser")]
        public virtual string InsUserProp
        {
            get
            {
                return this._Insuser;
            }
            set
            {
                this._Insuser = value;
            }
        }
        
        private DateTime _Insdata;
        [Column("INS_DATA", OpenAccessType = OpenAccessType.Date, SqlType = "DATE")]
        [Storage("_Insdata")]
        public virtual DateTime InsDataProp
        {
            get
            {
                return this._Insdata;
            }
            set
            {
                this._Insdata = value;
            }
        }
        
        private string _Altuser;
        [Column("ALT_USER", OpenAccessType = OpenAccessType.Varchar, IsNullable = true, Length = 40, SqlType = "NVARCHAR2")]
        [Storage("_Altuser")]
        public virtual string AltUserProp
        {
            get
            {
                return this._Altuser;
            }
            set
            {
                this._Altuser = value;
            }
        }
        
        private DateTime? _Altdata;
        [Column("ALT_DATA", OpenAccessType = OpenAccessType.Date, IsNullable = true, SqlType = "DATE")]
        [Storage("_Altdata")]
        public virtual DateTime? AltDataProp
        {
            get
            {
                return this._Altdata;
            }
            set
            {
                this._Altdata = value;
            }
        }
        
        private short _Versao;
        [Column("VERSAO", OpenAccessType = OpenAccessType.Decimal, Length = 4, SqlType = "NUMBER")]
        [Storage("_Versao")]
        public virtual short VersaoProp
        {
            get
            {
                return this._Versao;
            }
            set
            {
                this._Versao = value;
            }
        }
        
        private string _Designacao;
        [Column("DESIGNACAO", OpenAccessType = OpenAccessType.Varchar, IsNullable = true, Length = 400, SqlType = "NVARCHAR2")]
        [Storage("_Designacao")]
        public virtual string DesignacaoProp
        {
            get
            {
                return this._Designacao;
            }
            set
            {
                this._Designacao = value;
            }
        }
        
        private EstadosReuniao _Estadosreuniao;
        [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
        [ForeignKeyAssociation(ConstraintName = "R_ER_FK", SharedFields = "EstadoReuniaoIdProp", TargetFields = "IdProp")]
        [Storage("_Estadosreuniao")]
        public virtual EstadosReuniao EstadosreuniaoProp
        {
            get
            {
                return this._Estadosreuniao;
            }
            set
            {
                this._Estadosreuniao = value;
            }
        }
        
        private Organizacoes _Organizacoes;
        [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
        [ForeignKeyAssociation(ConstraintName = "R_O_FK", SharedFields = "OrganizacaoIdProp", TargetFields = "IdProp")]
        [Storage("_Organizacoes")]
        public virtual Organizacoes OrganizacoesProp
        {
            get
            {
                return this._Organizacoes;
            }
            set
            {
                this._Organizacoes = value;
            }
        }
        
        private TiposReuniao _Tiposreuniao;
        [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
        [ForeignKeyAssociation(ConstraintName = "R_TR_FK", SharedFields = "TipoReuniaoIdProp", TargetFields = "IdProp")]
        [Storage("_Tiposreuniao")]
        public virtual TiposReuniao TiposreuniaoProp
        {
            get
            {
                return this._Tiposreuniao;
            }
            set
            {
                this._Tiposreuniao = value;
            }
        }
        
        private IList<ParticipantesReuniao> _Participantesreuniaos = new List<ParticipantesReuniao>();
        [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
        [Collection(InverseProperty = "ReunioesProp")]
        [Storage("_Participantesreuniaos")]
        public virtual IList<ParticipantesReuniao> ParticipantesReuniaos
        {
            get
            {
                return this._Participantesreuniaos;
            }
        }
        
        private IList<Notificacoes> _Notificacoes = new List<Notificacoes>();
        [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
        [Collection(InverseProperty = "ReunioesProp")]
        [Storage("_Notificacoes")]
        public virtual IList<Notificacoes> Notificacoes
        {
            get
            {
                return this._Notificacoes;
            }
        }
        
        private IList<DocumentosReuniao> _Documentosreuniaos = new List<DocumentosReuniao>();
        [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
        [Collection(InverseProperty = "ReunioesProp")]
        [Storage("_Documentosreuniaos")]
        public virtual IList<DocumentosReuniao> DocumentosReuniaos
        {
            get
            {
                return this._Documentosreuniaos;
            }
        }
        
        private IList<Assuntos> _Assuntos = new List<Assuntos>();
        [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
        [Collection(InverseProperty = "ReunioesProp")]
        [Storage("_Assuntos")]
        public virtual IList<Assuntos> Assuntos
        {
            get
            {
                return this._Assuntos;
            }
        }

        [Telerik.OpenAccess.Transient()]
        private string _cor;

        public string Cor
        {
            get
            {
                return _cor;
            }
            set
            {
                _cor = value;
            }
        }
    }
}

and as you can see in bold, I try to force the Lazy fetch in related objects, like ESTADOS_REUNIAO, TIPOS_REUNIAO.

And here are my presistence class for REUNIOES object:

namespace SGA.Web
{
    [MetadataTypeAttribute(typeof(Reunioes.ReunioesMetadata))]
    public partial class Reunioes
    {
        internal sealed class ReunioesMetadata
        {
            [Key]
            public long IdProp { get; set; }

            //TIPOS REUNIAO
            [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
            [Association("TipoReuniao_Reuniao", "TipoReuniaoIdProp", "IdProp")]
            [Include]
            public TiposReuniao TiposreuniaoProp { get; set; }

            //PARTICIPANTES REUNIAO
            [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
            [Association("ParticipantesReuniao_Reuniao", "IdProp", "ReuniaoIdProp")]
            [Include]
            public IList<ParticipantesReuniao> ParticipantesReuniaos { get; set; }

            //ESTADO REUNIAO
            [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
            [Association("EstadoReuniao_Reuniao", "EstadoReuniaoIdProp", "IdProp")]
            [Include]
            public EstadosReuniao EstadosreuniaoProp { get; set; }
            
            //ANEXOS
            //[Association("Anexos_Reuniao", "IdProp", "ReuniaoIdProp")]
            //[Include]
            //public IList<Anexos> Anexos { get; set; }

            //DOCUMENTOS REUNIAO
            [Telerik.OpenAccess.LoadBehavior(Telerik.OpenAccess.LoadBehavior.Lazy)]
            [Association("Documentos_Reuniao", "IdProp", "ReuniaoIdProp")]
            [Include]
            public IList<DocumentosReuniao> DocumentosReuniaos { get; set; }
        }
    }
}

Now, as you can see in this DataForm, I some table: Information of REUNIOES, TIPOS_REUNIAO, etc.

And to retrive this information, I use LoadOperator for each table, that means, that to retrieve information of REUNIOES I use one method to load REUNIOES:

public void CarregaReunioesPorParticipante()
        {
            try
            {
                BloquearLeituraEscritaDados = true;
                LoadOperation<Reunioes> loadOp = contextoSGA.Load(contextoSGA.GetReuniaoQuery(AppGlobais.Globais.reuniaoGlobal.IdProp), LoadBehavior.RefreshCurrent, CarregaReunioesPorParticipanteCompleted, false);
            }
            catch (Exception Erro)
            {
                parametrosJanela.Closed = null;
                parametrosJanela.Header = "Erro carregar reunião";
                parametrosJanela.Content = "Ocorreu um erro ao carregar os dados da reunião\n" + Erro.Message;
                RadWindow.Alert(parametrosJanela);
            }
        }

        void CarregaReunioesPorParticipanteCompleted(LoadOperation<Reunioes> loadOperation)
        {
            try
            {
                List<Reunioes> listaReunioes;
                listaReunioes = loadOperation.Entities.ToList();
                OCReunioes = new ObservableCollection<Reunioes>(listaReunioes);
                
                SeparaReunioes();
            }
            catch (Exception Erro)
            {
                parametrosJanela.Closed = null;
                parametrosJanela.Header = "Erro carregar reunião";
                parametrosJanela.Content = "Ocorreu um erro ao carregar os dados da reunião\n" + Erro.Message;
                RadWindow.Alert(parametrosJanela);
            }
            CarregaEstadosReuniao();
        }

Another mehod to load ESTADOS_REUNIAO:

public void CarregaEstadosReuniao()
        {
            try
            {
                LoadOperation<EstadosReuniao> loadOp = contextoSGA.Load(contextoSGA.GetEstadosReuniaosQuery(true), LoadBehavior.RefreshCurrent, CarregaEstadosReuniaoCompleted, false);
            }
            catch (Exception Erro)
            {
                parametrosJanela.Header = "Erro a carregar os estados da reunião";
                parametrosJanela.Content = "\nOcorreu um erro a carregar os estados da reuniao.\n" + Erro.Message;
                RadWindow.Alert(parametrosJanela);
            }
        }

        void CarregaEstadosReuniaoCompleted(LoadOperation<EstadosReuniao> loadOperation)
        {
            try
            {
                List<EstadosReuniao> listaEstadosReuniao;
                listaEstadosReuniao = loadOperation.Entities.Where(ER => ER.SitProp == 1).ToList();
                OCEstadosReuniao = new ObservableCollection<EstadosReuniao>(listaEstadosReuniao);
            }
            catch (Exception Erro)
            {
                parametrosJanela.Header = "Erro a carregar os estados da reunião";
                parametrosJanela.Content = "\nOcorreu um erro a carregar os estados da reuniao.\n" + Erro.Message;
                RadWindow.Alert(parametrosJanela);
            }

            CarregaTiposReuniao();
        }

Another for TIPOS_REUNIAO, etc. So, for each table that I need to retreve and for user can choose option in edit mode I load 3 or 4 tables to this page, this is a lot of time.

What you recommend to do this? What is the best pratice? How I can load all this, without go some may times to database?

Another question: After take a look at your example in www.telerik.com/help/openaccess-orm/getting-started-root-quickstart-sl-wcfria-load-related-objects.html

using System.ServiceModel.DomainServices.Client;
using System.Windows.Controls;
using OA.SL4.RIA.Demo.Web;
using SofiaCarRentalEntityDiagramsModel;
namespace OA.SL4.RIA.Demo
{
   public partial class MainPage : UserControl
   {
       private SofiaCarRentalDomainContext dbContext = new SofiaCarRentalDomainContext();
       public MainPage()
       {
           InitializeComponent();
           LoadOperation<Customer> loadOperation = this.dbContext.Load( this.dbContext.GetCustomersQuery() );
           this.DataContext = loadOperation.Entities;
       }
       private void CustomersCombo_SelectionChanged( object sender, SelectionChangedEventArgs e )
       {
           if ( this.CustomersCombo.SelectedItem == null )
               return;
           Customer customer = this.CustomersCombo.SelectedItem as Customer;
           LoadOperation<Car> loadOperation = this.dbContext.Load<Car>( this.dbContext.GetCarByCustomerNameQuery( customer.FullName ) );
           this.CarsGrid.ItemsSource = loadOperation.Entities;
       }
   }
}

When I try do like this  LoadOperation<Customer> loadOperation = this.dbContext.Load( this.dbContext.GetCustomersQuery() ); in my context, LoadOperation doesn´t retrieve any data? Why?


Thanks in advance,

[PetarP]

Hello Manuel,

 Basically if you want to retrieve a graph of objects with just one call to the database the only way to go is with our fetch plan API. What it basically does is to instruct OpenAccess which reference properties will be retrieved together with the root one. Additional information can be found here.
As for the items not being loaded in your data service call - since the call itself is not synchronous it is possible that during debug you will not be able to see the records immediately. It is best if you can subscribe to the completed event of the loadOperation variable. If you put a break point in that even you will be able to see if there are any exceptions that prevent your service from obtaining the data.

Kind regards,
Petar
the Telerik team