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

c# entity-framework linq linq-to-entities


I'm attempting to filter based on two SQL Server tables and show the results using MVC3 VS2010 and SQL Server 2008 Express. The client table is one, while the agent table is the other. In the clients database, they share ClientAgentID, and in the agents table, they share ID. When an agent signs in, they should be able to view the customers they have been allocated. Please assist me if you have any suggestions on how to go about doing this. The message I am now receiving is in the title, however I am currently attempting to filter in the client controller, so this is what I have.

 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;
        return View(clientItems.ToList());

Since this is my first MVC project since the Music Store, I'm eager to learn and am open to any guidance. Cheers

Here is the final answer I came up with. Any opinions on whether this is a wise strategy would be much 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());
8/23/2012 9:09:01 PM

Accepted Answer

1. The error's cause:

As other people have said, it results from the usage ofConvert.ToString() inside yourwhere clause that Linq is unable to translate into SQL. If you only remove the two from your initial query, I would anticipate it working.Convert.ToString() functions.

2. "...optimal method for doing this"

Well, there's a better method.

The Navigational Features shortcut in Entity Framework makes it simple to move between related entities. These need to be produced for you in your EDMX if your methodology is "Database First." There is a nice article here detailing how to put this up if your methodology is "Code First."

In any case, I'd anticipate yourClient a navigation attribute for classAgent (OrderDetail's similar to this)Order in the MvcMusicStore example you reference, property):

public virtual Agents Agent { get; set; }

When that happens, your method becomes quite straightforward (i.e., comparable to many of the controller methods in MvcMusicStore).Joins Alternatively many statements must be made:

var clients = db.MVCInternetApplicationPkg.Where(c => c.Agent.AgentLogin == User.Identity.Name); 
return View(clients.ToList()); 
9/1/2013 1:13:07 AM

Popular Answer

In your Linq statement, you must not utilize the Convert!

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

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow