Linq-To-Sql: Alternative to the ‘WHERE IN’ Expression

The SqlException issue

Hi, everyone. Recently, I’ve been working on an application for which we used the Linq-To-Sql ORM. We followed the repository pattern at our data access layer design. All of our repositories had the possibilities to retrieve domain entities by their keys (IDs). In this post, I would like to describe the challenging issue we faced when implementing this functionality, and how it was resolved.

So, the task is to retrieve entities by IDs using Linq To Sql, which should lead to SQL query like in the code below.

SELECT
   Name,
   Email,
   …
FROM
   Employee
WHERE
   EmployeeId IN (
     ‘00000000-0000-0000-0000-000000000001’,
     ‘00000000-0000-0000-0000-000000000002’,…
                 )

Linq-To-Sql provider supports translation to this kind of queries by the contains expression.

from emp in dataContext.Employees
where entityIds.Contains(emp.EmployeeId)
select emp

The entityIds parameter is a List<Guid>, which contains IDs of the requested entities. This solution works correctly, if we work with the IDs list containing up to 2,098 elements. Otherwise, we will get the exception below.

The reason is that every ID from the list is passed to SQL Server as a parameter, and we reached upper level of them.

Implementation details

There are several solutions to resolve this problem. First of all, we can split our initial list of IDs to several batches. In this case, we will get several calls to SQL Server, which may lead to reducing performance. In this post, I would like to describe the solution based on utilizing a user-defined function (UDF) that will get a list of IDs and return a table of IDs. The result of this function can be joined to the table from which we would like to retrieve the entities.

from employee in dataContext.Employees
join ids in dataContext.udf_ParseGuids(idList)
       on employee.EmployeeId equals ids.Id
select employee

First of all, we need to pass the list of IDs to UDF. As we know, SQL Server supports table-valued parameters only starting from the SQL Server 2008 version. So, in case of SQL Server 2000/2005, we can pass this list only as a string (for example, in the CSV format) or in XML. After carrying out several performance tests, it was discovered that utilizing XML is much better. XML serialization of the IDs list is given below and can be performed with a standard XmlSerializer.

public static XElement SerializeGuidList(IList<Guid> ids)
{
            using (var sw = new StringWriter())
            {
                var guidArraySerializer = new XmlSerializer(typeof(Guid[]));
                guidArraySerializer.Serialize(sw, ids.ToArray());
                using (var sr = new StringReader(sw.ToString()))
                {
                    return XElement.Load(sr);
                }
            }
}

On the SQL Server side, we can parse this XML with XQuery.

ALTER FUNCTION [dbo].[udf_ParseGuids]
(@ids xml)
RETURNS @temp
TABLE(Id uniqueidentifier)
BEGIN
		INSERT INTO @temp(id)
		SELECT
			list.Id.value('.', 'uniqueidentifier')
		FROM
			@ids.nodes('/*/guid') as list(id)
		RETURN
END

So, we can use a standard serialization approach in .NET for packaging data and the XQuery feature in SQL Server for parsing data to provide flexible way of transferring dynamic list of data to SQL Server. This way, we get less code efforts, while working with transferring any different kinds of criteria from .NET to SQL Server.

Further reading

• Comparing Database Query Languages in MySQL, Couchbase, and MongoDB
• Optimizing the Performance of Apache Spark Queries
• Implementing k-means Clustering with TensorFlow

About the author

Aliaksei Yenzhyieuski is Senior Software Engineer at Altoros with 16+ years of experience in software development. He is responsible for project management and team leading. Aliaksei can boast of solid expertise in computer science. Along with broad experience in implementation and maintenance of large-scale web, desktop, and mobile applications, he has strong object-oriented design and programming skills. In addition, Aliaksei is experienced in Agile and Scrum methodologies.