Any workaround for lack of support for PadLeft in

问题:

I'm working on an app in MVC4 and Entity Framework 5 and recently came across this exception when executing my query.

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

When I have run across similar errors in the past, I've just made a variable outside the query and then used the variable in the LINQ statement. Unfortunately, in this case I'm manipulating the row results so I'm not sure how to go about that or if that is the best method. Any help would be appreciated. My query is below:

            IQueryable<System.String> LEAPrograms = db.Datamart_Draft
            .Where(where => where.snapshot_id == snapshot_id
                && !String.IsNullOrEmpty(where.entity_program))
            .Select(sel => (sel.entity_program.PadLeft(PROGRAMLENGTH, '0'))).Distinct();

回答1:

It's not too elegant but it does the job:

...
.Select(sel => SqlFunctions.Replicate
                   ("0", PROGRAMLENGTH - sel.entity_program.Length)
             + sel.entity_program)

回答2:

I think Gert Arnold solution in elegant. Here is a real solution based on his answer:

List<string> samplesWithoutMeasures = new List<string>();
samplesWithoutMeasures = (from mm in DB.MEDICIONESMUESTRA
   join mu in DB.MUESTRAS on mm.IDMUESTRA equals mu.IDMUESTRA
   where    (mu.IDESTADOMUESTRA >= 7 && mu.IDESTADOMUESTRA <= 8) && (mu.ESDUPLICADODE == null) &&
            (mm.IDESTADOMEDICIONMUESTRA == 1 &&  mm.IDPARAMETRO == Parameter.IDPARAMETRO) &&
            (mu.FECHARADICACION >= StartDate && mu.FECHARADICACION <= EndDate)
  select    SqlFunctions.Replicate("0", 15 - (SqlFunctions.StringConvert((double)mm.IDMUESTRA).Trim()).Length) 
            + SqlFunctions.StringConvert((double)mm.IDMUESTRA).Trim()
   ).ToList();

回答3:

I ended up creating a List then iterating through the results. When I do a .Distinct() on the List, it casts it back to an IEnumerable. I'm not sure performance-wise which is better, but with some effort I think a PadLeft could be created for LINQ to Entities that did approximately the same thing.

            IEnumerable<String> LEAPrograms = db.Datamart_Draft.Where(wh => wh.snapshot_id == snapshot_id && !String.IsNullOrEmpty(wh.entity_program)).Select(se => se.entity_program).Distinct();
        // create and populate a List (because LINQ to Entities doesn't support PadLeft)
        List<String> PaddedPrograms = new List<String>();
        foreach (var row in LEAPrograms)
        {
            PaddedPrograms.Add(row.PadLeft(4, '0'));
        }
        LEAPrograms = PaddedPrograms.Distinct();