CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entities
WillNZ
WillNZ

Reputation: 765

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

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

Upvotes: 3

Views: 14999

Answers (5)

Harry
Harry

Reputation: 1825

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.

Upvotes: 2

Merenzo
Merenzo

Reputation: 5156

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

Upvotes: 2

Krishnakumar
Krishnakumar

Reputation: 266

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?

Upvotes: 0

Tony Basile
Tony Basile

Reputation: 419

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

Upvotes: 1

COLD TOLD
COLD TOLD

Reputation: 13569

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

Upvotes: 3

Related Questions