SaS

SaS

  • NA
  • 3
  • 501

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:
  1. groupID, UserID, Code, totalFirst, totalAfter  
and I have a model/table called TOperations that contains fields:
  1. 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:
  1. user1 group1  AX    100 100  
  2. user1 group1  AS    20 10 
So, the output I want in my controller is something Code and totalFirst fields of the view only:
  1. user1 group1  [AX ,100] , [AS, 20]
my TOperations table class model declaration is:
  1. public class TOperations  
  2.     {  
  3.         [Key, Column(Order = 0)]  
  4.         public int userId { getset; }  
  5.   
  6.         [Key, Column(Order = 1)]  
  7.         public int groupId { getset; }  
  8.   
  9.         public DateTime? dateRegOn{ getset; }  
  10.  
  11.         #region Navigation Properties  
  12.   
  13.         public virtual Group Group { getset; }  
  14.         public virtual User User { getset; }  
  15.         
  16.         #endregion  
  17.     }
the VKile is defined as:
  1. [Table("Payments")]  
  2. public class VKiles  
  3. {
  4.     [Key, Column(Order = 0)]  
  5.     public int groupId { getset; }  
  6.     public int Code { getset; }  
  7.     [Key, Column(Order = 1)]  
  8.     public int userId { getset; }  
  9.     [DatabaseGenerated(DatabaseGeneratedOption.Computed)]  
  10.     public decimal totalFirst{ getset; }  
  11.     [DatabaseGenerated(DatabaseGeneratedOption.Computed)]  
  12.     public decimal totalAfter { getset; }  
  13.     #region Navigation Properties  
  14.     public virtual Group District { getset; }  
  15.     public virtual User { getset; } 
  16.     #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:
  1. SqlException: Invalid column name 'TOperations_groupId'.  
  2. Invalid column name 'TOperations_userId'.  
  3. Invalid column name 'TOperations_groupId'.   
  4. 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:
  1. public virtual ICollection VKiles  { getset; } 
and in my controller I added:
  1.   private object FlattenOperation(TOperations ot)  
  2.     {  
  3.   
  4.             var kiles = ot.VKiles.Select(  
  5.             cc => new { cc.Code, cc.totalFirst });  
  6.   

I was hoping for each row, I would get a tuple that contains Code and totalFirst values.