[转] LINQ Method cannot be translated into a store expression.

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

This is a shortcomming of LINQ to Entities, and indeed the ADO.NET Entity Framework at the current release! The reason for a similar exception message is that in queries on the Entity Framework, custom methods and extensions methods cannot be translated into store expressions.

There are workarounds to still enjoy LINQ to Entities and as an example please read the following:

My example uses the AdventureWorksDB database which can be downloaded from http://www.codeplex.com. I’ve generated the Entity Data Model from the database (in case you wish to replicate this behavior). The scenario is the following: I have a TextBox and a Button on a form and I want to print the name of the Contact by entering the ContactID into the TextBox.

1.<div>

2.    <asp:Label ID="lblContactID" runat="server"

3.               AssociatedControlID="txtContactID">ContactID:</asp:Label>

4.    <asp:TextBox ID="txtContactID" runat="server"></asp:TextBox>

5.    <asp:Button ID="btn" runat="server" Text="Search" OnClick="btn_Click" />

6.   

7.    <asp:Label ID="lblContactName" runat="server"></asp:Label>

8.</div>

The obvious thing to do is to apply a Where clause on an EntityCollection and filtering everything on the ContactID given in the txtContactID TextBox. If you use Convert.ToInt32(“ID”) in the Where clause the upper Exception is thrown.

01.protected void btn_Click(object sender, EventArgs e)

02.{

03.    AdventureWorksEntities entities = new AdventureWorksEntities();       

04.    Contact contact = entities.Contact

05.            .Where(c => c.ContactID == Convert.ToInt32(txtContactID.Text)).FirstOrDefault();

06.    if (contact != null)

07.        lblContactName.Text = contact.FirstName + " " + contact.LastName;

08.    else lblContactName.Text = "Contact not found!";

09.}

The workaround to this is to declare a variable of type int, which contains the converted value from the TextBox and use this variable in the Where query.

01.protected void btn_Click(object sender, EventArgs e)

02.{

03.    AdventureWorksEntities entities = new AdventureWorksEntities();

04.    int ContactID = Convert.ToInt32(txtContactID.Text);

05.    Contact contact = entities.Contact

06.                     .Where(c => c.ContactID == ContactID).FirstOrDefault();

07.    if (contact != null)

08.        lblContactName.Text = contact.FirstName + " " + contact.LastName;

09.    else lblContactName.Text = "Contact not found!";

10.}

Because this feature is available in normal LINQ to SQL I hope the ADO.NET team will do some improvement on this in the next major release of the Entity Framework.