Consider an environment with various computers at different locations. These computers have a Windows application running and a timer checking for new data to sync every 5 minutes. Information is synced to a centralised location over an internet connection. The database on both the client computers and the centralised server is an SQL Server database.
Before writing into the remote (centralised) server, the SingleOrDefault() method is used to check whether a record with the same ID exists remotely or not. The client has reported a surge in internet data used. I am wondering whether the centralised database has grown too large and the SingleOrDefault returns the whole queryable data over the connection and does the querying client side. Could this be the case? And what would be an alternate solution that does not do the same thing?
If you are using
Queryable.SingleOrDefault then it will be processed on the server (if you log the database queries you can see the use of
top 2 in the
However if you are using
Enumerable.SingleOrDefault then it will be processed on the (database) client.
You need to avoid converting from
IEnumerable<T> (eg. by use of
Detecting this can be done with code inspection or by looking at the generated SQL.
top 2 is used to detected more than a single result which is an error when using