Problem with converting int to string in Linq to entities
With EF v4 you can use SqlFunctions.StringConvert
. There is no overload for int so you need to cast to a double or a decimal. Your code ends up looking like this:
var items = from c in contacts
select new ListItem
{
Value = SqlFunctions.StringConvert((double)c.ContactId).Trim(),
Text = c.Name
};
convert int to string in linq for searching
For future reference, have fixed this by using:
var pop = ctx
.PurchaseOrders
.OrderBy(x => x.PurchaseOrderID)
.ToList()
.Select(x => new SearchItem()
{
id = x.PurchaseOrderID.ToString(),
label = x.SupplierID,
category = "purchaseorder"
});
It's the intermediary list that makes it work.
linq to entities can not convert int to string
There is a SqlFunctions class available that contains many SQL-functions that may be used for LINQ. Try a look at SqlFunctions.StringConvert
Items = Mapper.Map<List<PurchaseOrder>, List<PurchaseOrderViewModel>>(
purchaseOrderRepository
.GetMany(x => SqlFunctions
.StringConvert((double?) x.PurchaseOrderID)
.Contains(text))
.ToList());
Unfortunately there is only a SqlFunctions.StringConvert(double? number)
and no SqlFunctions.StringConvert(int? number)
. But I always convert the integer number to a double and it works as expected.
Edit: You are calling ToList
prior Where
. Therefore that SqlFunctions can only be called in a LINQ to Entity query
any SqlFunctions after a ToList()
is illegal.
Try without your GetMany Method:
purchaseOrderRepository.Set<PurchaseOrder>()
.Where(x => SqlFunctions
.StringConvert((double?) x.PurchaseOrderID)
.Contains(text))
Linq int to string
Linq to Entities does not support as many conversion of functions to server hosted sql.
ToString (on anything) is one of them
Here's the list of supported functions
This has been asked before on Stack overflow specifically asking how to convert an int to a string
how to convert int to string in Linq to entities
The EF provider does not know how to translate Convert.ToInt()
into SQL it can run against the database. Instead of doing the conversion on the server, you can pull the results back and do the conversion using linq to objects:
// the ToList() here causes the query to be executed on the server and
// the results are returned in a list of anonymous objects
var results = (from plan in entities.PA_RTM_CAP_Group
select new
{
Code = plan.cap_group_code,
Name = plan.cap_group_name
}).ToList();
// the conversion can now be done here using Linq to Objects
var vlauesCap = from r in results
select new Business.PartnerProfile.LookUp
{
Id = Convert.ToInt32(r.Code),
Value = r.Name
};
return vlauesCap.ToList();
LINQ to Entities StringConvert(double)' cannot be translated to convert int to string
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();
}
}
Related Topics
C# Convert Int to String With Padding Zeros
What's the Strangest Corner Case You'Ve Seen in C# or .Net
Get Url Parameters from a String in .Net
How to Convert an Integer into Its Verbal Representation
How to String.Format a Timespan Object With a Custom Format in .Net
How to Get the Connection String from a Database
My Algorithm to Calculate Position of Smartphone - Gps and Sensors
Entity Framework 5 Updating a Record
How to Remove All Namespaces from Xml With C#
Error: "An Object Reference Is Required For the Non-Static Field, Method or Property..."
Execute Powershell Script from C# With Commandline Arguments