LinQ how to change a integer like 1 to string 001

Question

I have a table in my database with four columns:

string: year
string: weeknr
int:    number

In other tables I combine these columns into yywwnnn as a string.

The number columns is a identity column.

Now I want to retrieve some records from a table that I want to join with the mentioned table.

something like:

from R in db.Requests
join RN in db.RequestNumbers on R.RequestNumber equals (RN.Year + RN.Weeknr + RN.Number)

But of course RN.Number is a integer and I need it to be a 3 digit string.

so:
16 07 1 ==> 1607001
16 07 10 ==> 1607010
16 07 100 ==> 1607100

I have tried this:

from R in db.Requests
join RN in db.RequestNumbers on R.RequestNumber equals (RN.Year + RN.Weeknr + (RN.Number.toString().PadLeft(3,char.Parse("0")))

But PadLeft is not recognized.
Is there any other solution to this?

[EDIT] This is the full method:

public List<RequestList> getMyRequests(string userID)
{
  var result = (from R in db.Requests
                join MT in db.MainTorsList on R.Category equals MT.MainTorsListID into MTL
                from MT in MTL.DefaultIfEmpty()
                join ST in db.SubTorsList on R.RequestType equals ST.SubTorsListID into STL
                from ST in STL.DefaultIfEmpty()
                join S in db.StatusList on R.RequestStatus equals S.StatusListID into SL
                from S in SL.DefaultIfEmpty()
                join RN in db.RequestNumber on R.RequestNumber equals RN.Year + RN.Week + (RN.Number.ToString().PadLeft(3, char.Parse("0"))) into RNL
                from RN in RNL.DefaultIfEmpty()
                where R.CreatedBy == userID && RN.Removed == false

                select new
                {
                  RequestID = R.RequestID,
                  RequestDate = R.CreatedOn,
                  RequestNumber = R.RequestNumber,
                  Category = MT.Name,
                  RequestType = ST.Name,
                  Description = R.RequestDescription,
                  Status = S.Name,
                  Options = ""
                }
                );

  List<RequestList> requests = (List<RequestList>)result.ToList().ToNonAnonymousList(typeof(RequestList));

  return requests;
}

The error message:

Additional information: LINQ to Entities does not recognize the method 'System.String PadLeft(Int32, Char)' method, and this method cannot be translated into a store expression.

You can do this with the .ToString() method. and then re-append. `int a = 1; console.writeline(a.tostring("000"));` — Charles May, Feb 16 '16 at 22:03
What do you mean by "PadLeft" is not recognized? Do you get an error message? Are you using LINQ-to-SQL or Entity Framework? — Heinzi, Feb 16 '16 at 22:13

Ivan Stoev · Accepted Answer · 2016-02-17T07:43:21.757

7

The trick is to use DbFunctions.Right like this

DbFunctions.Right("00" + RN.Number, 3)

i.e. prepend enough zeros at the beginning and take the exact length needed from the end.

All the used methods are supported by LINQ to Entities (at least in the latest at the moment EF6.1.3).

edited Feb 17 '16 at 07:43

answered Feb 17 '16 at 07:33

Ivan Stoev

195,425
15
312
343

Arturo Menchaca · Answer 2 · 2016-02-17T14:34:00.823

When you creates a linq expression pointing to a sql-database this it is translated into a sql query and there are functions that cannot be translated to sql (such as string.Format(), object.ToString()). When an unsupported function is used, an exception like yours is raised.

'SqlFunctions' and 'EntityFunctions' classes provides 'CRL methods' that you can use in Linq to Entities expressions.

SqlFunctions.StringConvert() converts an integer to its string representation, allowing you specify the desired length (filling with leading spaces).

You can use this function and call string.Replace(string, string) method (yes, it's available) to replace spaces to zeros.

This is the query:

from R in db.Requests
join RN in db.RequestNumbers on R.RequestNumber equals 
(RN.Year + RN.Weeknr + SqlFunctions.StringConvert((double)RN.Number, 3).Replace(" ", "0"))

Hope this helps.

Thank you for the background explanation. – peter_the_oak Aug 14 '20 at 07:52 — peter_the_oak, Aug 14 '20 at 07:52

score 2 · Answer 3 · answered Feb 17 '16 at 01:30

2

Did you try:

     Rn.number.value.ToString("000", System.Globalization.CultureInfo.InvariantCulture)

?

answered Feb 17 '16 at 01:30

David BS

1,822
1
19
35

score 1 · Answer 4 · answered Feb 16 '16 at 22:36

You can create the string in the needed format and then use it in your expression. Here's a quick example I did to pad '0' depending on the length of the number after the fourth character. You wouldn't need to assign var mystring = "your string" and so forth, I was just doing that so I didn't need to type in all the scenarios each time to run it:

var exampleA = "16071";
var exampleB = "160710";
var exampleC = "1607100";

var mystring = exampleB;

var afterFourthCharacter = mystring.Substring(4);

var result = mystring.Substring(0,4) + afterFourthCharacter.PadLeft(mystring.Length + (3 - mystring.Length), '0');

this is in C#, but I need it in Linq to entities. – Eric Feb 16 '16 at 22:40 — Eric, Feb 16 '16 at 22:40

score 1 · Answer 5 · answered Feb 17 '16 at 00:05

Create dynamic keys with the value and let Linq sort it out. Something like this:

db.Requests                   // Outer
  .Join(db.RequestNumber      // Inner
        R => new  { R.Year, R.Weeknr, R.Number },    // Outer key
        RN => new { RN.Year, RN.Weeknr, RN.Number }, // Inner key
        (R, RN) => new
            {
              R.Year,
              RN.Number,
              ....
            }
       );

Note because this is EF (or Linq To SQL) you may have to bring down the whole table (use .ToList()) on the Requests and RequestNumber together.

Or better yet if you have access to the database table, simply create a computed column whose heuristics will combine all three columns into a unique identity key. Then in Linq it can join on that id from the computed column.

LinQ how to change a integer like 1 to string 001

5 Answers5

Linked