EasyQuery - Wrap All Columns in the Select Statement in Brackets

Question

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.

Update 1

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...

According to the EasyQuery docs, you can do that with a setting in the Data Model: **Quote field name in SQL check box means if the field name should be put in quotes in SQL statements. Useful for field names which includes some reserved words. Field names with spaces will be quoted automatically.** — Laughing Vergil, Aug 09 '18 at 22:48
@LaughingVergil I'm sure you can, but unfortunately I was not able to locate the property in the EasyQuery's documentation, and I don't see any property named "Quote" or "Field" as part of the model, maybe you have a code example or a link to said documentation that would help clarify things for me? — David Rogers, Aug 09 '18 at 22:56
Your [referencing this](http://docs.korzh.com/easyquery/core/dme/entities-page), yes that would work if I was using a data model, but in this example I'm using the "LoadFromConnection" method to dynamically set the database. The fact that I don't know the database layout(and have no ability to write to the database either) before connecting to it is another complicating reason why I ask this question, otherwise I'd just write a series of views that contain sanitized columns names, then filter to that. Since I can't do that, and I can't write up a data mode, is there another way to do this? — David Rogers, Aug 10 '18 at 14:23
[EasyQuery's Support](https://korzh.com/account/ticket/create) is dead, 404 link when you submit your ticket. No support email address listed, this one may not get solved soon :( — David Rogers, Aug 16 '18 at 15:27
[EasyQuery's support](https://korzh.com/support) works well now. There was a bug during the website update which caused that 404 problem a few days ago. Sorry for any inconvenience. — Sergiy, Aug 21 '18 at 06:41

score 1 · Answer 1 · answered Aug 21 '18 at 06:09

1

There is Formats property of SqlQueryBuilder class which includes QuoteColumnAlias option. Turn it on to make SqlQueryBuilder take column aliases in quotes (or brackets).

builder.Formats.QuoteColumnAlias = true;
build.BuildSQL();
.   .   .   .   .

answered Aug 21 '18 at 06:09

Sergiy

1,912
2
16
25

Hmmm, doesn't seem to work, I've added the details in my question. – David Rogers Aug 21 '18 at 14:54
For that particular `Open` attribute you will need to turn on Quote option. You can do it using Data Model Editor (if you create your model "manually"). Otherwise (if you load your model from DbContext for example), you can do it in code right after model loading: var attr = model.FindEntityAttr("DataImport_2018.Open"); (attr as DbEntityAttr).Quote = false; – Sergiy Aug 22 '18 at 21:11
Ouch, so there's no generic way to do this. Hmmm that's tricky, the way I'm using the tool set is too point it at a new databases each implementation, unfortunately I can't predict the database ahead of time. Maybe I could iterate on all "EntityAttr" and enable "Quote" or do it for all [reserved words](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017)? Actually that might work, I'll try it out... – David Rogers Aug 22 '18 at 21:30
Yes you can do it quite easily: model.EntityRoot.Scan(null, attr => (attr as DbEntityAttr).Quote = true); – Sergiy Aug 24 '18 at 20:37

score 0 · Accepted Answer · answered Aug 22 '18 at 22:09

0

@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.

answered Aug 22 '18 at 22:09

David Rogers

2,601
4
39
84

1

You can do it in more simple way for the whole model (not only for the attributes which participates in the query): EasyQueryCachedObject.Model.EntityRoot.Scan(null, attr => (attr as DbEntityAttr).Quote = true); – Sergiy Aug 24 '18 at 20:43

EasyQuery - Wrap All Columns in the Select Statement in Brackets

Update 1

2 Answers2