linq to sql custom method using Expression<Func<...>>

Question

Maybe I am making myself somewhat difficult in this issue. The goal is to use an object (here SearchForm) to analyze the database records and returns if the record matches the search entry.

Since my website allows an advanced search form, the SearchForm object contains a lot properties (5 input fields, some dropdown lists, and a lot checkboxes). I am using

• C# NET 4.0 (not higher due of web host)
• LINQ-to-SQL
• ASP.NET MVC 4

Here is my snippet so far:

public static IEnumerable<CarDetails> SearchByForm(SearchForm form) {
    // get db instance
    _db = new MyDBDataContext();
    var q = from Cars_car c in _db.Cars_cars
    join Cars_equipment e in _db.Cars_equipments on c.equipmentId equals e.id
    where ValidData.Invoke(c, form)
    select new CarDetails {
         // filling CarDetails data
    };
    return q;
}

And the ValidData method is an Expression<Func<...> type.

private static Expression<Func<Cars_car, SearchForm, bool>> ValidData = (c, f) => MatchesSearchData(f, c);

where MatchesSearchData is the method behind to validate each records

private static bool MatchesSearchData(SearchForm f, Cars_car c) {
    // long check if true or false and returns it
}

But there is an error, the overloaded method has some invalid arguments at ValidData.Invoke(c, form).

Am I making myself difficult, or is this the good way to accomplish my results? If so, can someone provide me a solution to the above error note? If not, some guidance to use other way to solve this?

There is a way to do this with stored procedures, true, but I rather prefer to do this at this layer.

Used sources;

• article about custom LINQ to SQL method
• blog post calling custom methods
• other blog post about same subject

EDIT 1

An answer from user Erti-Chris Eelmaa has solved the compiler error by using Compile()-method.

    where ValidData.Compile().Invoke(c, form)

But when running, it redirects me to the error page. Apparently, i am getting method has no supported translation to sql error.

In Controllers,

public ActionResult Results(SearchForm form) {
    ViewBag.cars = Cars_car.SearchByForm(form);
    return View("Results");
}

and on template (just quick testing)

@foreach (var car in ViewBag.cars)** {
<p>results : car.Make !</p>
}

** Here, an method Boolean Invoke(MVCproject2.Models.Cars_car, MVCproject2.Models.SearchForm) has no supported translation to sql error got thrown. That's weird, because that Expression method was added to provide a custom method to LINQ to SQL.

Or am i wrong ?

Answer 1

If you want method to be part of the SQL query, you need to create your method on SQL server, and then call it. Like so:

CREATE FUNCTION ReverseCustName(@string varchar(100))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @custName varchar(100)
    -- Implementation left as exercise for users.
    RETURN @custName
END

// and C# part

[Function(Name = "dbo.ReverseCustName", IsComposable = true)]
[return: Parameter(DbType = "VarChar(100)")]
public string ReverseCustName([Parameter(Name = "string",
    DbType = "VarChar(100)")] string @string)
{
    return ((string)(this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        @string).ReturnValue));
}

You can now use ReverseCustName(string) in your "SQL" query.

Source: http://msdn.microsoft.com/en-us/library/vstudio/bb386973(v=vs.100).aspx

Alternatively you can download everything in to your C# client and execute Where there.

public static IEnumerable<CarDetails> SearchByForm(SearchForm form) {
    Func<RetailCar_car, SearchForm> compiledLambda = ValidData.Compile();
    // get db instance
    _db = new MyDBDataContext();
    var q = (from Cars_car c in _db.Cars_cars
    join Cars_equipment e in _db.Cars_equipments on c.equipmentId equals e.id)
    .ToList()
    .Where(x => compiledLambda(c, form))
    .Select(x => new CarDetails {});

    return q;
}

Let's get this straight:when you are using DbContext, you're using IQueryable. The class is meant to generate SQL queries. If you combine your C# method with IQueryable, LINQSQL doesn't know how to translate it to SQL query.

Those two solutions correspond to: either create new SQL method in SQL server, or download everything to C# side using ToList(), which means that IQueryable will be switched to IEnumerable.