The Right Way To Use LINQ to SQL

Somewhere along the road everyone gets a taste of LINQ to SQL but not everyone uses it correctly or understands to power behind it. I have seen some very bad uses the past few weeks so I decided I should give a brief overview of how you should use it and how to harness the power of LINQ to SQL.

Somewhere along the road everyone gets a taste of LINQ to SQL but not everyone uses it correctly or understands to power behind it. I have seen some very bad uses the past few weeks so I decided I should give a brief overview of how you should use it and how to harness the power of LINQ to SQL.

What is LINQ

​Let us first take a look at what LINQ is, as on MSDN: "Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents." What this means is that it is a standard way of accessing data from numerous data providers, this allows us an easy way to abstract our data layer from our business layer. Any data provider that supports LINQ can then replace each other in your projects. This allows for instance that you can change from XML to SQL server with minimal effort as both support LINQ.

How to LINQ to SQL

Some of the mistakes I have seen are real easy to spot and fix, the thing we need to remember about LINQ is that it is a query building tool, in other words it does not return any data, it only returns a query, so what would happen if I build a LINQ statement such as the one below:

var user = context.Users.ToList()

Al though the above code is correct and it will do what we want, it is not the best approach, unless we want to return and display all users to the client. So if we had 5000 users this would build a query select * from Users and query the database for the users and assign it to the user variable. So what if we wanted to filter the users or implement some paging? Following the above we would do:

var user = context.Users.ToList().Where(u => u.Name.Contains("%Peter%"))

This will build a query and select the 5000 users from the database and only take the users from the result which contain Peter in the name field and assign it to the user variable. Can you see why this is an issue? Imagine doing this over 1 000 000 records?

The correct way to approach the above query is as follows:

var user = context.Users.Where(u => u.Name.Contains("%Peter%")).ToList();

This will build a query such as:

select * from Users where Name LIKE "%Peter%"

This is way better as once we do the ToList(), which executes the query it will only fetch the required results.

When using LINQ it is important to remember that when something returns an IQueryable that the query has not yet been executed on the data source. This is a good thing as it allows us to expand the query and add additional filters to the query before sending it to the data source. The best thing to do is to not Enumerate the query until the last possible moment, returning it to the client. This will allow for faster applications and data access as we are only retrieving data relevant to what we need.

So let us look at a few more samples:

Projections:

While we usually retrieve objects in the same way that we save them – by using the DbSet properties on our custom DbContext – we can also retrieve a sub-set of that data by using custom projections, or mapping the data to an object that doesn’t necessarily have anything to do with the entity/type that was used to save it to the database.

For example, if we only care about Users’ UserID and Username properties, we don’t want to pull back the entire User object:

var users = context.Users;

When we can use a projection to retrieve only those two columns:

var users = context.Users.Select(u => new { u.UserID, u.Username });

It’s important to note that the .Select() method enhances the query by specifying what it should return – it does not evaluate/execute the query. In fact, you can even continue extending the query with .Where() clauses, etc. based on this new projection; all the same rules apply.

The reason the above projection is preferable is because it actually modifies the SQL query to return only the fields you’ve requested.

In other words, instead of:

SELECT * FROM Users;

The generated SQL query is:

SELECT UserID, Username FROM Users;
Filtering

Furthermore, we need to apply this same logic to filtering queries (i.e. .Where() clauses) as well. Rather than pulling down all the data in a table and evaluating it on the client by prematurely evaluating the LINQ query, we want to apply as many filters and projects as possible.

In other words, this code snippet…

var users = context.Users.ToList().Where(u => u.ClientId == clientId);

Will produce the following (undesirable) SQL query that brings back way too much data:

SELECT * FROM Users;

Whereas, this code snippet…

var users = context.Users.Where(u => u.ClientId == clientId).ToList();

Will produce the following (desirable) SQL query that brings back a set of data that is filtered on the DB:

SELECT * FROM Users WHERE ClientId = 11;

Feel free to contact me if you need more examples or if you have any questions.



Related posts