CODEWITHSUNDEEP
c#linq
Eric
Eric

Reputation: 359

LinQ how to change a integer like 1 to string 001

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.

Upvotes: 5

Views: 6386

Answers (5)

Arturo Menchaca
Arturo Menchaca

Reputation: 15982

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.

Upvotes: 3

Ivan Stoev
Ivan Stoev

Reputation: 205729

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

Upvotes: 7

David BS
David BS

Reputation: 1892

Did you try:

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

?

Upvotes: 2

ΩmegaMan
ΩmegaMan

Reputation: 31686

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.

Upvotes: 1

Ryan Intravia
Ryan Intravia

Reputation: 420

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');

Upvotes: 1

Related Questions