Related Classes, Interfaces, and Querying

[Garry]

I'm still in the architect stage of my app and I have decided to use OA. However I can't quite get my mind around using objects/classes when it comes to querying and creating my interfaces.

Basically I get stuck at this point. I have 2 reversed mapped classes, an interface, and a DataService class that I have listed below. The classes osParticipant and osEntitiy are related, along with a bazillion other tables, to one another. The problem I have is how to construct my interface to say that I am going to return a generic list containing objects or classes that contain fields from both of the related classes?

I can see where doing a simple IList<osEntity> or a IList<osParticipant> would be easy, but how do I do a IList<osEntity + osParticipant>? I read a thread on the forums where it was said that if tables are related a property would be mapped to the related class and a query such as this:

var query = from userData in scope.Extent<OsEntity>()
                        select new
                        {
                            ID = userData.EntityID,
                            LastName = userdata.LastName,
                            FirstName = userdata.FirstName,
                            IsActive = userdata.OSParticipant.IsActive
                        };

would be possible, however I am not seeing a property in any of the classes that were created and if this does work how would you return a collection of the result. I mean what type would be in the Collection?

Also to expand the question,  the osParticipant table has related tables such as an Address table that is a one to many relation. How would doing a query such as the one above handle this scenario? I am assume a property in osParticipant would be a collection that would hold the many addresses, but I do not see that being the case in any of the classes that OA generated.

Anyhow as you can see I am throughly confused at this point and any help you could offer would be greatly appreciated. I think if you could offer a quick sample/example then a light bulb will go off and I can have one of thos aha moments!

I went through the OA Examples that come with it, but to tell you the truth I had a hard time pulling out what I was looking for. I looked like some queries to the OrderDetails was done in the DBManager class, but then there were a ton of other releated persistent classes that I could not see you pulling from at all.

Thanks in advance!

osParticipant

using System;
using System.Collections.Generic;

namespace EliteExtender.Infrastructure.Models.Elite
{
    // Generated by Telerik OpenAccess
    // Used template: c:\program files (x86)\telerik\openaccess orm\sdk\IDEIntegrations\templates\PCClassGeneration\cs\templates\classgen\class\partialuserdefault.vm
    // NOTE: Field declarations and 'Object ID' class implementation are added to the 'designer' file.
    //       Changes made to the 'designer' file will be overwritten by the wizard.
    public partial class OsParticipant
    {
        //The 'no-args' constructor required by OpenAccess.
        public OsParticipant()
        {
        }

        [Telerik.OpenAccess.FieldAlias("pk")]
        public int Pk
        {
            get { return pk; }
            set { this.pk = value; }
        }

        [Telerik.OpenAccess.FieldAlias("creationDate")]
        public DateTime? CreationDate
        {
            get { return creationDate; }
            set { this.creationDate = value; }
        }

        [Telerik.OpenAccess.FieldAlias("fkMLSosEntityModuleRole")]
        public int FkMLSosEntityModuleRole
        {
            get { return fkMLSosEntityModuleRole; }
            set { this.fkMLSosEntityModuleRole = value; }
        }

        [Telerik.OpenAccess.FieldAlias("fkosCompanyModule")]
        public int FkosCompanyModule
        {
            get { return fkosCompanyModule; }
            set { this.fkosCompanyModule = value; }
        }

        [Telerik.OpenAccess.FieldAlias("fkosEntity")]
        public int FkosEntity
        {
            get { return fkosEntity; }
            set { this.fkosEntity = value; }
        }

        [Telerik.OpenAccess.FieldAlias("fkosEntityModuleSubRole")]
        public int? FkosEntityModuleSubRole
        {
            get { return fkosEntityModuleSubRole; }
            set { this.fkosEntityModuleSubRole = value; }
        }

        [Telerik.OpenAccess.FieldAlias("isActive")]
        public string IsActive
        {
            get { return isActive; }
            set { this.isActive = value; }
        }

        [Telerik.OpenAccess.FieldAlias("lastEditDate")]
        public DateTime? LastEditDate
        {
            get { return lastEditDate; }
            set { this.lastEditDate = value; }
        }


    }
}

osEntity

using System;
using System.Collections.Generic;

namespace EliteExtender.Infrastructure.Models.Elite
{
    // Generated by Telerik OpenAccess
    // Used template: c:\program files (x86)\telerik\openaccess orm\sdk\IDEIntegrations\templates\PCClassGeneration\cs\templates\classgen\class\partialuserdefault.vm
    // NOTE: Field declarations and 'Object ID' class implementation are added to the 'designer' file.
    //       Changes made to the 'designer' file will be overwritten by the wizard.
    public partial class OsEntity
    {
        //The 'no-args' constructor required by OpenAccess.
        public OsEntity()
        {
        }

