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 :
<div>System.IndexOutOfRangeException: LongDescription</div> <div class="line source">at System.Data.ProviderBase.FieldNameLookup.GetOrdinal</div> <div class="line">at System.Data.SqlClient.SqlDataReader.GetOrdinal</div> <div class="line">at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.TryGetColumnOrdinalFromReader</div>
<div class="line"></div> <div class="line">Can I make my properties optional, or do I want a cut down model for my listings style pages?</div> <div class="line"></div> <div class="line">Thanks</div>
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.
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.
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.
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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
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.
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.
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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
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?
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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
405 Points
519 Posts
Not returning all columns from SQL that model is expecting
Aug 09, 2019 07:32 PM|chilluk|LINK
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 :
<div>System.IndexOutOfRangeException: LongDescription</div> <div class="line source">at System.Data.ProviderBase.FieldNameLookup.GetOrdinal</div> <div class="line">at System.Data.SqlClient.SqlDataReader.GetOrdinal</div> <div class="line">at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.TryGetColumnOrdinalFromReader</div> <div class="line"></div> <div class="line">Can I make my properties optional, or do I want a cut down model for my listings style pages?</div> <div class="line"></div> <div class="line">Thanks</div>All-Star
53041 Points
23612 Posts
Re: Not returning all columns from SQL that model is expecting
Aug 09, 2019 07:52 PM|mgebhard|LINK
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.
Member
405 Points
519 Posts
Re: Not returning all columns from SQL that model is expecting
Aug 09, 2019 07:59 PM|chilluk|LINK
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; }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
All-Star
53041 Points
23612 Posts
Re: Not returning all columns from SQL that model is expecting
Aug 09, 2019 08:46 PM|mgebhard|LINK
I did not know you where using entity framework. The error means your procedure result set does not match the property names.
Contributor
3710 Points
1431 Posts
Re: Not returning all columns from SQL that model is expecting
Aug 12, 2019 10:06 AM|Yuki Tao|LINK
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
405 Points
519 Posts
Re: Not returning all columns from SQL that model is expecting
Aug 14, 2019 04:14 AM|chilluk|LINK
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.
Contributor
3710 Points
1431 Posts
Re: Not returning all columns from SQL that model is expecting
Aug 14, 2019 08:16 AM|Yuki Tao|LINK
Hi chilluk,
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
405 Points
519 Posts
Re: Not returning all columns from SQL that model is expecting
Aug 14, 2019 07:01 PM|chilluk|LINK
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?
Contributor
3710 Points
1431 Posts
Re: Not returning all columns from SQL that model is expecting
Aug 15, 2019 05:56 AM|Yuki Tao|LINK
Hi chilluk,
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.