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.

Answer 1

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

Answer 2

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.