Linq to Entities Does Not Recognize the Method 'System.String Tostring()' Method, and This Method Cannot Be Translated into a Store Expression

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

Just save the string to a temp variable and then use that in your expression:

var strItem = item.Key.ToString();

IQueryable<entity> pages = from p in context.pages
                           where  p.Serial == strItem
                           select p;

The problem arises because ToString() isn't really executed, it is turned into a MethodGroup and then parsed and translated to SQL. Since there is no ToString() equivalent, the expression fails.

Note:

Make sure you also check out Alex's answer regarding the SqlFunctions helper class that was added later. In many cases it can eliminate the need for the temporary variable.

Why LINQ to Entities does not recognize the method 'System.String ToString()?

That can't be converted to SQL. I guess, in theory, it could, but isn't implemented.

You just need to perform your projection after you have your results:

var pricingSecurityID = (from m in dbContext.Reporting_DailyNAV_Pricing
                                     select m.PricingSecurityID).AsEnumerable()
    .Select(x => new SelectListItem{ Text = x.ToString(), Value = x.ToString() });

LINQ to Entities does not recognize the method 'System.String ToString()' method in MVC 4

You got this error because Entity Framework does not know how to execute .ToString() method in sql. So you should load the data by using ToList and then translate into SelectListItem as:

var query = dba.blob.ToList().Select(c => new SelectListItem
    {
    Value = c.id.ToString(),
    Text = c.name_company,
    Selected = c.id.Equals(3)
});

Edit: To make it more clear, Entity framework converts your use of query operators such as Select, Where ETC into an sql query to load the data. If you call a method like ToString(), which Entity Framework does not have an equivalent in sql, it will complain. SO the idea is to defer the use of such functions post data load. ToList, ToArray ETC force execution of the query thus loading of data. Once the data is loaded, any further operation (such as Select, Where ETC) is performed using Linq to Objects, on the data already in memory.

LINQ to Entities does not recognize convert to string

well, one typical solution would be to fetch the data from the database as-it-is and then translate it on your side:

    var allProviders = _db.CareProviders.AsQueryable();
    var resultItems = allProviders.Select(a => new
    {
        ProviderId = a.ProviderId, // fetch it as it is
        CareServiceType = a.CareServiceType,
        CareServiceName = a.CareServiceName,
        Email = a.Email
    })
    // download results from DB and execute
    // rest of the query on the application side
    .AsEnumerable()
    // now convert
    .Select(a => new
    {
        ProviderId = a.ProviderId.ToString(), //convert to string
        CareServiceType = a.CareServiceType,
        CareServiceName = a.CareServiceName,
        Email = a.Email
    });

However, sometimes this is somewhat an overkill just to convert to string..

There are some conversion methods that actually usually are understood by LINQ, but that varies dependig on your linq provider. You can experiment and try one of:

... = SqlFunctions.StringConvert(a.ProviderId)
... = Convert.ToString(a.ProviderId)
... = a.ProviderId + ""

Error: LINQ to Entities does not recognize the method 'System.String ToString(System.Object)' method occurs while string conversion

Your problem is related to the ToShortDateString call you have. One way to get rid of it would be to inspect the searchText for a potential date string before creating the expression and work with the date instead.

Expression<Func<Contact, bool>> cntExpression;

var searchDate = default(DateTime);
if (DateTime.TryParse(searchText.Trim(), out searchDate)) 
{
    cntExpression = p => p.DOB.HasValue && p.DOB == searchDate;
} 
else 
{
    cntExpression = p => p.LastName.ToLower().Trim().Contains(searchedText) ||
            p.FirstName.ToLower().Trim().Contains(searchedText) ||
            p.MiddleName.ToLower().Trim().Contains(searchedText) ||
            p.NickName.ToLower().Trim().Contains(searchedText);
}

LINQ to Entities - method cannot be translated into a store expression

You need to use "LINQ to Objects" to perform string.Format or interpolated strings by using AsEnumerable() or ToList() before using Select:

var chwWorker = (from c in db.PatientContacts
                where c.UserName == userFN &&
                     (c.PCP_Status == "Appointment Made" || c.PCP_Status_AWC == "Appointment Made"
                      || c.PCP_Status_AWDV == "Appointment Made" ||
                     (c.PCP_Status == "RX for Mamogram" && c.Status == "Appointment Made")) 
                orderby c.PCP_Status_Date descending select c) 
                .AsEnumerable() // or 'ToList()'
                .Select(c => new 
                {
                     Id = c.MemberID,
                     Name = c.PatientFirstName + " " + c.PatientLastName, 
                     PCP_Appt = $"{c.PCP_Status_Date:d}",
                     Mammogram_Appt = $"{c.StatusDate:d}",
                     Phone = GetPhone(c.MemberID)
                });

Note that string.Format method is not recognized by LINQ to Entities to translate it as an SQL command, hence query result materialization to memory is necessary.

NB: You can use SqlFunctions.StringConvert if you still want LINQ to Entities query before using Any() method, but not all SQL providers able to translate it into SQL statement.

Related issue:

LINQ to Entities does not recognize the method 'System.String Format

LINQ to Entities does not recognize the method 'System.String ToString(Int32)'

You cannot use these conversion functions in a LINQ to Entities statement, they cannot be translated to SQL, you need to do the conversions in memory. But I don't think you need to do that at all.

If you were just using the resultMap to get your resultList, filtered by Results of which the Id is present in mapResult, do the following:

var resultList = db.Result_DE
    .Where(r => r.IsActive == "1" && mapResult.Any(mr => mr.ResultDE == r.ID));
    .ToList();

If mapResult is an in-memory collection, instead of an IQueryable that is attached to the db context, you need to do the following:

var resultIds = mapResult.Select(mr => mr.ResultDE).ToList();
var resultList = db.Result_DE
    .Where(r => r.IsActive == "1" && resultIds.Contains(r.ID));
    .ToList();

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

1. Reason for the error:

As others have stated, it's due to the use of Convert.ToString() within your where clause, which Linq cannot convert into SQL. I would expect your original query to work just by removing the two Convert.ToString() functions.

2. "....best way to do this":

Well, a better way.... :)

In Entity Framework, the easy way to navigate between related entities is via Navigation Properties. If your approach is "Database First", these should be generated for you in your EDMX. If your approach is "Code First", there's a good post here describing how to set this up.

Either way, I'd expect your Client class to have a navigation property to Agent (i.e. similar to OrderDetail's Order property in the MvcMusicStore sample you mention):

public virtual Agents Agent { get; set; }

Then your method becomes very simple (i.e. similar to many of the controller methods in MvcMusicStore) ...no Joins or multiple statements required:

var clients = db.MVCInternetApplicationPkg.Where(c => c.Agent.AgentLogin == User.Identity.Name); 
return View(clients.ToList()); 

LINQ to Entities does not recognize the method and this method cannot be translated into a store expression

Your code is basically trying to find the Convert.ToString() method in the DB and understandably failing.

You can get around it, for example by making sure the query executes before the select, e.g.

var ps = dbContext.SplLedgers.Select(p => new  
    { 
         Name = p.Name, 
         VoucherType = p.VoucherType
    }).ToList().Select(p => new SplLedgerModel( 
    { 
         Name = p.Name, 
         VoucherType = Convert.ToString(((JIMS.VoucherTypes)p.VoucherType)) 
    }); 

---

Related Topics