LINQ Method cannot be translated into a store expression.

by Arnold Matusz 17 11 2008

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.

<div>
    <asp:Label ID="lblContactID" runat="server" 
               AssociatedControlID="txtContactID">ContactID:</asp:Label>
    <asp:TextBox ID="txtContactID" runat="server"></asp:TextBox>
    <asp:Button ID="btn" runat="server" Text="Search" OnClick="btn_Click" />
    <br />
    <asp:Label ID="lblContactName" runat="server"></asp:Label>
</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.

protected void btn_Click(object sender, EventArgs e)
{
    AdventureWorksEntities entities = new AdventureWorksEntities();        
    Contact contact = entities.Contact
            .Where(c => c.ContactID == Convert.ToInt32(txtContactID.Text)).FirstOrDefault();
    if (contact != null)
        lblContactName.Text = contact.FirstName + " " + contact.LastName;
    else lblContactName.Text = "Contact not found!";
}

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.

protected void btn_Click(object sender, EventArgs e)
{
    AdventureWorksEntities entities = new AdventureWorksEntities();
    int ContactID = Convert.ToInt32(txtContactID.Text);
    Contact contact = entities.Contact
                     .Where(c => c.ContactID == ContactID).FirstOrDefault();
    if (contact != null)
        lblContactName.Text = contact.FirstName + " " + contact.LastName;
    else lblContactName.Text = "Contact not found!";
}

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.

DZone it Digg it Submit to StumbleUpon Submit to Technorati Submit to reddit.com Submit to del.icio.us Submit to NewsVine Submit to Furl Submit to BlinkList

Tags: , ,

LINQ | C# | ASP.NET | Programming

Comments

11/18/2008 4:53:21 AM #

trackback

Trackback from DotNetKicks.com

LINQ Method cannot be translated into a store expression.

DotNetKicks.com |

2/23/2009 9:28:57 AM #

Mike Borozdin

Hi,

I've just run into a similar exception. I hope they'll fix it soon!

Mike Borozdin Russia |

5/29/2009 7:30:17 PM #

Yusuke

The example you provided has a workaround as you showed. However, supporse you want to sort records by a varchar column representing number;

"101"
"110"
"1501"
"2005"
"202"

OrderBy(r => r.room_number.PadLeft(4, '0'))

or

OrderBy(r => Int32.parse(r.room_number))

Both expressions are not supported.

Yusuke Japan |

5/29/2009 9:38:34 PM #

Arnold Matusz

Hi Yusuke

I agree with your point, and to be frank I've also run into these problems.
What I'm happy about is that according to some blog posts from the ADO.NET Team ... the new Entity Framework v2 "will have more Store Expressions" implemented.

Thanks for your comment.

Arnold Matusz Romania |

1/14/2010 7:20:03 AM #

pingback

Pingback from dexterlegaspi.com

.debug » LINQ To Entity is the Work of the Devil

dexterlegaspi.com |