formatting numbers in a linq query RRS feed

  • Question

  • I want to display a combination of 4 fields in a listBox.DisplayMember

    in Linq I do

    finalString = F1+" " +F2 +" " +F3

    this works Ok except 1 of the fields is a number this displays like 1.500000, I want to format to 1.5


    I use string.format("{0:N1}",F2) when put in the final string this throws 'error could not translate expression 'table...'

    it is Ok if I use it on it's own only when I concatenate


    thanks in advance





    Friday, July 25, 2008 2:50 PM

All replies

  • You could override the ToString of the object being returned to do the String.Format - the listbox will try and use ToString() to display the objects added.



    Monday, July 28, 2008 8:28 PM
  • Thanks for your response

    not sure what I am doing wrong

    if I use fq as the display member I get 1.0 Good but incomplete string

    if I use fn I get "xxtextxx 1.0000000" bad

    if I try to use fo the sql is not generated

    if i try fp it fails to compile


    var Ing = from di in db.TblDishItems

    join ids in db.TblLiqItems on di.IID equals ids.LiqId

    where di.RId == id

    orderby ids.LiqItemName

    select new


    fq = string.Format("{0:N1}",di.RQuant),

    fn = ids.BinNo + " " + ids.LiqItemName + " " + di.RQuant.ToString(),

    //fo = ids.BinNo + " " + ids.LiqItemName + " " + string.Format("{0:N1}", di.RQuant),

    fp =ids.BinNo + " " + ids.LiqItemName + " " + di.RQuant.ToString("{0:N1}"),



    lb.DataSource = Ing;

    lb.ValueMember = "IID";

    lb.DisplayMember ="fq";

    Tuesday, July 29, 2008 10:38 AM
  • (Six years later...!)

    If anyone else hits this issue, here's the solution I used.

    I have some nullable decimals in one of my database tables, so even if LINQ would let me use the following ToString() command (which it doesn't !), it would introduce a bug, and cause the code crash as soon as a NULL value was read in.

    var query = (from cust in dc.Customers
    	     select new
    	       ID = cust.Customer_ID,
    	       Debt = cust.Customer.Debt.ToString("0,00"),  //  YOU CAN'T DO THIS !!

    My solution was to split my LINQ into two parts.

    First, I loaded the database records I needed:

    var query = (from cust in dc.Customers
    	     select new
    	       ID = cust.Customer_ID,
    	       Debt = cust.Customer.Debt,

    And then I used a second LINQ query to add the formatting, using a SafeFormat function:

    var results = (from cust in query
     	       select new
    	         ID = cust.Customer_ID,
    	         Debt = SafeFormat(cust.Debt, "0,00"),
    public static string SafeFormat(decimal? value, string format)
        if (value == null)
            return "";
        string str = value.Value.ToString(format);
        return str;

    So, the key is to split the database part of your LINQ, to the formatting part.  You can't mix the two.

    Monday, June 16, 2014 7:54 AM