none
Entity Framework Code First Product Total Sum RRS feed

  • Question

  • I have this problem

    I have two entities Products and Details

    public class Product 
    { 
    
        [Key] 
        public int ProdId { get; set; }
    
    
        public string Name { get; set; }
       
    
        public decimal UnitPrice { get; set; } 
    
    } 
    
    
    public class Detail 
    { 
    
         public int Id { get; set; } 
         
        
         public int ProdId { get; set; } 
     
    
         public int Quantity { get; set; } 
        
    
        [ForeignKey("ProdId")] 
        public Product Product { get; set; } 
    
    
        public decimal Total {
    
        get { return Quantity*Product.UnitPrice }
    
        Set {} 
    }

    CORRECT CODE


    I want the Total field there result of quantity *unitprince but i don't know how to do this in mvc 3....

    I get error the value Unitprice can't be null in httpPOST method...







    Wednesday, February 15, 2012 3:42 PM

Answers

  • Guys I solved my problem. It can set a calculated field in the database in read-only? The answer is no :) I'm writing this for people like me who has ran into this problem. If you have a particular scenario where you have to calculate costs based on a salary of a particular employee, you must create an intermediate table where you have to do queries based on the date of a change in salary. Without going into details, we always talk about query. For example you can filter the values ​​by date, so you get a total cost based on the date with a different salaraio per employee. In any case, we must think about how you design your own database. I have untapped in this mistake because I thought such a thing were possible. The point of this discussion is that a thing can not be done in the database, otherwise try to find alternatives, like I did.

    I want to Thank everyone for their availability, especially Julie who also supported me on twitter :)

    And finally, sorry for my English, I promise to improve: D

    Tuesday, February 28, 2012 11:39 PM

