User defined persistent type as stored procedure return type

[Robert]

Hello,

I'm having problem with following: I have a stored procedure returning data from multiple tables + computed distance:

SELECT TOP(@maxCnt) ctr.[Name] as [LongName], ctr.ISO2Char as ShortName, o.ObjectID as ObjectID,
        p.PropValueID as PropValueID, p.[Value] as [Value], p.UnitValue as UnitValue, o.[Name] as [Name],
        ((o.Latitude - @lat) * (o.Latitude - @lat) + (o.Longitude - @lng) * (o.Longitude - @lng)) as Dist
    FROM PropsValues p
    JOIN Object o ON p.OwnerID = o.ObjectID
    JOIN Category c ON o.CategoryID = c.CategoryID
    JOIN geoCountries ctr ON o.Country = ctr.CountryID
    WHERE c.CategoryID = 2 AND p.PropID = 7
    ORDER BY p.[Value], Dist

and have defined user-defined persistent class:

public partial class GetChiepestDiesel
    {
        public GetChiepestDiesel()
        {

        }

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


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

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


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

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

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

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

        [Telerik.OpenAccess.FieldAlias("dist")]
        public float? Dist
        {
            get { return dist; }
            set { this.dist = value; }
        }
    }

    [Telerik.OpenAccess.Persistent(IdentityField = "objectID")]
    public partial class GetChiepestDiesel
    {
        private string longName;
        private string shortName;
        private int objectID;
        private int propValueID;
        private string unitValue;
        private string value;
        private string name;
        private float? dist;

    }

which is supposed to be a return type for the stored procedure:

   public static class StoredProcedure
   {
        public static IEnumerable<DataClasses.GetChiepestDiesel> GetCheapestDieselFromLatLng(IObjectScope scope,int? maxCnt,float? lat,float? lng)
        {
            Query<DataClasses.GetChiepestDiesel> query = scope.GetSqlQuery<DataClasses.GetChiepestDiesel>("GetCheapestDieselFromLatLng ?,?,?","INTEGER maxCnt,FLOAT lat,FLOAT lng");
            QueryResultEnumerable<DataClasses.GetChiepestDiesel> res = query.ExecuteEnumerable(new object[] {maxCnt,lat,lng});

            return res;
        }

   }

Currently I'm getting this error message:

Persistent class 'DataClasses.GetChiepestDiesel' was specified as candidate, but the ResultSet does not contain any/all of the pk columns.

Is there a way how to solve this?

Thanks,
Robert

[PetarP]

Hi Robert,

The problem here is that your column names are not named as the class fields. The class expects to receive different columns than the one that are being provided. To successfully run this you will need to rename your columns to match the class fields.

All the best,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Kris]

Hi Petar, I am also having trouble with this error. Here's what I have.

I have a persistent class called PhotoLatLong.

 [Telerik.OpenAccess.Persistent(IdentityField = "photoID")]
    public class PhotoLatLong //: Photo
    {
        public Guid photoID; // pk
        private string cameraModel;
        private string description;
        private byte?[] geoData;
        private DateTime lastUpdate;
        private string lastUpdateUser;
        private Guid parentID;
        private string parentTable;
        private DateTime? photoDateTime;
        private int? photoNumber;
        private string serialNumber;
        private string vvlDirection;
        private string vvlType;
        private string witness;
        private double? longitude;
        private double? latitude;

        //The 'no-args' constructor required by OpenAccess.
        public PhotoLatLong()
        {
        }

        [Telerik.OpenAccess.FieldAlias("photoID")]
        public Guid PhotoID
        {
            get { return photoID; }
            set { this.photoID = value; }
        }

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

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

        [Telerik.OpenAccess.FieldAlias("geoData")]
        public byte?[] GeoData
        {
            get { return geoData; }
            set { this.geoData = value; }
        }

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

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

        [Telerik.OpenAccess.FieldAlias("parentID")]
        public Guid ParentID
        {
            get { return parentID; }
            set { this.parentID = value; }
        }

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

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

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

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

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

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

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

        [Telerik.OpenAccess.FieldAlias("longitude")]
        public double? Longitude
        {
            get { return longitude; }
            set { this.longitude = value; }
        }

        [Telerik.OpenAccess.FieldAlias("latitude")]
        public double? Latitude
        {
            get { return latitude; }
            set { this.latitude = value; }
        }
    }

I use this stored procedure in the StoredProcedures.cs class

        public static IEnumerable<SARTSData.PhotoLatLong> upGetPhotos(IObjectScope scope, Guid? parentID, string parentTable)
        {
            Query<SARTSData.PhotoLatLong> query = scope.GetSqlQuery<SARTSData.PhotoLatLong>("[upGetPhotos] ?,?", "GUID parentID,VARCHAR parentTable");
            QueryResultEnumerable<SARTSData.PhotoLatLong> res = query.ExecuteEnumerable(new object[] { parentID, parentTable });

            return res;
        }

And here is the SQL for the sproc

ALTER PROCEDURE [dbo].[upGetPhotos] --'0b4f4dbb-cd0d-4ffb-a38d-2cc6b2d328c7','facilities'
    -- Add the parameters for the stored procedure here
    @parentID uniqueidentifier
    ,@parentTable varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT [photoID]
      ,[cameraModel]
      ,[description]
      ,[geoData]
      ,[lastUpdate]
      ,[lastUpdateUser]
      ,[parentID]
      ,[parentTable]
      ,[photoDateTime]
      ,[photoNumber]
      ,[serialNumber]
      ,[vvlDirection]
      ,[vvlType]
      ,[witness]
      ,[geoData].STX as longitude
      ,[geoData].STY as latitude
      --,[photoID] as UnID
  FROM [dbo].[photos] where
  parentID = @parentID and parentTable = @parentTable
  and vvlType <> 'Document'
  order by photoNumber, ISNULL(photoDateTime,lastUpdate)

END

As far as I can tell, the columns being returned from the sproc matchesthe class field definitions. So I am stumped as to why this error isstill occurring.

Any ideas? thanks

[PetarP]

Hi Kris,

Can you please confirm that your column names exactly match your field names? We tested this here locally and it worked. Please note that it also worked for Robert. If you are sure that your column names match your field names, can you please send us a small project that represents this issue because it seems that we are not able to reproduce it as everything works as expected on our side. We are looking forward to your reply.

Regards,
Petar
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.

[Kris]

Thanks for the reply, I have submitted a support ticket with a sample project demonstrating the issue. Ticket #284362

[devoas]

Hello,

We are using OA Q3 Build 1218 and having same problem when trying to get SP output in a Domain modal. 

Persistent class was specified as candidate, but the ResultSet does not contain any/all of the pk columns.

If we use Object as return type instead of Domain Modal, it work properly....

Please confirm how to fix the issue. We have double check that Fields name returned by the SP and Persistence class are exactly same....

Thanks
devoas.

[devoas]

Hello,

I found that if the primary key field name in both Stored Procedure and persistence view is renamed in a way that it should start with Underscore (eg:   _pkfield ), then it is working without any problem..... seems till Q3-2009 this issue is not be resolved. Please confirm if this is been resolved in Q1-2010.

Thanks,
devoas.

[PetarP]

Hello devoas,

We do not analyze the field name during run time but just compare the procedure resultset column names with the class column names. An underscore should not matter at all. Can you please open a support ticket and send us a sample project where we can observe the problem as we were not able to reproduce it. We are looking forward to your reply.

Greetings,
Petar
the Telerik team