Linq to entities and CAST

We use an EDMX to access our database with Entity Framework. Before you comment: we have our reasons not to use code-first. Today we encountered a small problem with linq to entities and validating a varchar field that contains an integer value. Again: we have our reasons. Think about key-value pairs.

An excellent answer of Joel Mueller on stackoverflow.com helped us overcome this problem. In short we had to add a function to our edmx conceptual model. The link above has all te details.

<Function Name="ParseInt" ReturnType="Edm.Int32">
   <Parameter Name="stringvalue" Type="Edm.String"/>
   <DefiningExpression>
      CAST(stringvalue as Edm.Int32)
   </DefiningExpression>
</Function>

The validation works like a charm now. This is what EF generates (see the CAST?):

SELECT [GroupBy1].[A1] AS [C1]
FROM ( SELECT COUNT(1) AS [A1]
        FROM [dbo].[Table] AS [Extent1]
        WHERE ([Extent1].[Key] = @p__linq__0) 
        AND (( CAST( [Extent1].[Value] AS int) < @p__linq__1) 
          OR ( CAST( [Extent1].[Value] AS int) > @p__linq__2))
    )  AS [GroupBy1]

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s