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;

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 ?

Please don't put answers inside the question. They are meant to be posted below, as an answer. — user229044
– user229044 ♦, Commented Jan 4, 2014 at 14:07

Erti-Chris Eelmaa · Accepted Answer · 2014-01-04 12:17:33Z

2

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.

edited Jan 4, 2014 at 12:17

answered Jan 4, 2014 at 11:49

Erti-Chris Eelmaa

26.6k6 gold badges65 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KarelG Over a year ago

i got the Invoke() method from a blog post. ( documentation ) Your solution has returned with no compiling errors, but with has no supported translation to SQL at the template (going to edit post).

KarelG Over a year ago

wow ! you gave me a super assist ! (if you know football). I had to adapt the code a bit, but now it works as expected. Thank you !

Vikram Sharma · Accepted Answer · 2014-01-04 11:46:00Z

0

Can you please confirm whether the following LINQ is correct

var q = from Cars_car c in _db.Cars_cars
    join Cars_equipment e in _db.Cars_equipments on c.equipmentId equals e.id

I can't understand why you are using alias to alias here. According to me, this snippet should be like

var q = from c in _db.Cars_cars
        join e in _db.Cars_equipments on c.equipmentId equals e.id

May be this is not the reason for the error, but I wanted to confirm if my understanding is correct.

answered Jan 4, 2014 at 11:46

Vikram Sharma

3871 gold badge8 silver badges23 bronze badges

1 Comment

KarelG Over a year ago

it's same. It doesn't matter, but i am doing this way to improve code readability.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related