Expressions trees and Coalesce<\/a> ).<\/p>\nAlthough LINQ to SQL could optimized out the Coalesce operator, in VS 2008 RTM it converted from a predicate logic to value logic (and vice versa) which in some cases blows the applicable index on the column.<\/p>\n
Here’s an example for the SQL that is generated in VS 2008 RTM:<\/p>\n
<\/p>\n
SELECT [t0].[CustomerID]
FROM [dbo].[Orders] AS [t0]
WHERE (COALESCE(
(CASE
WHEN [t0].[EmployeeID] = @p0 THEN 1
WHEN NOT ([t0].[EmployeeID] = @p0) THEN 0
ELSE NULL
END),@p1)) = 1
— @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [123]
— @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]<\/p>\n
<\/h2>\n<\/h2>\nThe Good News<\/h3>\n
The good news is that the LINQ to SQL team has a fix to their code generation to recognize this pattern, and now they emit the SQL code that you would have expected to emit:<\/p>\n
SELECT [t0].[CustomerID]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[EmployeeID] = @p0
— @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [123]<\/p>\n
In other words, LINQ to SQL (and LINQ to Entities – we made the same fix there) is now smart enough to pass three-value logic from VB to SQL.<\/p>\n
In this particular case, both VB and SQL employs three-value boolean logic, but the intermediate layer (the LINQ operators), employed two-value boolean logic, and the translation between the layers was “lost”, which resulted in the non-optimized code that we generated in VS2008 RTM. Look forward to this in an update coming later this year.<\/p>\n
.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, “Courier New”, courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt\n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/p>\n","protected":false},"excerpt":{"rendered":"
Over the last few months, the VB and Data Programmability teams were working on fixing a performance problem with LINQ to SQL (which also manifested in LINQ to Entities). The issue was that LINQ to SQL was generating sub optimal T-SQL queries when the VB LINQ queries included filters over nullable columns. For example, consider […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[192,195],"tags":[94,154],"class_list":["post-4503","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-visual-basic","tag-linqvb9","tag-timothy-ng"],"acf":[],"blog_post_summary":"
Over the last few months, the VB and Data Programmability teams were working on fixing a performance problem with LINQ to SQL (which also manifested in LINQ to Entities). The issue was that LINQ to SQL was generating sub optimal T-SQL queries when the VB LINQ queries included filters over nullable columns. For example, consider […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4503","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=4503"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4503\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=4503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=4503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=4503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}