Entity Framework use of indexes and foreign keys

Question

I want to be able to search a table quite quickly using the Entity Framework, say if I have a Contacts table, a JobsToDo table and a matrix table linking the two tables e.g Contacts_JobsToDo_Mtx and I specify two foreign keys in the Contacts_JobsToDo_Mtx table, if I wanted to search this Mtx table, do I need to specify an index on the two foreign keys? Or by the fact that they are two foreign keys are they considered indexed on them anyway? Will the Entity Framework be able to search through the Mtx table quickly without having to specfiy an index on both keys? Thanks!

Answer 1

Depends on the database backend you have, but typically (from my SQL Server experience), no. Just using the Entity Framework doesn't magically make your SQL Server restrictions (or potentials for speed gains!) go away.

If the underlying database is crap, the EF performance will be crap. Your database needs to be in good shape - just slapping EF over it won't make the performance issues suddenly go away.

So I guess you'll still have to put some brainpower into making sure you get the appropriate indices in place to make your system perform well. EF can only be as good as the underlying database is....

Answer 2

Entity Framework 1 has nothing to do with creating your schema or adding foreign keys to your "matrix", commonly called many to many, tables. Entity Framwork 4.0 will can create these tables for you and will have the required keys and indexes.

Indexes are automatically created for any primary keys so you don't have to worry about setting up the correct index if you've created the table correctly.

Entity Framework does write optimized queries for 99% of the common CRUD scenarios. If your getting crazy with esql or linq statements they might not be as optimized but should still perform just fine with small to medium scaled query access.

Entity Framework isn't responsible for using the correct indexes, thats your underlying data stores query optimizers job. You can write badly performing queries that don't use indexes properly but thats really an edge case I wouldn't worry about until you find a problem.

TL:DR;

Entity Framework either isn't responsible for doing the jobs your asking about and performs just fine in most circumstances.