I have easy query code that generates a dynamic SQL statement:
//Model is loaded with "model.LoadFromConnection"
var EasyQueryCachedObject = new DbQuery();
EasyQueryCachedObject.Model = model; //Specified before
EasyQueryCachedObject.Model.Description = "XXX";
EasyQueryCachedObject.Formats.SetDefaultFormats(FormatType.MsSqlServer);
... //More logic to form query
var builder = new SqlQueryBuilder(EasyQueryCachedObject);
if (builder.CanBuild)
{
builder.BuildSQL();
var SQLQuery = builder.Result.SQL;
var QueryXML = builder.Result.Query.SaveToString();
}
The issue is that I'm querying tables that generate SQL such as the following:
SELECT Entry No
,Item No
,Open
FROM.[dbo].[DataImport_2013]
This fails because the column names contain reserved words/spaces, how can I get EasyQuery to wrap all columns with square brackets such as the following?
SELECT [Entry No ]
,[Item No ]
,[Open]
FROM.[dbo].[DataImport_2013]
Obviously a solution using a built in parameter of EasyQuery would be preferred, but if that's not possible maybe some sort of regex or post model population may work too, really I'm open to any solution that would address the need.
Tried several things, including the "QuoteColumnAlias", the query that is generated is similar to the follow:
SELECT DataImport_2018."Entry No " AS "DataImport_2018 Entry No",
DataImport_2018."Item No " AS "DataImport_2018 Item No",
DataImport_2018.Open AS "DataImport_2018 Open"
FROM dbo.DataImport_2018 AS DataImport_2018
Still errors on the reserve word "open", somehow I need to get the quotes to cover all column, not just the ones with spaces...
@Sergiy comment was extremely helpful and I came up with a way to do it post loading the model:
foreach (var column in EasyQueryCachedObject.Columns)
{
if (column.Expr.GetType() == typeof(DbEntityAttrExpr))
{
((DbEntityAttrExpr)column.Expr).Attribute.Quote = true;
}
}
Seems to work, all columns are correctly wrapped and the query executes without error.