Avatar of Douglass MacLean
Douglass MacLean
Flag for United States of America asked on

Performance Question on Entity Framework

We are building a Web Portal, OurPortal. The IT Infrastructure includes a pair of Virtual Servers: AppSvr and DBSvr running Windows Server 2019. The database is on SQL Server 2019.

The database has been designed with many tables, PKs, and FKs.

The development team has chosen to use Entity Framework for development speed.


Here is a simple test case and my question:

Two Entities are Torganization and TContact

OurPortal issues a request to retrieve a subset of records from Tcontacts.

Select * from Tcontacts where (FirstName = ‘Bill’)  or (LastName like ‘%son%’);

Three EDM variants for implementing the query:

  • SQL Query for a specific entity type.
  • SQL Query for a primitive data type.
  • SQL commands.

The question: With each variant, which server is executing the query and applying the WHERE clause: AppSvr or DBSvr?

Windows OSWindows Server 2019Databases* Entity Framework Core

Avatar of undefined
Last Comment
Chinmay Patel

8/22/2022 - Mon

I am not an expert, but I believe that in all cases the SQL server will execute the query. The application server is the SQL client.
Chinmay Patel

There are certain where clauses which will not be applied on the SQL Server. Especially, where you modify the condition in C#, for example DateTime manipulation, string manipulation and so on.
Douglass MacLean

Ah, interesting, Chinmay.
Is this a valid restatement of your point?
In certain cases the where clause needs to get applied on the App Server so the raw Select content gets transferred to the app server.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Chinmay Patel

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Douglass MacLean

Yes, we're on the same page. As you say, doing the filter on the client app is tolerable for a small dataset.
This portal we are building already has a rather complex database and we expect it to grow to be quite a large one. I expect we will end up creating and using a lot of stored procedures containing 'raw SQLstatements.

Chinmay Patel

Most probably yes but I have a feeling that EF Core may surprise you. When done right, it can do wonders even against a large volume and complexity.