CODEWITHSUNDEEP
c#.netentity-frameworklinq-to-entities
CalvinDale
CalvinDale

Reputation: 9731

How to prepend spaces in LINQ to Entities queries

In linq-to-entities queries, how spaces be prepended to shorter-length values when querying against an nvarchar column?

I seek this capability to facilitate proper sorting of certain fields.

Here is my scenario:

I have database that contains part number columns in virtually every table. These part number columns are represented as nvarchar(20) in the database. They usually contain a combination of both 6-digit and 8-digit part numbers. The vast majority of these part "numbers" are in fact numeric; but some are non-numeric. Consequently they are represented as strings rather than integers.

Because these part numbers are represented as strings rather than numbers, they do no sort properly. The 6-digit and 8-digit values are intermingled rather than separately grouped. So I want to pad the shorter length values with leading spaces to solve the sorting problem.

I do realize that if these columns were stored as nchar rather than nvarchar, I would get this padding automatically. But changing the data type for these columns is not an option at this point.

When I craft my own linq queries, I can do sorting properly:

db.table.Select(_ => _.partNumberColumn).OrderBy(_ => _.Length).ThenBy(_ => _);

The problem arises when other tools (such as grid) automatically compose queries. Then don't know about the additional sorting requirements. So it seems like a good solution is to prepend the spaces to the values that are of shorter length.

Here's an example query. When I build the model, rather than merely bringing in the values, I want to prepend 2 spaces to the 6-character values.

_db.HubAssembliesWides
    .Select(_ =>
        new Models.HubAssemblyModel()
        {
            HubAssemblyNumber = _.HubAssemblyNumber,
            DetailedOnNumber = _.DetailedOnNumber,
            HubMachiningNumber = _.HubMachiningNumber,
            HubCastingNumber = _.HubCastingNumber,
            ComponentHubAssemblyNumber = _.ComponentHubAssemblyNumber
        }
    );

So instead of 

HubAssemblyNumber = _.HubAssemblyNumber

I want

HubAssemblyNumber = _.HubAssemblyNumber.Length == 6 ? "  " + _.HubAssemblyNumber : _.HubAssemblyNumber

Can this be done? Thanks!

Upvotes: 1

Views: 425

Answers (1)

CalvinDale
CalvinDale

Reputation: 9731

Surprise! I didn't realize that the syntax that I proposed actually works.

HubAssemblyNumber = _.HubAssemblyNumber.Length == 6 ? "  " + _.HubAssemblyNumber : _.HubAssemblyNumber

Gets translated into a case statement when translated by linq-to-entities into T-SQL.

Upvotes: 2

Related Questions