TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
SaS
NA
3
509
Using a DB View in a Controller that already uses a table
Jul 27 2019 9:14 AM
Hello,
I am stuck at this task that I am told is possible but am lost. It is quite long post and the app is just new to me (and is quite big and undocumented sadly). Basically, I have a view that is too complex with formulas and calculations. I want to use it as it is without knowing any tables it uses underneath and the logic it uses. It is named VKile and it produces the following columns:
groupID, UserID, Code, totalFirst, totalAfter
and I have a model/table called TOperations that contains fields:
UserId, groupID, dateRegOn
the table and the view share UserId and groupID fields. The relationship between the two, in terms of data is that each UserId, groupID can have multiple records in the view such as the following row in TOperations user1, group1, a row in the view can be:
user1 group1 AX 100 100
user1 group1
AS
20 10
So, the output I want in my controller is something Code and totalFirst fields of the view only:
user1 group1 [AX ,100] , [
AS
, 20]
my TOperations table class model declaration is:
public
class
TOperations
{
[Key, Column(Order = 0)]
public
int
userId {
get
;
set
; }
[Key, Column(Order = 1)]
public
int
groupId {
get
;
set
; }
public
DateTime? dateRegOn{
get
;
set
; }
#region Navigation Properties
public
virtual
Group Group {
get
;
set
; }
public
virtual
User User {
get
;
set
; }
#endregion
}
the VKile is defined as:
[Table(
"Payments"
)]
public
class
VKiles
{
[Key, Column(Order = 0)]
public
int
groupId {
get
;
set
; }
public
int
Code {
get
;
set
; }
[Key, Column(Order = 1)]
public
int
userId {
get
;
set
; }
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public
decimal
totalFirst{
get
;
set
; }
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public
decimal
totalAfter {
get
;
set
; }
#region Navigation Properties
public
virtual
Group District {
get
;
set
; }
public
virtual
User {
get
;
set
; }
#endregion
}
What I don't really get from this View definition is the Table("Payments") part as there is no Payments table defined in the database at all, yet VKile is used in other controllers and working well.
The controller that is working right now simply pulls data from TOperations table but now I want to add the records from VKile but it raises the following error:
SqlException: Invalid column name
'TOperations_groupId'
.
Invalid column name
'TOperations_userId'
.
Invalid column name
'TOperations_groupId'
.
nvalid column name
'TOperations_userId'
.
I am not sure why each field is printed twice but that's what it is. To get the above error, I modified my TOperations model navigation to include:
public
virtual
ICollection
VKiles {
get
;
set
; }
and in my controller I added:
private
object
FlattenOperation(TOperations ot)
{
var kiles = ot.VKiles.Select(
cc =>
new
{ cc.Code, cc.totalFirst });
}
I was hoping for each row, I would get a tuple that contains Code and totalFirst values.
Reply
Answers (
0
)
How to Fix Internal server Error in API
how to save yes or no as 1 or 0 format using asp c# but user