none
Using EF for complex queries RRS feed

  • Question

  • There has been a big push to start using EF instead of writing sql.  Suppose I am designing an application that will mostly be reading and displaying data.  I will be writing several queries and each query may be performing 5 to 10 joins.  Each table could potentially have several millions of rows.  Is using EF in this context a good idea?  Every example I've seen is just touching one table using simple CRUD operations.  Is EF used for querying large amounts of data using several joins where i may be only interested in one or two columns of data from each table join?  I feel like I'm missing something...
    Monday, May 20, 2013 7:55 PM

Answers

  • This is very broader question.  http://stackoverflow.com/questions/9739230/entity-framework-vs-stored-procedures-performance-measure It has to be dealt on a case by case basis. Please read the comments on the link. Hope it will give you some direction.
    Monday, May 20, 2013 8:31 PM
  •  To be honest, a query that uses many joins even with straight T-SQL is not a good practice. However, there are exceptions to the rule, like using the query to create reports.

    As for displaying data, no I wouldn't use a big join if it's only to display data at a screen with EF. I think that it would lead to performance issues if using a Linq query.

    I would use the EF backdoor that would allow me to use ADO.NET, SQL Command object and using a sproc. The results I would use a datareader and a custom object or a List<T> of custom objects populated from the resultset and datareader.

    Or I would use Entity SQL, which is like T-SQL, and return the results, use the EF datareader to read the resultset and populating  a custom object or objects in a List<T> return.

    Monday, May 20, 2013 8:35 PM

All replies

  • This is very broader question.  http://stackoverflow.com/questions/9739230/entity-framework-vs-stored-procedures-performance-measure It has to be dealt on a case by case basis. Please read the comments on the link. Hope it will give you some direction.
    Monday, May 20, 2013 8:31 PM
  •  To be honest, a query that uses many joins even with straight T-SQL is not a good practice. However, there are exceptions to the rule, like using the query to create reports.

    As for displaying data, no I wouldn't use a big join if it's only to display data at a screen with EF. I think that it would lead to performance issues if using a Linq query.

    I would use the EF backdoor that would allow me to use ADO.NET, SQL Command object and using a sproc. The results I would use a datareader and a custom object or a List<T> of custom objects populated from the resultset and datareader.

    Or I would use Entity SQL, which is like T-SQL, and return the results, use the EF datareader to read the resultset and populating  a custom object or objects in a List<T> return.

    Monday, May 20, 2013 8:35 PM
  • I have to disagree somewhat with this answer.  First off, SQL Server is a relational database.  The core purpose of a relational database is to normalize data.  Queries with a number for joins are par for the course and completed expected.  Using a combination of EF and linq you should be able to obtain perfectly acceptable performance even with a large number of joins and rows.
    Thursday, November 5, 2015 7:19 PM
  • I have to disagree somewhat with this answer.  First off, SQL Server is a relational database.  The core purpose of a relational database is to normalize data.  Queries with a number for joins are par for the course and completed expected.  Using a combination of EF and linq you should be able to obtain perfectly acceptable performance even with a large number of joins and rows.

    https://coding.abel.nu/2012/06/dont-use-linqs-join-navigate/
    Thursday, November 5, 2015 8:53 PM