LINQ to Entities StringConvert(double)' cannot be translated to convert int to string

Question

Problem

Need to convert int to string using EF4 + SQL CE4. The recommended option of using SqlFunctions.StringConvert(double) still gives me errors.

Option 1 (original). This gives me error:

public static IEnumerable<SelectListItem> xxGetCustomerList()
    {
        using (DatabaseEntities db = new DatabaseEntities())
        {
            var list = from l in db.Customers
                       orderby l.CompanyName
                       select new SelectListItem { Value = l.CustomerID.ToString(), Text = l.CompanyName };
            return list.ToList();
        }
    }

Option 2 (most suggested). Then as many posts suggests, I used the SqlFunctions.StringConvert() function from the library System.Data.Objects.SqlClient:

public static IEnumerable<SelectListItem> GetCustomerList()
    {
        using (DatabaseEntities db = new DatabaseEntities())
        {
            var list = from l in db.Customers
                       orderby l.CompanyName
                       select new SelectListItem { Value = SqlFunctions.StringConvert((double)l.CustomerID), Text = l.CompanyName };
            return list.ToList();
        }
    }

Which now shows below error:

The specified method 'System.String StringConvert(System.Nullable`1[System.Double])' on the type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression.

Option 3 (for very specific case). Then anoter post shows a smart solution using Dictionary, which finally works:

public static IEnumerable<SelectListItem> xGetCustomerList()
    {
        using (DatabaseEntities db = new DatabaseEntities())
        {
            var customers = db.Customers.ToDictionary(k => k.CustomerID, k => k.CompanyName);
            var list = from l in customers
                       orderby l.Value
                       select new SelectListItem { Value = l.Key.ToString(), Text = l.Value };
            return list.ToList();
        }
    }

But only work for simple pair values (key, value). Can someone help me with another solution or what I'm doing wrong with option 2?

And I hope Microsoft will soon make EF right before pushing us to move from L2S which is already stable and much more mature. I actually using EF4 just because want to use SQL CE, otherwise I stay with L2S.

Maybe is due to Im using SQL CE? instead of SQL or SQL Express? I read other post having same problem using MySQL. EF4 should be database independent, shouldn't it? — Nestor, Apr 24 '11 at 15:28

score 5 · Accepted Answer · answered Apr 24 '11 at 15:51

EF is database independent at upper layers but the part dealing with conversion of linq query to SQL is always database dependent and SqlFunctions are dependent on SQL Server Provider but you are using SQL Server CE provider which is not able to translate functions from SqlFunctions class.

Btw. third option is also not a solution because it will select whole customer table to memory and use linq-to-objects after that. You should use this:

public static IEnumerable<SelectListItem> xxGetCustomerList()
{
    using (DatabaseEntities db = new DatabaseEntities())
    {
        // Linq to entities query
        var query = from l in db.Customers
                    orderby l.CompanyName
                    select new { l.CustomerID, l.CompanyName };

        // Result of linq to entities transformed by linq to objects
        return query.AsEnumerable()
                    .Select(x => new SelectListItem
                        {
                           Value = x.CustomerID.ToString(),
                           Test = x.CompanyName  
                        }).ToList();
    }
}

That makes sense, and I understand CE is meant for simple apps, but still, is a pain to use it this way. Thanks. I will mark your question as a solution and put my last working version. — Nestor, Apr 24 '11 at 16:30

score -1 · Answer 2 · answered May 05 '11 at 07:20

Here is a simplified version I'm using now (specific for SQL CE):

    public static IEnumerable<SelectListItem> GetBlogCategoryList()
    {
        using (SiteDataContext db = new SiteDataContext())
        {
            var list = from l in db.BlogCategories.AsEnumerable()
                       orderby l.CategoryName
                       select new SelectListItem { Value = l.CategoryID.ToString(), Text = l.CategoryName };

            return list.ToList();
        }
    }

Note the db.BlogCategories.AsEnumerable() part

LINQ to Entities StringConvert(double)' cannot be translated to convert int to string

2 Answers2

Linked