Feature: Generate Parameterized SQL

[sspekinc]

Dynamically generated SQL contains inline constants, which causes SQL Server to compile the query plan every time a new constant is provided for the same query. Compilation is a costly operation and it can be avoided if the generated SQL is parameterized.

Expression.Constant calls can be replaced by a "wrapped" constant as explained in the article below so that the generated SQL is parameterized and the query plan will be cached by the SQL Server.

https://github.com/graeme-hill/gblog/blob/master/source_content/articles/2014.139_entity-framework-dynamic-queries-and-parameterization.mkd

[StefH]

This looks interesting. I'll investigate this.

[sspekinc]

I have modified the class System.Linq.Dynamic.Core.Parser.ExpressionHelper so that it generates parameterized SQL. It's working fine for me so far, but I couldn't run the tests yet so please take it with a grain of salt. Here is the proposed changes below.

#185

[StefH]

I added some review comments, please take a look.

About the unit-tests, why doesn't this work on your machine?

And this PR did trigger CI build + test, take a look at the 3 fails.

[StefH]

Closing this : PR has been merged.

[BradleyUffner]

How would I actually use this feature? I'm using version 1.0.9.2 of the nuget package (released after this PR was merged), and it is still inlining constants instead of using sql parameters.

[BradleyUffner]

After more testing, it seems to be a problem with Contains, StartsWith, and EndsWith.

If I use .Where(config, "ActivityCode==@0", "AB") it works as expected, generating the parameterized value, but .Where(config, "ActivityCode.Contains(@0)", "A") still inlines the value.

[BradleyUffner]

I created a new issue for this.