Custom extension method to simplify LINQ to SQL statement

Question

I have a snippet of LINQ code that I need to use in a lot of queries

let catchmentId = Convert.ToInt32(
        phy.PhysicalProperty_binData.Substring(offset + 3, 1) +
        phy.PhysicalProperty_binData.Substring(offset + 2, 1) +
        phy.PhysicalProperty_binData.Substring(offset + 1, 1) +
        phy.PhysicalProperty_binData.Substring(offset, 1))

This translates well to SQL (and is more than performant for my specific needs), but makes my queries look ugly. Is there any way I can make this less verbose? Perhaps an extension method that returns an Expression> ? Note that I don't want to fetch the data to the client side (run it on the server as SQL), which makes things a little more complicated. For example:

let catchmentId = phy.PhysicalProperty_binData.AnExtensionMethodHere<int>(offset)

I tried writing an extension method that does this, but the provider simply ignores that call or throws an exception stating it doesn't know how to translate that call to SQL. Can we somehow extend the SQL generator to do this for us or provide it something in a way that will generate the same SQL?

Thanks in advance!

score 1 · Answer 1 · answered Sep 19 '13 at 17:20

1

You can map custom SQL functions in your Entity model.

http://msdn.microsoft.com/en-us/library/dd456812(v=vs.103).aspx

answered Sep 19 '13 at 17:20

Knaģis

20,827
7
66
80

My bad, I added the EF4 tag by mistake. I have a dbml file (LINQ-to-objects), not an edmx file as is required by that method. – Ani Sep 19 '13 at 17:27

StriplingWarrior · Accepted Answer · 2013-09-19T17:51:46.247

1

The links on this page will show you how you can use custom attributes to tie your extension method to a user-defined function in your database.

Of course, that will mean that you need to create a user-defined function in SQL to do the same logic that your extension method wants to do.

Another option is to have your extension method produce an Expression, and use LINQKit to parse through the query tree, finding the call to .Compile() off of that expression, and inlining that expression in the query.

edited Sep 19 '13 at 17:51

answered Sep 19 '13 at 17:46

StriplingWarrior

151,543
27
246
315

Since the LINQ to SQL generator already understands all the methods I need to use, is there a way to do so without a SQL (or SQL CLR) function? – Ani Sep 19 '13 at 17:49
@ananthonline: Yes, see my update. It takes a little more work, but LINQKit can do most of the heavy lifting. – StriplingWarrior Sep 19 '13 at 17:54
Perfect. I've gone the user-defined function route for now, but will look into AsExpandable some more. – Ani Sep 19 '13 at 18:58

Adam Kewley · Answer 3 · 2013-09-19T17:37:55.513

-1

Wrong language (sorry) here it is in F# if people need.

let catchmentId =  
    // Curry in invariant components.
    let newSubstr end = 
        phy.PhysicalProperty_binData.Substring(offset + end, 1)
    Convert.ToInt32(
        newSubstr 3 +
        newSubstr 2 + 
        newSubstr 1 +
        newSubstr 0)

edited Sep 19 '13 at 17:37

answered Sep 19 '13 at 17:26

Adam Kewley

1,224
7
16

Is that C#? You're probably thinking F#. This is a "let", part of a bigger C# LINQ statement. – Ani Sep 19 '13 at 17:31
Apologies, I saw let and assumed F# – Adam Kewley Sep 19 '13 at 17:33
Most of your verbosity is from calling the PhysicalProperty in your example. You might save many characters just creating an alias: var physProp = phy.PhysicalProperty_binData; – Adam Kewley Sep 19 '13 at 17:39

Custom extension method to simplify LINQ to SQL statement

3 Answers3

Wrong language (sorry) here it is in F# if people need.

Linked