All replies

  • Hi Mirko Pastorelli,

    Welcome to MSDN Forum.

    Based on the issue, could you please post the code which throws error here? This is, so I can help you more effectively.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 17, 2012 2:36 AM
    Moderator
  • As Allen said it's hard to imagine what's going wrong withoud of code, but try this

        public class Product
        {
            [Key]
            public int ProdId { get; set; }
            public string Name { get; set; }
            public decimal? UnitPrice { get; set; }
        }
    
        public class Detail
        {
            public int Id { get; set; }
            public int ProdId { get; set; }
            public int Quantity { get; set; }
            [ForeignKey("ProdId")]
            public Product Product { get; set; }
            public decimal Total { get { return Quantity * (Product.UnitPrice ?? default(decimal)); } }
        }

    and if you're using nullable decimal anyway you can check the UnitPrice has a value before call.

    • Edited by eugene_s Friday, February 17, 2012 11:30 AM
    Friday, February 17, 2012 11:22 AM
  • Hi Mirko Pastorelli,

    Have you solved the issue?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 22, 2012 1:22 AM
    Moderator
  • Thanks, but i have this problem. With this code i can save the total value in database

    public class Product 
    { 
    [Key]
     public int ProdId { get; set; } 
    
    public string Name { get; set; } 
    
    public decimal UnitPrice { get; set; } 
    } 
    
    public class Detail
     { 
    
    public int Id { get; set; }
    
     public int ProdId { get; set; }
    
     public int Quantity { get; set; } 
    
    [ForeignKey("ProdId")]
     public Product Product { get; set; }
    
     public decimal Total { get { return Quantity*Product.UnitPrice } Set {} }
    But i need I need that if for example I'm going to change the UnitPrice total value in the database does not change! Should only be readonly ... is it possible?


    Thursday, February 23, 2012 7:21 AM
  • Any help Guys? :D
    Thursday, February 23, 2012 6:54 PM
  • Hi Mirko Pastorelli,

    I have tested to realize readonly propery by code-first, but it couldn't work. And I have searched on the internet, but didn't find any documentation about it. But there's a workaround, if you only want to the 'Total' property as a value of computed through the other columns, you can use Computed Columns. Please refer to the link, I think it can help you.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 24, 2012 2:22 AM
    Moderator
  • Entity Framework Model supports the fields readonly or not? Thanks for the link I've provided, basically is a column that I have to create the database through SQL commands right?
    Saturday, February 25, 2012 7:52 AM
  • For now I'm following this guide and it seems to work with a one table on SQL server.

    http://www.mssqltips.com/sqlservertip/1682/using-computed-columns-in-sql-server-with-persisted-values/

    The problems are three:

    1) When I change a field automatically changes the calculated field, setting the persisted = "no"data is changed anyway. how can I do?

    2) The computed column can be calculated multiplying value which is in a different table (as in this case)?

    3) How do I set the value of the computed column of entity framework (model or code first does not matter)?

    thanks



    Saturday, February 25, 2012 9:21 AM
  • This is odd because by code first convention, a property that has only a getter or a setter (but not both) do not get automatically mapped. If they are not mapped, then EF will never try to use that property in a query or in an update to the database.

    I wouldn't recommend the computed columns ...that's dependent on the database.

    Can you try to * explicitly* unmap the field with the [NotMapped] data annotation and see how that goes?

    That's a step in the right direction. This is the second time I've seen the convention fail. :(

    julie


    Julie Lerman, Author of Programming Entity Framework, MVP

    Saturday, February 25, 2012 2:10 PM
  • Thanks for your response Julie.I tried to put the property Total [NotMapped]

    [NotMapped]
    public decimal Total {
    
    get {return Product.UnitPrice * Quantity;}
    
    set ()
    
    }


    and the value is not saved in the database. Maybe Entity Framework Code First does not support this feature. If the property Total has set () the field is saved correctly, but can be changed. Maybe it's not a matter of Entity Framework, but SQL Server. I tried looking on the internet, but also by inserting a calculated field value is never readonly. At this point I do not know what to do.


    Saturday, February 25, 2012 2:35 PM
  • you *do* want it saved in the database? That's a different story.

    So the reason for the first problem is becasue CF convention was doing it's job...it won't map a property that doesn't have both setter & getter. So when it builds the database it doesn't create the relevant column and when it queries/saves it doesn't involve that property.

    My guess is that you used code first to build the database with the unmapped property, then tried to get the property to be recognized (adding the setter) without modifying the database. So now EF is trying to query/update Total but can't find a total field in teh database.

    So if you want the database to compute the TOTAL for you, then you *do* want to use a computed column. You can let code first know that the database will be responsible for the column and therefore EF should not persist it using the 

    [DatabaseGenerated(DatabaseGenerationOption.Computed)]

    annotation on the total property (and putting the calculation details into the databse). EF will retrieve the value from the database but will not try to persist that property value back to teh database.

    But that means that you can't see the total on the fly in your app without savingchange to the database.

    I'm pretty sure you can have the calculation logic in the getter to have the class give you the total on-the-fly if you are changing the relevant properties in your app without having a negative impact on the computed column feature.

    Sorry for the confusion.



    Julie Lerman, Author of Programming Entity Framework, MVP


    Saturday, February 25, 2012 2:52 PM
  • My first post was not in the property Total setter because I had no idea that you could put the setter. :) In my last example I entered the setter because then I solved the problem. My scenario is now this:

    I have two tables: Products and Details. After creating a new product x, I go to create a new order and the Total field saves the multiplication of UnitPrice * Product.Quantity

    ___________________________________

    I AM ABLE TO REPLY TO HERE BY ENTERING THE SETTER IN PROPERTY 'TOTAL

    public class Product 
    { 
    
        [Key] 
        public int ProdId { get; set; }
    
    
        public string Name { get; set; }
       
    
        public decimal UnitPrice { get; set; } 
    
    } 
    
    
    public class Detail 
    { 
    
         public int Id { get; set; } 
         
        
         public int ProdId { get; set; } 
     
    
         public int Quantity { get; set; } 
        
    
        [ForeignKey("ProdId")] 
        public Product Product { get; set; } 
    
    
        public decimal Total {
    
        get { return Quantity*Product.UnitPrice }
    
        Set {} 
    }

    Unfortunately when I change quantity or UnitPrice, the total value changes in the database based on values ​​modified


    Now what I want to do is this:

    If I edit  Quantity or UnitPrice in my application, Total value should not be changed, but must always remain the first

    To give you an example, if an employee's salary increases today (25/02/2012) CostEmploye the field can not change because it used to cost less. This is what I need to do.

    The Total field must be saved and can not be changed, even if I change the UnitPrice and Quantity.

    I hope I was clear this time: D



    Saturday, February 25, 2012 3:10 PM
  • Ok guys I read an article by @Julie and I managed to display the calculated column in my application.

    http://msdn.microsoft.com/en-us/data/gg193958

    This is property total

    [DatabaseGenerated(DatabaseGenerationOption.Computed)]
    public decimal Total { get; set; }

    Now the problem is:

    The Total column must be readonly. For example, when I change the Price field, the Total column should not change.

    Saturday, February 25, 2012 4:48 PM
  • As you no longer have a calculation in the getter, I don't see how the total will change just by the price changing.

    DO you have the total field bound to a UI control? Is that UI control getting updated and then pushing that new value back to the Total?

    What happens if you make the setter private? (this area always confuses me and I can't remember from the top of my head the effect). This should let the database set the value when you do a query, but not anything else.


    Julie Lerman, Author of Programming Entity Framework, MVP

    Saturday, February 25, 2012 5:09 PM
  • I did some testing, if required private set is not inserting anything happens. It 'the same as before.

    [DatabaseGenerated(DatabaseGenerationOption.Computed)]
    public decimal Total { 
    
    get {
    
    return Quantity*Product.UniPrice
    }
    
    
     private set {} }


    I removed the calculation method get in because I thought no longer be put XD now I have returned and the result does not change. Data is entered but is not readonly.

    DO you have the total field bound toa UI control?

    Should not be calculated automatically when i creating a new detail!


    Some time ago I tried with HiddenFor field, but it did not work.



    Saturday, February 25, 2012 6:27 PM
  • you say you removed the calculaton method get but I still see it.

    I still don't understand why you can't just you do it like this?

    [DatabaseGenerated(DatabaseGenerationOption.Computed)]
    public decimal Total(get;set;)

    The total will get mapped to the database. It will retrieve the computed value from teh database in queries. It will never try to update the database.



    Julie Lerman, Author of Programming Entity Framework, MVP

    Saturday, February 25, 2012 6:45 PM
  • I used your example for my test! And to me it seemed the best solution. But I need the Total field is readonly. Let me explain my scenario, I hope to be clear :)

    I have two tables. Employees and Details

        public class Employe
        {
            [Key]
            public int EmployeId { get; set; }
    
            public string Name { get; set; }
    
            public decimal EmployeCost {get; set;}
    
         }
        public class Detail
        {
            public int DetailID { get; set; }
    
            public int EmployeID { get; set; }
      
            public decimal Time {get; set;}
            
               [DatabaseGenerated(DatabaseGenerationOption.Computed)]
            public decimal TotalCost {get; set;}
    
            [ForeignKey("EmployeId")]
            public virtual Employe Employe { get; set; }
    }

    The field TotalCost multiplies the field CostEmploye * Time. I need that the field TotalCost is readonly because maybe in two months the employee can increase his salary, and if I change the value CostEmploye TotalCost the field should not change! Otherwise, the annual report would be wrong!

    Your example is fine, the only thing as I said before is the issue of edit field (CostEmployee) :P

    PS

    Now I am creating a simple test project






    Saturday, February 25, 2012 7:03 PM
  • even if your application changes the value of TotalCost, EF will never send the value to the database. However, app developers might *think* they are changing the value even though it never gets saved. So why not make the setter private again and see if that works. 

    I'm assuming that in this scenario, code first will still map the property becasue of the annotation and EF will still be able to populate that property when it queries from the database. (This is teh thing that I can't remember without testing and I don't have time to test right now).

    Let me know how that goes.


    Julie Lerman, Author of Programming Entity Framework, MVP

    Saturday, February 25, 2012 7:22 PM
  • Ok Julie, now i test and tomorrow i reply with the result ;)
    Saturday, February 25, 2012 9:50 PM
  • I put the private set but situation does not change. For now I'm testing the calculation with two values ​​in the same table because unfortunately SQL Server does not allow the calculation of two values ​​in different tables when not using views. I'll worry about this later, now is not important.

     If it works on two fields in the same table, it will work on two different tables.

    Sunday, February 26, 2012 10:16 AM
  • Hi Mirko,

    Yes, as you mentioned, if you want to caculate through tables, you have to create a view.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Monday, February 27, 2012 8:00 AM
    Moderator
  • Guys I solved my problem. It can set a calculated field in the database in read-only? The answer is no :) I'm writing this for people like me who has ran into this problem. If you have a particular scenario where you have to calculate costs based on a salary of a particular employee, you must create an intermediate table where you have to do queries based on the date of a change in salary. Without going into details, we always talk about query. For example you can filter the values ​​by date, so you get a total cost based on the date with a different salaraio per employee. In any case, we must think about how you design your own database. I have untapped in this mistake because I thought such a thing were possible. The point of this discussion is that a thing can not be done in the database, otherwise try to find alternatives, like I did.

    I want to Thank everyone for their availability, especially Julie who also supported me on twitter :)

    And finally, sorry for my English, I promise to improve: D

    Tuesday, February 28, 2012 11:39 PM
  • Hi Mirko,

    I'm glad to hear that you have solved the issue and thanks for sharing the experience. If you have any problems, please feel free to post in the forum. With your help, the forum will be better and better.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 29, 2012 1:14 AM
    Moderator