public void Test()
{
// https://pgexercises.com/questions/string/pad.html
// The zip codes in our example dataset have had leading zeroes removed from them by
// virtue of being stored as a numeric type. Retrieve all zip codes from the members table,
// padding any zip codes less than 5 characters long with leading zeroes. Order by the
// new zip code.
//
// select lpad(cast(zipcode as char(5)),5,'0') zip from cd.members order by zip
EFServiceProvider.RunInContext(context =>
{
// Method - 1
var members = context.Members
.Select(member => new { ZipCode = Convert.ToString(member.ZipCode) })
.OrderBy(m => m.ZipCode)
.ToList();
/*
* SELECT CONVERT(nvarchar(max), [m].[ZipCode]) AS [Zip]
* FROM [Members] AS [m]
* ORDER BY CONVERT(nvarchar(max), [m].[ZipCode])
*/
// Now using LINQ to Objects
members = members.Select(member => new { ZipCode = member.ZipCode.PadLeft(5, '0') })
.OrderBy(m => m.ZipCode)
.ToList();
var expectedResult = new[]
{
new { ZipCode = "00000" },
new { ZipCode = "00234" },
new { ZipCode = "00234" },
new { ZipCode = "04321" },
new { ZipCode = "04321" },
new { ZipCode = "10383" },
new { ZipCode = "11986" },
new { ZipCode = "23423" },
new { ZipCode = "28563" },
new { ZipCode = "33862" },
new { ZipCode = "34232" },
new { ZipCode = "43532" },
new { ZipCode = "43533" },
new { ZipCode = "45678" },
new { ZipCode = "52365" },
new { ZipCode = "54333" },
new { ZipCode = "56754" },
new { ZipCode = "57392" },
new { ZipCode = "58393" },
new { ZipCode = "64577" },
new { ZipCode = "65332" },
new { ZipCode = "65464" },
new { ZipCode = "66796" },
new { ZipCode = "68666" },
new { ZipCode = "69302" },
new { ZipCode = "75655" },
new { ZipCode = "78533" },
new { ZipCode = "80743" },
new { ZipCode = "84923" },
new { ZipCode = "87630" },
new { ZipCode = "97676" },
};
members.Should().BeEquivalentTo(expectedResult);
// Method - 2, Using a custom DbFunction
var newMembers = context.Members
.Select(member => new
{
ZipCode =
SqlDbFunctionsExtensions.SqlReplicate(
"0", 5 - Convert.ToString(member.ZipCode).Length)
+ member.ZipCode
})
.OrderBy(m => m.ZipCode)
.ToList();
/*
* SELECT REPLICATE(N'0', 5 - CAST(LEN(CONVERT(nvarchar(max), [m].[ZipCode])) AS int)) + CAST([m].[ZipCode] AS nvarchar(max)) AS [Zip]
* FROM [Members] AS [m]
* ORDER BY REPLICATE(N'0', 5 - CAST(LEN(CONVERT(nvarchar(max), [m].[ZipCode])) AS int)) + CAST([m].[ZipCode] AS nvarchar(max))
*/
newMembers.Should().BeEquivalentTo(expectedResult);
});
}
