c# - WithSqlFilter WITH (NOEXPAND) hint not correct in output SQL -
i'm trying use new .withsqlfilter()
extension method add "with (noexpand)" hint select statement generated against indexed view. however, output sql putting hint after clause instead of clause. customer dto has [alias("customerinfo")]
. intended behavior or generating sql incorrectly?
var customer = db.from<customer>() .where(ci => ci.customerid == customercode) .withsqlfilter(sql => issqlserver(db) ? sql + " (noexpand)" : sql); return db.single(customer);
edit: needed issqlserver method because have unit tests , other code uses sqlite instead of sql server , needed able ignore hint based on connection.
private bool issqlserver(idbconnection db) { var dialect = db.getdialectprovider(); return dialect servicestack.ormlite.sqlserver.sqlserverormlitedialectprovider || dialect servicestack.ormlite.sqlserver.sqlserver2012ormlitedialectprovider || dialect servicestack.ormlite.sqlserver.sqlserver2014ormlitedialectprovider || dialect servicestack.ormlite.sqlserver.sqlserver2016ormlitedialectprovider; }
generated sql:
select top 1 "customerid", "firstname", "middleinitial", "lastname", "address", "city", "state", "zipcode", "dateofbirth", "homephonenumber", "cellphonenumber", "emailaddress", "enrollmentdate", "isemployee", "modifydate", "lastmodifiedbyemployee" "customerinfo" ("customerid" = @0) (noexpand) params: @0=123456
the withsqlfilter
gets called complete sql statement if you're concatenating " (noexpand)" @ end of sql string that's appends to.
to extend clause can either replace like:
.withsqlfilter(sql => issqlserver(db) ? sql.replace("from \"customerinfo\"", "from \"customerinfo\" (noexpand)") : sql);
or can extend fromexpression
, e.g:
var q = db.from<customer>() .where(ci => ci.customerid == customercode); q.fromexpression += " (noexpand)";
Comments
Post a Comment