Jurgen Cuschieri Jurgen Cuschieri - 2 years ago 158
C# Question

Does SingleOrDefault() query remotely?

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?

Answer Source

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 select1).

However if you are using Enumerable.SingleOrDefault then it will be processed on the (database) client.

You need to avoid converting from IQuerable<T> to IEnumerable<T> (eg. by use of AsEnumerable).

Detecting this can be done with code inspection or by looking at the generated SQL.

1 top 2 is used to detected more than a single result which is an error when using SingleOrDefault.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download