        [Telerik.OpenAccess.FieldAlias("pk")]
        public int Pk
        {
            get { return pk; }
            set { this.pk = value; }
        }

        [Telerik.OpenAccess.FieldAlias("creationDate")]
        public DateTime? CreationDate
        {
            get { return creationDate; }
            set { this.creationDate = value; }
        }

        [Telerik.OpenAccess.FieldAlias("disguisedTaxID")]
        public string DisguisedTaxID
        {
            get { return disguisedTaxID; }
            set { this.disguisedTaxID = value; }
        }

        [Telerik.OpenAccess.FieldAlias("entityID")]
        public string EntityID
        {
            get { return entityID; }
            set { this.entityID = value; }
        }

        [Telerik.OpenAccess.FieldAlias("externalVendorNumber")]
        public string ExternalVendorNumber
        {
            get { return externalVendorNumber; }
            set { this.externalVendorNumber = value; }
        }

        [Telerik.OpenAccess.FieldAlias("fileNameAs")]
        public string FileNameAs
        {
            get { return fileNameAs; }
            set { this.fileNameAs = value; }
        }

        [Telerik.OpenAccess.FieldAlias("firstName")]
        public string FirstName
        {
            get { return firstName; }
            set { this.firstName = value; }
        }

        [Telerik.OpenAccess.FieldAlias("fkosProgramAffiliation")]
        public int? FkosProgramAffiliation
        {
            get { return fkosProgramAffiliation; }
            set { this.fkosProgramAffiliation = value; }
        }

        [Telerik.OpenAccess.FieldAlias("fullName")]
        public string FullName
        {
            get { return fullName; }
            set { this.fullName = value; }
        }

        [Telerik.OpenAccess.FieldAlias("initials")]
        public string Initials
        {
            get { return initials; }
            set { this.initials = value; }
        }

        [Telerik.OpenAccess.FieldAlias("isCompany")]
        public string IsCompany
        {
            get { return isCompany; }
            set { this.isCompany = value; }
        }

        [Telerik.OpenAccess.FieldAlias("isExcludedFrom1099")]
        public string IsExcludedFrom1099
        {
            get { return isExcludedFrom1099; }
            set { this.isExcludedFrom1099 = value; }
        }

        [Telerik.OpenAccess.FieldAlias("isExcludedFromIncomeTargeting")]
        public string IsExcludedFromIncomeTargeting
        {
            get { return isExcludedFromIncomeTargeting; }
            set { this.isExcludedFromIncomeTargeting = value; }
        }

        [Telerik.OpenAccess.FieldAlias("isSocialSecurityNumber")]
        public string IsSocialSecurityNumber
        {
            get { return isSocialSecurityNumber; }
            set { this.isSocialSecurityNumber = value; }
        }

        [Telerik.OpenAccess.FieldAlias("isValidated")]
        public string IsValidated
        {
            get { return isValidated; }
            set { this.isValidated = value; }
        }

        [Telerik.OpenAccess.FieldAlias("lastEditDate")]
        public DateTime? LastEditDate
        {
            get { return lastEditDate; }
            set { this.lastEditDate = value; }
        }

        [Telerik.OpenAccess.FieldAlias("lastName")]
        public string LastName
        {
            get { return lastName; }
            set { this.lastName = value; }
        }

        [Telerik.OpenAccess.FieldAlias("lkosAddress1099Override")]
        public int? LkosAddress1099Override
        {
            get { return lkosAddress1099Override; }
            set { this.lkosAddress1099Override = value; }
        }

        [Telerik.OpenAccess.FieldAlias("lkosAddressDefault")]
        public int? LkosAddressDefault
        {
            get { return lkosAddressDefault; }
            set { this.lkosAddressDefault = value; }
        }

        [Telerik.OpenAccess.FieldAlias("lkosContactDefault")]
        public int? LkosContactDefault
        {
            get { return lkosContactDefault; }
            set { this.lkosContactDefault = value; }
        }

        [Telerik.OpenAccess.FieldAlias("middleName")]
        public string MiddleName
        {
            get { return middleName; }
            set { this.middleName = value; }
        }

        [Telerik.OpenAccess.FieldAlias("namePrefix")]
        public string NamePrefix
        {
            get { return namePrefix; }
            set { this.namePrefix = value; }
        }

        [Telerik.OpenAccess.FieldAlias("nameSuffix")]
        public string NameSuffix
        {
            get { return nameSuffix; }
            set { this.nameSuffix = value; }
        }

        [Telerik.OpenAccess.FieldAlias("pHACode")]
        public string PHACode
        {
            get { return pHACode; }
            set { this.pHACode = value; }
        }

