Creating helper method that can be used in LINQ (EF)

Scenario:

I Have a table FooTable when column Foo is varchar(8) NOT NULL and the info in this column is like:

Foo
-----------
13940-00
13940-01
13940-02
13941-00
13941-01

Where the numeric part after the hyphen (-) always have two digits.

Problem:

I'm using Ado.net Entity Framework, and I created a class with 2 static methods to help get first and second part of the number:

public class HelperFoo
{
    public static string Prefix(string value) { /* code here */ }
    public static string Suffix(string value) { /* code here */ }
}

So now I can do something like this:

context.FooTable.Where(w => HelperFoo.Prefix(w.Foo) == "13940");

But, as you probably already know, this command line throws a NotSupportedException. It's because LINQ don't recognize HelperFoo.Prefix, so it can't convert the expression in SQL.

I can write a block of code in SQL that do the same that my methods of HelperFoo so I can create the SQL to my methos.

Question

Can I create something (class, method, or other) that makes LINQ knows my method when I executed the LINQ code?

EDITED

PS: I need something generic that works like a method or SQL function because I need to get this Prefix and Suffix in scenarios like Select, OrderBy, GroupBy and many others.

Answers


You can use .StartsWith to check the prefix:

string prefix = "13940";
var result = context.FooTable.Where(w => w.Foo.StartsWith(prefix + "-"));

and .EndsWith to check the suffix:

string suffix = "02";
var result = context.FooTable.Where(w => w.Foo.EndsWith("-" + suffix));

You could try creating your own IQueryable filters for the FooTable, a bit like this:

public static class Filters
{
    public static IQueryable<FooTable> WithPrefix(this IQueryable<FooTable> item, string prefix)
    {
        return item.Where(i => i.Foo.StartsWith(prefix));
        // note that this should be the same code you have in the 
        // Prefix() method inside HelperFoo...
    }
}

Which you can use like this:

context.FooTable.WithPrefix("13940");

UPDATE: Sadly the second option here does not work:

Another option would be to have the helper methods not return a value but a Predicate<> for FooTable:

public class HelperFoo
{
    public static Func<FooTable, bool> Prefix(string value)
    {
        return (i) => i.Foo.Substring(0, 5) == value;
    }
    public static Func<FooTable, bool> Suffix(string value) { /* code here */ }
}

And use it like this:

context.FooTable.Where(HelperFoo.Prefix("13940"));

Caveat: I'm not entirely sure the second method would not give you the same problem though.


With the the plethera of awnsers and you stating it needs to be more generic and you need the prefix and suffix avaliable to the Select, OrderBy, & GroupBy keywords, you should have the prefix and suffix in two different fields.

Foo Table
----------
Prefix | Suffix
----------------
10245  | 05

With that, you can query them individually to get what you want:

var resultSet = Db.Foo.Where(x => x.Suffix == "05").OrderBy(x => x.Prefix);

With this you can easily add a read-only property to get a formatted value:

public [partial] class Foo {
    //Your other code

    public string FormattedValue {
        get { return Prefix + "-" + Suffix; }
    }
}

You could create custom getters in your Foo class:

public class Foo
{
    //your code
    [NotMapped]
    public string Prefix { get { return /*whatever*/; }
}

I think this should work.


You cannot filter your database selection based on a custom function - as you say, it cannot convert this to SQL.

For this specific problem, I could propose you use the StartsWith function, which does work on SQL server

context.FooTable.Where(w => w.Foo.StartsWith("13940"));

Use Microsoft.Linq.Translations.

It would look something like this:

partial class FooTable 
{
    private static readonly CompiledExpression<FooTable,string> prefixExpression
            = DefaultTranslationOf<FooTable>.Property(e => e.Prefix).Is(e => e.Foo.Substring(0, 5));

    public string Prefix
    {
        get { return prefixExpression.Evaluate(this); }
    }
}

And queried like:

context.FooTable.Where(w => w.Prefix == "13940").WithTranslations();

Nuget gallery page

Documentation

EDIT: This solution works in Select, GroupBy, OrderBy.


Need Your Help

Combine the results of two SQL queries

php mysql sql phpmyadmin

How can I combine the results of these two sql queries either in SQL or PHP .. they're all involving joins .. I would like to combine them both and sort them by orderid .. how can I do that ?