none
EF4 CTP4 Code First - mapping to an indexed view

    Question

  • Hi

    I have an existing database which has a Customers table and Transactions table.  In order to calculate the balance for each customer we have an indexed view which sums the values from the transactions table.  Each customer can have a balance for multiple currencies (i.e. $ balance and EURO balance etc.) -- the view therefore returns a rows for each customer and currency combination.  

    I’m thinking about creating a class to model a balance and then map it to the balances view. The customer class will then contain a collection of these balances.

    My questions therefore are:

    1) Using EF code first should I be mapping to a view (especially in this situation where we would never write back to the view as it's a sum of values)?

    2) Although I have a customer id column in the view, it isn't a defined foreign key (as you would have on a table).  Is this going to cause a problem for EF Code First?

    3) Is there a trick that I am missing here?  We find that without the use of an indexed view (i.e. a normal non indexed view) the calculation of the balances takes too long.  But I'd be interested to know if anyone has any alternative ideas for dealing with this problem, particularly in the world of EF.

    Thanks.

    Paul.

    Wednesday, August 25, 2010 12:54 PM

Answers

  • Hi Paul,

    1) You can certainly use Code First to map to a view, just tell Code First that it's a table and it will use the same SQL against the view that it would for a table. Obviously if your view isn't writeable then saving is going to fail if you try and update values in the entities that are based on the view, but we will just delegate to the database so if you don't update these entities then you won't have any issues.

    2) You don't need to have a foreign key in the database, Code First will assume you have one, but in this case that won't cause any issues because there will always be a matching customer for each row anyway. You will need to give Code First a valid primary key for the view, which sounds like it would be customer_id and currency_code in your case.

    3) Indexed views sound like the right tool for the job :)

    ~Rowan

    Sunday, August 29, 2010 12:33 AM

All replies

  • Hi Paul,

    1) You can certainly use Code First to map to a view, just tell Code First that it's a table and it will use the same SQL against the view that it would for a table. Obviously if your view isn't writeable then saving is going to fail if you try and update values in the entities that are based on the view, but we will just delegate to the database so if you don't update these entities then you won't have any issues.

    2) You don't need to have a foreign key in the database, Code First will assume you have one, but in this case that won't cause any issues because there will always be a matching customer for each row anyway. You will need to give Code First a valid primary key for the view, which sounds like it would be customer_id and currency_code in your case.

    3) Indexed views sound like the right tool for the job :)

    ~Rowan

    Sunday, August 29, 2010 12:33 AM
  • how would i query view whose names are like

     

    ACT.View1, ACT.Table1, ACT.SP1 etc.

     

    I am using SQL 2008 R2 and objects in our database are queried with The name of the database is VM.

    how can i instruct that SQL include ACT  in SQL queries, now it should be dynamic as some PTS.View1 or PTS.Table1

    Tuesday, August 16, 2011 5:52 PM