How to format string in linq expression?

Question

Given the following code

IQueryable<string> customers = from Customers in db.Customers where Customers.CstCompanyName.Contains(prefixText) && Customers.CstInactive == false select Customers.CstCompanyName + " (Phone: " + Convert.ToInt64(Customers.CstPhone).ToString("###-###-#### ####") + ")"; 

This is a challenge to my essence. I am returning a phone number from the database. I am trying to format it in a given format string. Unfortunately, when I run this, I get the following error:

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

So my question is: how to return this database object to a formatted string?

Thanks!

Answer 1

I would execute the query in the database, but the formatting is local:

 var customers = db.Customers .Where(c => c.CstCompanyName.Contains(prefixText)) .Select(c => new { c.CstCompanyName, c.CstPhone }) .AsEnumerable() // Switch to in-process .Select(c => c.CstCompanyName + " (Phone: " + Convert.ToInt64(Customers.CstPhone) .ToString("###-###-#### ####") + ")"); 

Answer 2

I’m not sure if this is the right way, but I wanted to use what I’m comfortable with. If you use the entity framework and load it, you can put String.Format in the LINQ query.

 Dim ds as Entity = New Entity() ds.FinancialInstitutionTransactions.OrderByDescending(Function(c) c.TransID).Load() Dim openLoad = From tr In ds.FinancialInstitutionTransactions.Local Select TransDate = String.Format("{0:d}", tr.Date), tr.Number, tr.ToFrom, Debit = If(tr.Debit = 0, " ".ToString(), String.Format("{0:N}", tr.Debit).ToString()), Credit = If(tr.Credit = 0, " ".ToString(), String.Format("{0:N}", tr.Credit).ToString()) 

You need to reference System.Data.Entity to download, but then it's easy to format the results the way you want.

Hope this helps someone. I know this is in vb.net, but it would not be difficult to convert to C #.