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

Question

Using MVC3 VS2010 and SQL Server 2008 Express I am trying to filter based on two SQL Server tables and display the result. One table is clients table and the other is agent. They have in common ClientAgentID in the clients table and ID in the Agents table. An agent logs and should be able to see the clients assigned to the agent. If you have any ideas on the best way to do this please help me. So far I am trying to filter in the clients controller and here is what I have but the message is I am getting is in the title.

 public ActionResult Index()
    {
        //This displays all the clients not filtered by the Agent ID number
        //var clientItems = db.MVCInternetApplicationPkg;
        //return View(clientItems.ToList());

        //Trying to filter by the agent name given in the login page then finding 
        //the agent ID

        var getAgentID = from a in db.AgentsPkg
                            where a.AgentLogin == User.Identity.Name
                            select a.ID;

        var clientItems = from r in db.MVCInternetApplicationPkg
                          where Convert.ToString(r.ClientAgentID)
                                == Convert.ToString(getAgentID)
                          select r;
        //THIS IS THE LINE OF CODE THAT SHOWS THE ERROR MESSAGE
        return View(clientItems.ToList());
    }

This is my first MVC project after the Music Store so am willing to learn and accept any help or advice. Cheers

Here is the solution that I used in the end. Any feed back on if this is a good approach would be appreciated

 public ActionResult Index()
    {

        var innerJoint = from agents in db.AgentsPkg where agents.AgentLogin == User.Identity.Name
                         join clients in db.MVCInternetApplicationPkg on agents.ID equals clients.ClientAgentID
                         select clients;

        return View(innerJoint.ToList());
    }

possible duplicate of [LINQ to Entities does not recognize the method 'Int32](http://stackoverflow.com/questions/3316615/linq-to-entities-does-not-recognize-the-method-int32) — JP Alioto, Aug 22 '12 at 05:01
Is there a foreign key defined between AgentsPkg and MVCInternetApplicationPkg? — Merenzo, Aug 22 '12 at 06:26
@Merenzo There is a foreign Key defined in the SQL Data base but no where in the MVC does it reference a foreign key — WillNZ, Aug 22 '12 at 21:33
@WillNZ From your description, I would expect an "AgentsPkg" navigation property to have been generated in your Entity Model for your "MVCInternetApplicationPkg" class, which will allow you to get your list with just a one-liner (almost :) — Merenzo, Aug 23 '12 at 01:58
@WillNZ: Your solution looks like it should work, but in general I would take advantage of navigation properties (automatically generated for foreign key relationships), where they exist, to simplify the query. Does your "MVCInternetApplicationPkg" entitiy have a "AgentsPkg" property? — Merenzo, Aug 23 '12 at 23:49
@Merenzo Im sorry but I do not fully understand your question. I am fully new to this. I have a DAL folder with a Context Class and here is my packages. `public DbSet BritanniaInternetApplicationPkg { get; set; } public DbSet AgentsPkg { get; set; }` — WillNZ, Aug 24 '12 at 04:07
@WillNZ: does your `Clients` class have a property similar to `public virtual Agents Agents { get; set; }` ? This would be your navigation property for the Client -> Agent FK. — Merenzo, Aug 24 '12 at 05:23
Here is the clients class `public class Clients { public virtual int ID { get; set; } public virtual string ClientFirstNames { get; set; } public virtual string ClientLastName { get; set; } public virtual string ClientGreetingName { get; set; } public virtual DateTime ClientDateOfBirth { get; set; } public virtual int ClientAgentID { get; set; } }` In the database the ClientAgentID is the FK for ID in the agent table — WillNZ, Aug 26 '12 at 21:38

score 3 · Answer 1 · answered Aug 22 '12 at 05:18

3

you do not want to use the Convert in your linq statement!!!!!!

 string clientagentid=Convert.ToString(r.ClientAgentID);
    string getagentid= Convert.ToString(getAgentID);

var clientItems = (from r in db.MVCInternetApplicationPkg
                          where clientagentid==getagentid
                          select r).ToList();
 return View(clientItems);

answered Aug 22 '12 at 05:18

COLD TOLD

13,513
3
35
52

Thanks for this example, I will give this a try and let you know how I get on. – WillNZ Aug 22 '12 at 21:35

Merenzo · Accepted Answer · 2013-09-01T01:13:07.280

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());

Thanks for this example, I will give this a try and let you know how I get on. — WillNZ, Aug 22 '12 at 21:36
Reason for marking this as answer is due to the support from @Merenzo and his reasoning in the answer. If you answer with code but no explanation how is anyone going to learn? — WillNZ, Aug 28 '12 at 23:34

score 2 · Answer 3 · answered Apr 29 '14 at 22:41

The answer is to use SqlFunctions.StringConvert (use 'decimal' or 'double' but 'int' won't work) see example

using System.Data.Objects.SqlClient ;

var clientItems = from r in db.MVCInternetApplicationPkg
                      where SqlFunctions.StringConvert((double ), r.ClientAgentID)
                            == SqlFunctions.StringConvert((decimal) , getAgentID)
                      select r;

see http://msdn.microsoft.com/en-us/library/dd466166.aspx for more info.

score 1 · Answer 4 · answered Aug 22 '12 at 05:31

What is going on is the Linq provider (Linq to Entities) is trying to convert your query to SQL, and there is no mapping for Convert. These errors are sometimes hard to decipher, but the clue is in the "String.ToString()" line. Also realize that because of deferred execution, the error won't show up until the clientItems is iterated, in your case with the call to toList() in return View(clientItems.ToList());

score 0 · Answer 5 · edited May 23 '17 at 11:44

0

LINQ cannot map Convert.ToInt32() into equivalant T-SQL, so it throws exception. As COLD TOLD said you have to convert the value and then use the converted value in the query.

please check : Why do LINQ to Entities does not recognize certain Methods?

edited May 23 '17 at 11:44

Community

1
1

answered Aug 22 '12 at 05:36

Krishnakumar

266
2
7

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

5 Answers5