{"id":53412,"date":"2011-11-23T14:57:34","date_gmt":"2011-11-23T11:57:34","guid":{"rendered":"https:\/\/www.altoros.com\/blog\/?p=53412"},"modified":"2020-04-15T17:19:08","modified_gmt":"2020-04-15T14:19:08","slug":"linq-to-sql-alternative-to-the-where-in-expression","status":"publish","type":"post","link":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/","title":{"rendered":"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#The_SqlException_issue\" >The SqlException issue<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#Implementation_details\" >Implementation details<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#Further_reading\" >Further reading<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#About_the_author\" >About the author<\/a><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"The_SqlException_issue\"><\/span>The <code style=\"color: black; background-color: #e6e6e6;\">SqlException<\/code> issue<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Hi, everyone. Recently, I&#8217;ve been working on an application for which we used the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/bb425822.aspx\" rel=\"noopener noreferrer\" target=\"_blank\">Linq-To-Sql ORM<\/a>. We followed the <a href=\"https:\/\/martinfowler.com\/eaaCatalog\/repository.html\" rel=\"noopener noreferrer\" target=\"_blank\">repository pattern<\/a> 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.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2011\/11\/Linq-To-Sql.jpg\" alt=\"\" width=\"547\" height=\"237\" class=\"aligncenter size-full wp-image-53417\" \/><\/p>\n<p>So, the task is to retrieve entities by IDs using Linq To Sql, which should lead to SQL query like in the code below.<\/p>\n<div class=\"code\">\n<pre><span style=\"color: #0000ff;\">SELECT<\/span>\r\n   Name,\r\n   Email,\r\n   \u2026\r\n<span style=\"color: #0000ff;\">FROM<\/span>\r\n   Employee\r\n<span style=\"color: #0000ff;\">WHERE<\/span>\r\n   EmployeeId <span style=\"color: #999999;\">IN<\/span> (\r\n     \u201800000000-0000-0000-0000-000000000001\u2019,\r\n     \u201800000000-0000-0000-0000-000000000002\u2019,\u2026\r\n                 )<\/pre>\n<\/div>\n<p>Linq-To-Sql provider supports translation to this kind of queries by the <code style=\"color: black; background-color: #e6e6e6;\">contains<\/code> expression.<\/p>\n<div class=\"code\">\n<pre><span style=\"color: #0000ff;\">from<\/span> emp <span style=\"color: #0000ff;\">in<\/span> dataContext.Employees\r\n<span style=\"color: #0000ff;\">where<\/span> entityIds.Contains(emp.EmployeeId)\r\n<span style=\"color: #0000ff;\">select<\/span> emp<\/pre>\n<\/div>\n<p>The <code style=\"color: black; background-color: #e6e6e6;\">entityIds<\/code> parameter is a <code style=\"color: black; background-color: #e6e6e6;\">List&lt;Guid&gt;<\/code>, 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.<\/p>\n<div class=\"code\">\n<p><em>SqlException:<\/em><\/p>\n<p><em>The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2,100.<\/em><\/p>\n<\/div>\n<p>The reason is that every ID from the list is passed to SQL Server as a parameter, and we reached upper level of them.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Implementation_details\"><\/span>Implementation details<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>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 <a href=\"https:\/\/en.wikipedia.org\/wiki\/User-defined_function\" rel=\"noopener noreferrer\" target=\"_blank\">user-defined function<\/a> (UDF) that will get a list of IDs and return a table of IDs. The result of this function can be joined to the\u00a0table from which we would like to retrieve the entities.<\/p>\n<div class=\"code\">\n<pre><span style=\"color: #0000ff;\">from<\/span> employee <span style=\"color: #0000ff;\">in<\/span> dataContext.Employees\r\n<span style=\"color: #0000ff;\">join<\/span> ids <span style=\"color: #0000ff;\">in<\/span> dataContext.udf_ParseGuids(idList)\r\n<span style=\"color: #0000ff;\">       on<\/span> employee.EmployeeId <span style=\"color: #0000ff;\">equals<\/span> ids.Id\r\n<span style=\"color: #0000ff;\">select<\/span> employee<\/pre>\n<\/div>\n<p>First of all, we need to pass the list of IDs to UDF. As we know, SQL Server supports <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/bb510489.aspx\" rel=\"noopener noreferrer\" target=\"_blank\">table-valued parameters<\/a> 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 <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/system.xml.serialization.xmlserializer.aspx\" rel=\"noopener noreferrer\" target=\"_blank\">XmlSerializer<\/a>.<\/p>\n<div class=\"code\">\n<pre><span style=\"color: #0000ff;\">public static<\/span> <span style=\"color: #68ded2;\">XElement<\/span> SerializeGuidList(<span style=\"color: #68ded2;\">IList&lt;Guid&gt;<\/span> ids)\r\n{\r\n            <span style=\"color: #0000ff;\">using<\/span> (<span style=\"color: #0000ff;\">var<\/span> sw = <span style=\"color: #0000ff;\">new<\/span> <span style=\"color: #68ded2;\">StringWriter<\/span>())\r\n            {\r\n                <span style=\"color: #0000ff;\">var<\/span> guidArraySerializer = <span style=\"color: #0000ff;\">new<\/span> <span style=\"color: #68ded2;\">XmlSerializer<\/span>(<span style=\"color: #0000ff;\">typeof<\/span>(<span style=\"color: #68ded2;\">Guid<\/span>[]));\r\n                guidArraySerializer.Serialize(sw, ids.ToArray());\r\n                <span style=\"color: #0000ff;\">using<\/span> (<span style=\"color: #0000ff;\">var<\/span> sr = <span style=\"color: #0000ff;\">new<\/span> <span style=\"color: #68ded2;\">StringReader<\/span>(sw.ToString()))\r\n                {\r\n                    <span style=\"color: #0000ff;\">return<\/span> <span style=\"color: #68ded2;\">XElement.Load<\/span>(sr);\r\n                }\r\n            }\r\n}<\/pre>\n<\/div>\n<p>On the SQL Server side, we can parse this XML with <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms345122%28SQL.90%29.aspx\" rel=\"noopener noreferrer\" target=\"_blank\">XQuery<\/a>.<\/p>\n<div class=\"code\">\n<pre><span style=\"color: #0000ff;\">ALTER FUNCTION<\/span> [dbo].[udf_ParseGuids]\r\n(@ids xml)\r\n<span style=\"color: #0000ff;\">RETURNS<\/span> @temp\r\n<span style=\"color: #0000ff;\">TABLE<\/span>(Id uniqueidentifier)\r\n<span style=\"color: #0000ff;\">BEGIN<\/span>\r\n\t\t<span style=\"color: #0000ff;\">INSERT INTO<\/span> @temp(id)\r\n\t\t<span style=\"color: #0000ff;\">SELECT<\/span>\r\n\t\t\tlist.Id.value(<span style=\"color: #ff0000;\">'.'<\/span>, <span style=\"color: #ff0000;\">'uniqueidentifier'<\/span>)\r\n\t\t<span style=\"color: #0000ff;\">FROM<\/span>\r\n\t\t\t@ids.nodes(<span style=\"color: #ff0000;\">'\/*\/guid'<\/span>) as list(id)\r\n\t\t<span style=\"color: #0000ff;\">RETURN<\/span>\r\n<span style=\"color: #0000ff;\">END<\/span><\/pre>\n<\/div>\n<p>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.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Further_reading\"><\/span>Further reading<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/comparing-database-query-languages-in-mysql-couchbase-and-mongodb\/\">Comparing Database Query Languages in MySQL, Couchbase, and MongoDB<\/a><\/li>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/optimizing-the-performance-of-apache-spark-queries\/\">Optimizing the Performance of Apache Spark Queries<\/a><\/li>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/using-k-means-clustering-in-tensorflow\/\">Implementing k-means Clustering with TensorFlow<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"About_the_author\"><\/span>About the author<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2011\/11\/Aliaksei-Yenzhyieuski.png\" alt=\"\" width=\"102\" height=\"102\" class=\"alignright size-full wp-image-53426\" \/><small><b>Aliaksei Yenzhyieuski<\/b>  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.<\/small><\/p>\n<hr\/>\n<p><center><small>The post was written by Aliaksei Yenzhyieuski and edited by <a href=\"https:\/\/www.altoros.com\/blog\/author\/alex\/\">Alex Khizhniak<\/a>.<\/small><\/center><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The SqlException issue<\/p>\n<p>Hi, everyone. Recently, I&#8217;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 [&#8230;]<\/p>\n","protected":false},"author":5,"featured_media":53443,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","footnotes":"","_links_to":"","_links_to_target":""},"categories":[7],"tags":[895],"class_list":["post-53412","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-news-and-opinion","tag-research-and-development"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression | Altoros<\/title>\n<meta name=\"description\" content=\"The blog post overviews the SqlException issue and provides the solution based on utilizing a user-defined function to retrieve entities by IDs.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression | Altoros\" \/>\n<meta property=\"og:description\" content=\"The SqlException issue Hi, everyone. Recently, I&#8217;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 [...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/\" \/>\n<meta property=\"og:site_name\" content=\"Altoros\" \/>\n<meta property=\"article:published_time\" content=\"2011-11-23T11:57:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-15T14:19:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2011\/11\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png\" \/>\n\t<meta property=\"og:image:width\" content=\"640\" \/>\n\t<meta property=\"og:image:height\" content=\"360\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Alex Khizhniak\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alex Khizhniak\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/\"},\"author\":{\"name\":\"Alex Khizhniak\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#\\\/schema\\\/person\\\/3d914db6ad1b2908c32c0dc5dcabc420\"},\"headline\":\"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression\",\"datePublished\":\"2011-11-23T11:57:34+00:00\",\"dateModified\":\"2020-04-15T14:19:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/\"},\"wordCount\":545,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2011\\\/11\\\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png\",\"keywords\":[\"Research and Development\"],\"articleSection\":[\"News\\\/Opinion\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/\",\"name\":\"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression | Altoros\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2011\\\/11\\\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png\",\"datePublished\":\"2011-11-23T11:57:34+00:00\",\"dateModified\":\"2020-04-15T14:19:08+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#\\\/schema\\\/person\\\/3d914db6ad1b2908c32c0dc5dcabc420\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2011\\\/11\\\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png\",\"contentUrl\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2011\\\/11\\\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png\",\"width\":640,\"height\":360},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/linq-to-sql-alternative-to-the-where-in-expression\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/\",\"name\":\"Altoros\",\"description\":\"Insight\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#\\\/schema\\\/person\\\/3d914db6ad1b2908c32c0dc5dcabc420\",\"name\":\"Alex Khizhniak\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/druzya-edit1-150x150.jpg\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/druzya-edit1-150x150.jpg\",\"contentUrl\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/druzya-edit1-150x150.jpg\",\"caption\":\"Alex Khizhniak\"},\"description\":\"Alex Khizhniak is Director of Technical Content Strategy at Altoros and a cofounder of a local Java User Group. Managing distributed teams since 2004, he has gained experience as a journalist, an editor-in-chief, a technical writer, a technology evangelist, a project manager, and a product owner. Alex is obsessed with AI\\\/ML, data science, data integration, ETL\\\/DWH, data quality, databases (SQL\\\/NoSQL), big data, IoT, and BI. The articles and industry reports he created or helped to publish reached out to 3,000,000+ tech-savvy readers. Some of the pieces were covered on TechRepublic, ebizQ, NetworkWorld, CIO.com, etc. Find him on Twitter at @alxkh.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/https:\\\/\\\/twitter.com\\\/alxkh\"],\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/author\\\/alex\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression | Altoros","description":"The blog post overviews the SqlException issue and provides the solution based on utilizing a user-defined function to retrieve entities by IDs.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/","og_locale":"en_US","og_type":"article","og_title":"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression | Altoros","og_description":"The SqlException issue Hi, everyone. Recently, I&#8217;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 [...]","og_url":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/","og_site_name":"Altoros","article_published_time":"2011-11-23T11:57:34+00:00","article_modified_time":"2020-04-15T14:19:08+00:00","og_image":[{"width":640,"height":360,"url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2011\/11\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png","type":"image\/png"}],"author":"Alex Khizhniak","twitter_misc":{"Written by":"Alex Khizhniak","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#article","isPartOf":{"@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/"},"author":{"name":"Alex Khizhniak","@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/3d914db6ad1b2908c32c0dc5dcabc420"},"headline":"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression","datePublished":"2011-11-23T11:57:34+00:00","dateModified":"2020-04-15T14:19:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/"},"wordCount":545,"commentCount":0,"image":{"@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#primaryimage"},"thumbnailUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2011\/11\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png","keywords":["Research and Development"],"articleSection":["News\/Opinion"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/","url":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/","name":"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression | Altoros","isPartOf":{"@id":"https:\/\/www.altoros.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#primaryimage"},"image":{"@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#primaryimage"},"thumbnailUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2011\/11\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png","datePublished":"2011-11-23T11:57:34+00:00","dateModified":"2020-04-15T14:19:08+00:00","author":{"@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/3d914db6ad1b2908c32c0dc5dcabc420"},"breadcrumb":{"@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#primaryimage","url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2011\/11\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png","contentUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2011\/11\/Linq-To-Sql-Alternative-to-the-\u2018WHERE-IN\u2019-Expression.png","width":640,"height":360},{"@type":"BreadcrumbList","@id":"https:\/\/www.altoros.com\/blog\/linq-to-sql-alternative-to-the-where-in-expression\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.altoros.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Linq-To-Sql: Alternative to the \u2018WHERE IN\u2019 Expression"}]},{"@type":"WebSite","@id":"https:\/\/www.altoros.com\/blog\/#website","url":"https:\/\/www.altoros.com\/blog\/","name":"Altoros","description":"Insight","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.altoros.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/3d914db6ad1b2908c32c0dc5dcabc420","name":"Alex Khizhniak","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2017\/06\/druzya-edit1-150x150.jpg","url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2017\/06\/druzya-edit1-150x150.jpg","contentUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2017\/06\/druzya-edit1-150x150.jpg","caption":"Alex Khizhniak"},"description":"Alex Khizhniak is Director of Technical Content Strategy at Altoros and a cofounder of a local Java User Group. Managing distributed teams since 2004, he has gained experience as a journalist, an editor-in-chief, a technical writer, a technology evangelist, a project manager, and a product owner. Alex is obsessed with AI\/ML, data science, data integration, ETL\/DWH, data quality, databases (SQL\/NoSQL), big data, IoT, and BI. The articles and industry reports he created or helped to publish reached out to 3,000,000+ tech-savvy readers. Some of the pieces were covered on TechRepublic, ebizQ, NetworkWorld, CIO.com, etc. Find him on Twitter at @alxkh.","sameAs":["https:\/\/x.com\/https:\/\/twitter.com\/alxkh"],"url":"https:\/\/www.altoros.com\/blog\/author\/alex\/"}]}},"_links":{"self":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/53412","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/comments?post=53412"}],"version-history":[{"count":21,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/53412\/revisions"}],"predecessor-version":[{"id":53415,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/53412\/revisions\/53415"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/media\/53443"}],"wp:attachment":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/media?parent=53412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/categories?post=53412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/tags?post=53412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}