How to create a custom store expression for my linq queries

asp.net-mvc c# entity-framework-6 linq sql-server

Question

Let me first explain what I'm trying to accomplish.

I'm working with a C# ASP.NET MVC 5 project using Entity Framework to communicate with a SQL Server database. Most of the queries utilizes linq for its queries. In various places on the frontend site I'm displaying lists of records and need to provide the means of searching these records via a search bar. The initial idea right now is to allow the user to enter a search phrase with keywords being separated by spaces, and those keywords are used to match any combination of fields in the records of a table.

For example, say my search is "John Doe" against a user table. Consider these being the records in this table:

uFirstName    uLastName
----------    ----------
Johnny        Doe
John          Doe
Jane          Doe

The first two records should be returned.

Here's an example method I would call to return the results I expect:

public static List<UserModel> GetUserList(string terms)
{
    using (DBConnection dbcontext = new DBConnection())
    {
        var termlist = (terms == "") ? new List<string>() : terms.Split(' ').ToList();
        var linqList = (from u in dbcontext.Users
                        where 
                        (
                            (terms == "") ||
                            (termlist.Any(_s => u.uLastName.Contains(_s))) ||
                            (termlist.Any(_s => u.uFirstName.Contains(_s)))
                        )
                        select new { u.uLastName, u.uFirstName });
        return linqList.ToList().ConvertAll<UserModel> ( u => new UserModel { LastName = u.uLastName, FirstName = u.uFirstName } );
    }
}

In my project I'm utilizing this search bar in various places being used to search against a variety of tables that obviously have different fields. What I would like to do is create a helper method that allows me to pass in the "terms" string and have it matched against a list of field values within the linq statement generically. Here's an example pseudo method that shows what I would like to change the above method to:

public static List<UserModel> GetUserList(string terms)
{
    using (DBConnection dbcontext = new DBConnection())
    {
        var linqList = (from u in dbcontext.Users
                        where SearchTermMatch(terms, new List<string>() { u.uLastName, u.uFirstName }) == true
                        select new { u.uLastName, u.uFirstName });
        return linqList.ToList().ConvertAll<UserModel>(u => new UserModel { LastName = u.uLastName, FirstName = u.uFirstName });
    }
}

And this is what the helper method would look like:

public static bool SearchTermMatch(string terms, List<string> fieldvalues)
{
    if (terms == "") return true;
    else
    {
        var termlist = terms.Split(' ').ToList();
        var foundlist = new List<bool>();
        foreach (string value in fieldvalues)
            foundlist.Add(termlist.Any(s => value.Contains(s)));
        return foundlist.Any(f => f == true);
    }
}

Even though this compiles fine, at runtime it produces the following error:

LINQ to Entities does not recognize the method 'Boolean SearchTermMatch(System.String, System.Collections.Generic.List`1[System.String])' method, and this method cannot be translated into a store expression.

From all my searching on how to get this working, it's clear I need to utilize Expressions, but I can't for the life of me understand how those work. What I do understand is that Entity Framework wants to convert the linq statements into a query that SQL can understand, and my helper method isn't equipped to do so.

Ultimately what I want to accomplish is to build a helper method that I can later expand upon with more advanced searching techniques. I figure if I start simple with a search on all relevant fields based on a keyword split, I can later add more complexity that I would only have to do to this helper method and all my search bars will benefit from those advancements.

So I guess what I'm looking for is your help on how I can create this helper method that I can use throughout my various linq statements in my project.

1
3
6/26/2015 7:04:10 AM

Accepted Answer

Ok, I found a solution to my question. It's not completely ideal, but it gets the job done.

Let me first give reference to the sources I'm using for my solution. I first referred to this answer as the starting point: https://stackoverflow.com/a/27993416/4566281

This answer referred to a source that I ended up using in my project. If you're using Visual Studio, you can find the package in NuGet, just search for "neinlinq", or get it from this GitHub repository: https://github.com/axelheer/nein-linq

The only reason I don't consider this my ideal solution is that I was hoping to stick completely to the libraries in .NET / MVC. There's nothing wrong with using a 3rd party library, and in this case, it got the job done for me. But I was hoping to accomplish this as native as possible, and within reason.

So on to my code solution, as I hope this will help someone else in some capacity.

My "helper" function(s) ended up being this (don't forget to include "using NeinLinq;")

    [InjectLambda]
    public static bool SearchTermMatch(List<string> termlist, List<string> fieldvalues)
    {
        throw new NotImplementedException();
    }
    public static Expression<Func<List<string>, List<string>, bool>> SearchTermMatch()
    {
        return (t,f) => 
        (
            (t.Count() == 0) ||
            (t.Count(_t => f.Any(_f => _f.Contains(_t)) || _t == "") == t.Count())
        );
    }

And, my linq statement ended up being the following:

    public static List<UserModel> GetUserList(string terms)
    {
        using (DBConnection dbcontext = new DBConnection())
        {
            var termlist = (terms == "") ? new List<string>() : terms.Split(' ').ToList();
            var linqList = (from u in dbcontext.Users
                            where SearchTermMatch(termlist, new List<string>() { u.uLastName, u.uFirstName })
                            select new { u.uLastName, u.uFirstName });
            return linqList.ToList().ConvertAll<UserModel>(u => new UserModel { LastName = u.uLastName, FirstName = u.uFirstName });
        }
    }

I also didn't like that I have to construct the "termlist" before the linq statement in order to make the comparisons I wanted. Ideally I'd like to have the "SearchTermMatch" expression to construct the list through something similar to Split so all I had to do was pass in the string "terms", but I couldn't figure out how to accomplish that in the expression. If someone has an idea on how to do that please let me know. I could then have the flexibility to establish my own set of search rules in the expression instead of having the calling linq statement make the list.

So, to come full circle on how this accomplishes my sitution, I can now repurpose SearchTermMatch for all my search bar scenarios. Take for example this statement:

            var linqList = (from p in Person 
                            join a in Address on p.AddressID equals a.AddressID 
                            select new { p.ContactName, p.EmailAddress, a.Street, a.City, a.State, a.Zipcode });

I can now easily update it to the following to handle my search bar call:

            var termlist = (terms == "") ? new List<string>() : terms.Split(' ').ToList();
            var linqList = (from p in Person 
                            join a in Address on p.AddressID equals a.AddressID
                            where SearchTermMatch(termlist, new List<string>() { p.ContactName, p.EmailAddress, a.Street, a.City, a.State, a.Zipcode })
                            select new { p.ContactName, p.EmailAddress, a.Street, a.City, a.State, a.Zipcode });       
3
5/23/2017 12:07:57 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow