LINQ & Entity Framework : issue with concatenating NULL value

Question

I have an INT fields called TradeNo that may contain NULL value. The requirement is to display on the dropdown the "contract Id" and the "trade no" in bracket and if "trade no" is null, then display N/A.

Example:
44444 (2222)
55555 ( N/A )

Here’s what I thought would work. This is my functions that returns a SelectList

public IEnumerable<SelectListItem> GetContractsBySupplierDropDown(string supplier)
{
    var result = from c in context.Contracts 
                     where c.Supplier==supplier 
                     orderby c.ContractID
                     select new { 
                            Text = c.ContractID.ToString() + " (" + 
                                   (c.TradeNo.HasValue  ? 
                                       c.TradeNo.Value.ToString() : 
                                       " N/A ").ToString() + 
                                    " )", 
                            Value = c.ContractID };

    return new SelectList(result, "Text", "Value"); 
}

The error being return is:

LINQ to Entities does not recognize the method 'System.String ToString()'
method, and this method cannot be translated into a store expression.

From what I can tell, the error displayed means that EF is trying to convert ToString to a database function?

score 2 · Accepted Answer · answered Jul 24 '12 at 13:34

Unfortunately you cannot use ToString in a number field coming from the database.

As a work around you can cast ToList before doing it, so then the contents are in the memory.

public IEnumerable<SelectListItem> GetContractsBySupplierDropDown(string supplier)
{
    var query =  from c in context.Contracts 
                 where c.Supplier==supplier 
                 orderby c.ContractID
                 select new {
                     c.ContractID,
                     c.TradeNo,

                 };
    var result = from c in query.ToList()
                 select new { 
                            Text = c.ContractID.ToString() + " (" + 
                                   (c.TradeNo.HasValue  ?
                                       c.TradeNo.Value.ToString() : 
                                       " N/A ") + 
                                    " )", 
                            Value = c.ContractID
                  };

    return new SelectList(result, "Text", "Value");
}

LINQ & Entity Framework : issue with concatenating NULL value

1 Answers1