        [Telerik.OpenAccess.FieldAlias("payeeTaxKey")]
        public string PayeeTaxKey
        {
            get { return payeeTaxKey; }
            set { this.payeeTaxKey = value; }
        }

        [Telerik.OpenAccess.FieldAlias("previousEntityID")]
        public string PreviousEntityID
        {
            get { return previousEntityID; }
            set { this.previousEntityID = value; }
        }

        [Telerik.OpenAccess.FieldAlias("programAffiliation")]
        public string ProgramAffiliation
        {
            get { return programAffiliation; }
            set { this.programAffiliation = value; }
        }

        [Telerik.OpenAccess.FieldAlias("taxID")]
        public string TaxID
        {
            get { return taxID; }
            set { this.taxID = value; }
        }


    }
}

IEliteDataService

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EliteExtender.Infrastructure.Models.Elite;

namespace EliteExtender.Infrastructure.Services
{
   public interface IEliteService
    {
        /// <summary>
        /// Returns a list of residents based off a simple search looking only at Tax ID(SSN) or FileNameAs(LastName)
        /// </summary>
        /// <param name="param"></param>
        /// <returns>Returns a List</returns>
       IList<object> SimpleResidentSearch(string param);
    }
}

EliteDataService

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EliteExtender.Infrastructure.Models.Elite;

namespace EliteExtender.Infrastructure.Services
{
    public class EliteDataService : IEliteService
    {
        private EliteDBManager dbManager;

        public EliteDataService()
        {
            dbManager = new EliteDBManager();
            dbManager.InitializeDatabase();
        }

        #region IEliteService Members

        public IList<object> SimpleResidentSearch(string param)
        {
            //var query = from userdata in scope.Extent<osEntity>()
            //            select new {
            //                ID = userdata.User.ID,
            //                LastName = userdata.User.LastName,
            //                FirstName = userdata.User.FirstName,
            //                LastLogin = userdata.LastLogin
            //            };
            return null;
        }

        #endregion
    }
}

[Zoran]

Hello Garry Clark,

 The query you are executing is actually returning an anonymous type. This is a new feature introduced in .NET 3.5 for the purposes of Linq and one of the characteristics for those kind of result sets is that they can really be used in the scope of a single method. It is not possible for one to return a result set of anonymous objects as you said yourself there is no way for one to know the type of the collection, you can only return "object" but I am sure that this is not your preferred way. 

The approach I suggest you here is to make your own type which is not persistent where you can store the selected data for the purposes of accessing it from different layers of your application.

For example, you could have a business class(note that an interface is not really appropriate int his scenario) like the following:

public class EntityBO
{
   public int ID { get; set; }
   public string LastName { get; set; }
   public string FirstName { get; set; }
   public bool IsActive { get; set; }
}

Your Linq query would in that case look like below:

IQueryable<EntityBO> query = from userData in scope.Extent<OsEntity>()
                       select new EntityBO
                       {
                         ID = userData.EntityID,
                         LastName = userdata.LastName,
                         FirstName = userdata.FirstName,
                         IsActive = userdata.OSParticipant.IsActive
                       };

Sincerely yours,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Garry]

Zoran,
Thank you for the response and I was actually afraid that was the answer. Is that basically for different result sets I will need different classes. Also reason I was looking at using Interfaces in conjuction with classes is that I am doing this inside a Prism App and in order to do DI I have to have a related Interface do I not?

Also again I am not seeing an osParticipant property in my osEntity class for the two to link on. What should I see if OA has properly linked these two classes?

Last thing can you reposnd to this portion of my last post? Even if I create a custom class to return the result in how would my class look and how would I fill the property where a one to many or many to many relation existed?
"Also to expand the question,  the osParticipant table has related tables such as an Address table that is a one to many relation. How would doing a query such as the one above handle this scenario? I am assume a property in osParticipant would be a collection that would hold the many addresses, but I do not see that being the case in any of the classes that OA generated."

Thansk again!

[Zoran]

Hi Garry Clark,

 How are the OsEntity and OsParticipant tables related in your database. Are they related through a join table or is there a foreign key constraint between them? If there is a foreign key constraint, there should really be a member of type OsParticipant in the OsEntity class, if there is not such it means that maybe some bug of the Reverse Mapping Wizard is exposed. 

Regarding the issue with the collections that you do not see exposed, this is the default behavior of the Reverse Mapping Wizard. You can generate them in the following way(example with Address <->  OsParticipant) : 

• Expand the Address class node in the advanced view of the Reverse Mapping Wizard
• Select the OsParticipant sub-node
• Check the "Create one-to-many list" checkbox on the right

