locked
Not returning all columns from SQL that model is expecting RRS feed

  • Question

  • User269881539 posted

    If I have a model that has say 10 properties in it, which I may want to fill all of when I am on a detail page, can I use the same model in a List for a navigational page, where the underlying SQL for that page might not return all columns?

    For example I may have a Product Model :

    public int ProductID { get; set; }
    public string FriendlyURL { get; set; }
    public string ProductCode { get; set; }
    public string Barcode { get; set; }
    public string Description { get; set; }
    public string LongDescription { get; set; }
    public string ImageFile { get; set; }
    public decimal RRP { get; set; }
    public decimal SellPrice { get; set; }

    The underlying SQL for a product detail page will return all of these columns and so the binding can match all fields.

    However for efficiency in SQL returning only the data that is needed, my category listing page might omit a couple of these and so not all properties can be bound.

    I can see that it works as my pages are rendering but I am also seeing a lot of exceptions of this nature :

    System.IndexOutOfRangeException: LongDescription
    at System.Data.ProviderBase.FieldNameLookup.GetOrdinal
    at System.Data.SqlClient.SqlDataReader.GetOrdinal
    at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.TryGetColumnOrdinalFromReader
    Can I make my properties optional, or do I want a cut down model for my listings style pages?
    Thanks

    Friday, August 9, 2019 7:32 PM

All replies

  • User475983607 posted

    An ordinal is an integer index into a column array.  You are using indexes to map column names which is not a good idea. 

    Secondly, your types should match the result set.  If you want a generic table structure than use the DataTable.  Once you have a DataTable,  you can use reflection or LINQ to convert the DataTable to a type.  Just Google a solution there's many.

    Friday, August 9, 2019 7:52 PM
  • User269881539 posted

    mgebhard

    An ordinal is an integer index into a column array.  You are using indexes to map column names which is not a good idea.

    Sorry I am not sure what you mean here? My code to get the data from the db is using a Stored Procedure - I assumed that the mapping was based on the column names?  :

    // returns a single product
    public Product GetProduct(int id)
    {
    // setup params
    var pId = new SqlParameter { ParameterName = "ProductID", Value = id };
    // setup stored procedure
    string query = "EXEC prcProductPage @ProductID";
    // perform query
    var prod = context.Database.SqlQuery<Product>(query, pId).SingleOrDefault();
    return prod;
    }

     

    mgebhard

    Secondly, your types should match the result set.  If you want a generic table structure than use the DataTable.  Once you have a DataTable,  you can use reflection or LINQ to convert the DataTable to a type.  Just Google a solution there's many.

    So I should always have all columns in the output of the SQL that my model is expecting ideally? Else I just return to a datatable and then convert? I think I would prefer to create a "ProductListing" model perhaps with the properties in that will support my listing views and then stick to that.

    Thanks

    Friday, August 9, 2019 7:59 PM
  • User475983607 posted

    Sorry I am not sure what you mean here? My code to get the data from the db is using a Stored Procedure - I assumed that the mapping was based on the column names?  :

    I did not know you where using entity framework.  The error means your procedure result set does not match the property names.

    Friday, August 9, 2019 8:46 PM
  • User1520731567 posted

    Hi chilluk,

    I test you code in my project,but it work fine that whether the model's attributes are more or less than the SQL query.

    The only thing I don't know now is how your stored procedure is.

    If you would like to post more details,I can reproduce your issue.

    But I think this problem is easy to solve, just delete or add it according to the error message.

    Best Regards.

    Yuki Tao

    Monday, August 12, 2019 10:06 AM
  • User269881539 posted

    mgebhard

    I did not know you where using entity framework.  The error means your procedure result set does not match the property names.

    Thanks for this - I am going through my app making sure my Stored Procedures contain all columns that match my model property names one to one.

    A slight issue I am seeing is where I am populating a model that inherits from another - so for example I have a basic Product model class with the bare bones properties of a product, then I have a class that represents a fuller representation of a product that inherits the basic model class and extends it with further properties. Thus I use the basic model for product listing pages for example where I show only a sub set of product info, and the fuller model for product detail pages where I show much more info.

    I am sure I am still seeing a similar error in this instance - should the mapping be seeing the inherited properties? I am using a product called Retrace to monitor the CLR errors, and can see it's moaning about properties that are both in my Stored Proc and in the inherited class as far as I can tell.

    Wednesday, August 14, 2019 4:14 AM
  • User1520731567 posted

    Hi chilluk,

    so for example I have a basic Product model class with the bare bones properties of a product, then I have a class that represents a fuller representation of a product that inherits the basic model class and extends it with further properties. Thus I use the basic model for product listing pages for example where I show only a sub set of product info, and the fuller model for product detail pages where I show much more info.

    According to your descriptions,I think your could refer to one to one entity or one to many entity,rather then inherit.

    For example:(one to one)

    public class Student
    {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
    
        public virtual StudentAddress Address { get; set; }
    }
         
    public class StudentAddress 
    {
        [ForeignKey("Student")]
        public int StudentAddressId { get; set; }
            
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public string City { get; set; }
        public int Zipcode { get; set; }
        public string State { get; set; }
        public string Country { get; set; }
    
        public virtual Student Student { get; set; }//navigation property
    }

    Generally,we handle relationships between multiple entities by including a reference navigation property in EF in MVC.

    Best Regards.

    Yuki Tao

    Wednesday, August 14, 2019 8:16 AM
  • User269881539 posted

    Thanks for that - it's not really relationships though - it's one class extending another - all for the same entity - a Product.

    The basic class is to be used to hold date when fetching products for pages where I only want to show some basic details (listing type pages) and the extended full class where I want to show all product details.

    This is to solve a specific issue where I am seeing .NET CLR exceptions when the results of my Stored procedure don't have all columns in my Product model class - so I created a cut down basic version.

    I can completely split the model classes into ProductBasic and ProductFull for example, but it seemed more logical to let one inherit the other?

    Wednesday, August 14, 2019 7:01 PM
  • User1520731567 posted

    Hi chilluk,

     

    Thanks for that - it's not really relationships though - it's one class extending another - all for the same entity - a Product.

    The basic class is to be used to hold date when fetching products for pages where I only want to show some basic details (listing type pages) and the extended full class where I want to show all product details.

    This is to solve a specific issue where I am seeing .NET CLR exceptions when the results of my Stored procedure don't have all columns in my Product model class - so I created a cut down basic version.

    I can completely split the model classes into ProductBasic and ProductFull for example, but it seemed more logical to let one inherit the other?

    I perfer to create a ViewModel which can contains the fields what you want,

    or

    divide a large class into multiple subclasses to form a one-to-one relationship,for example:

    Product model()

    public class Product
    {
        public int ProductID{ get; set; }
        ...
    ....listing type pages... public virtual ProductDetail productDetail{ get; set; }//you could call productDetail in Product model for its extended types } public class ProductDetail { [ForeignKey("ProductID")] public int ProductDetailId{ get; set; } .... public string extendDetails{ get; set; } ....extended type .... public virtual Product product { get; set; }//you could call product in ProductDetail model for its listing type pages }

    Best Regards.

    Yuki Tao

    Thursday, August 15, 2019 5:56 AM