I would just like to mention that this workflow is completely automated in the new Visual Designer for OpenAccess that is part of the latest release. There the collections are exposed by default and manipulating associations is much more flexible. If you decide to use it though, you must not use it in the same project where your current persistent classes are. You can use either the new designer or the old Reverse Mapping Wizard inside a project - never both.

Greetings,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Garry]

Zoan,
I created a small DB diagram to illustarte a small subset of the tables/relationships I am working with. I would also like to say that I did not create nor approve of this horrid monster of a data model that I have been handed so please don't think less of me. :)

A sample query I might try and run against this data model would be as such. Also I am using aliases here, which it appeared to me that SQL Compact Edition does not support so I will have to rework some of my logic.

SELECT     dbo.ptWMember.PK, dbo.osParticipant.fkosCompanyModule, dbo.osParticipant.PK AS fkosParticipant, dbo.osParticipant.fkosEntity,
                      dbo.ptWMember.FirstName AS 'First  Name', dbo.ptWMember.MiddleName AS 'Middle Name', dbo.ptWMember.LastName AS 'Last Name', dbo.ptWMember.Gender,
                      dbo.ptWMember.SocialSecurityNumber AS 'Social Security  Number', dbo.ptWMember.DateOfBirth AS 'Date Of Birth',
                      dbo.osCompanyModule.Description AS 'Company Module', dbo.MLSosEntityModuleRole.Description AS 'Participant  Role', CASE WHEN osParticipant.IsActive IS NULL
                       THEN 'N/A' WHEN osParticipant.IsActive = 'No' THEN 'Inactive' ELSE 'Active' END AS 'Status', MLSosCodeCS.Description AS CommunityService,
                      MLSosCodeEthnicity.Description AS Ethnicity, MLSosCodeRelationship.Description AS Relationship
FROM         dbo.ptWMember WITH (nolock) INNER JOIN
                      dbo.ptParticipantWCertLink WITH (nolock) ON dbodbo.ptWMember.fkptWCertification = dbo.ptParticipantWCertLink.fkptWorkingCertification INNER JOIN
                      dbo.osParticipant WITH (nolock) ON dbodbo.ptParticipantWCertLink.fkosParticipant = dbo.osParticipant.PK INNER JOIN
                      dbo.osCompanyModule WITH (nolock) ON dbodbo.osParticipant.fkosCompanyModule = dbo.osCompanyModule.PK INNER JOIN
                      dbo.MLSosEntityModuleRole WITH (nolock) ON dbodbo.osParticipant.fkMLSosEntityModuleRole = dbo.MLSosEntityModuleRole.PK LEFT OUTER JOIN
                      dbo.MLSosCode AS MLSosCodeCS ON dbo.ptWMember.fkMLSosCodeCommunityService = MLSosCodeCS.PK LEFT OUTER JOIN
                      dbo.MLSosCode AS MLSosCodeEthnicity ON dbo.ptWMember.fkMLSosCodeEthnicity = MLSosCodeEthnicity.PK LEFT OUTER JOIN
                      dbo.MLSosCode AS MLSosCodeRelationship ON dbo.ptWMember.fkMLSosCodeRelationship = MLSosCodeRelationship.PK
ORDER BY 'First  Name', 'Last Name'

Also if you refer back up to the 2 classes that OA generated for me off of this sample data model you can see or rather I do not see the related tables/members of the other tables. Also I would love to use the new Visual Designer, but I am doing this project in VS 2010 RC, which I am told does not support the Visual Designer yet.

One caveat to this, that might be causing OA an issue, is that I am actually connecting OA to the local cache DB that I am generating from the bigger data model. Could this be an issue?

Thanks!

[Zoran]

Hi Garry Clark,

 From the model you have sent me it is clearly visible that a foreign key relationship between osParticipant and osEntity exists. That means that there should really be a member of type OsEntity on your OsParticipant class. If there is not such, it means that we are confronting a bug here. The best way for us to investigate is for you to send us the reversemapping.config file so we can see the behavior of the Reverse Mapping Wizard that you have. If you do have this problem, I suggest you starting a support thread where you are able to attach all kinds of files and send us the configuration file there. 

Regarding the question for the osParticipants and osAddresses, I guess we had misunderstanding there, as these table are not directly related(nor via join table) so there is no way for you to have members in your object model that relate these entities directly through a single property. If the model is generally OK, you should be able to navigate from the osParticipant to the corresponding osAddress through the osEntity instance in between. So you could have something like:

from ... in ...
select new {OsParticipant.OsEntity.OsAddress.City};

The above of course will only be possible if the OsEntity member is there generated at the OsParticipant class.

Otherwise, the query you just wrote in SQL should be easily achievable using Linq and OpenAccess.

Kind regards,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.