Problems implementing a search filter using LINQ

Question

I'm trying to implement a search filter using LINQ in my ASP.Net application.

However I'm running into problems. Here is my code:

var result = (from x in db.ContactSet
    select new Models.Contact
    {
        AccountId = x.AccountId,
        FirstName = x.FirstName,
        LastName = x.LastName,
        FullName = x.FullName,
        JobTitle = x.JobTitle,
        ParentCustomerId = x.ParentCustomerId,
        EMailAddress1 = x.EMailAddress1,
        Telephone1 = x.Telephone1,
        MobilePhone = x.MobilePhone,
        Fax = x.Fax,
        GenderCode = x.GenderCode,
        BirthDate = x.BirthDate
    }); //? Gets all rows

result = result
    .Where(x =>
        x.FirstName.Contains(model.searchPhrase) ||
        x.LastName.Contains(model.searchPhrase)); //? Search Filter

The Gets all rows code works perfectly in getting all the rows from my database. However I'm having issues with the Search Filter part of the code.

model.searchPhrase is a null-able string.

Every time I run through the Search Filter nothing gets returned (doesn't matter what the input is.

After debugging I noticed that after that block of code runs I get the following Exception Error:

Static members: 'NotSupportedException' is a type, which is not valid in the given context. Non-public members: 'new System.Linq.SystemCore_EnumerableDebugView(result).items' thew an exception of type 'System.NotSupportedException'

Any idea what's wrong here?

Did you see this answer? http://stackoverflow.com/a/19791382/728795 — Andrei, Mar 30 '17 at 13:08
Your _"gets all rows"_ is just a query, you are not executing it. So of course any excpetion is raised later, f.e. in your debugger. You haven't even mentioned the linq provider, is it Linq-To-Entities? First check if `var list = result.ToList()` works. — Tim Schmelter, Mar 30 '17 at 13:09
Change `}); //? Gets all rows` to `}).ToList(); //? Gets all rows` to execute the query and see if the error actually occurs here. Your search filter looks fine to me. Why not combine the two and avoid fetching all rows in the first place? — Equalsk, Mar 30 '17 at 13:11
try changing the order of your `Contains` to `model.searchPhrase.Contains(x.FirstName)` and the same for the `LastName` — NtFreX, Apr 03 '17 at 11:54

score 0 · Answer 1 · answered Jan 10 '19 at 19:13

I've written a library to help with this problem and make it easier to build conditional queries for filtering. You can get the package from NuGet here: https://www.nuget.org/packages/LinqConditionalExtensions

Your code could be rewritten as:

var hasFilter = !string.IsNullOrWhiteSpace(model.searchPhrase);

var result = db.ContactSet
    .WhereIf(hasFilter, x => x.FirstName.Contains(model.searchPhrase) || x.LastName.Contains(model.searchPhrase))
    .Select(x => new Models.Contact
    {
        AccountId = x.AccountId,
        FirstName = x.FirstName,
        LastName = x.LastName,
        FullName = x.FullName,
        JobTitle = x.JobTitle,
        ParentCustomerId = x.ParentCustomerId,
        EMailAddress1 = x.EMailAddress1,
        Telephone1 = x.Telephone1,
        MobilePhone = x.MobilePhone,
        Fax = x.Fax,
        GenderCode = x.GenderCode,
        BirthDate = x.BirthDate
    });

The WhereIf() method will only apply the filter if the condition is true. In this case, if your model's search-phrase is not null or white-space.

You can read more about it here: https://github.com/xKloc/LinqConditionalExtensions

Ricky007 · Accepted Answer · 2017-03-30T14:59:30.893

-1

model.searchPhrase is a null-able string as he written. You cannot compare data type null-able variable with data type (string variable) in LINQ Query. So, it is throwing exception.

Use following code. It worked for me

string SearchValue="";
try
{SearchValue=model.searchPhrase.Tostring();}catch{}

result = result.Where(x => x.FirstName.Contains(SearchValue) ||
        x.LastName.Contains(SearchValue)).ToList();

edited Mar 30 '17 at 14:59

answered Mar 30 '17 at 14:25

Ricky007

127
8

1

You're just hiding exception 0.o – FCin Mar 30 '17 at 14:29
@FCin , model.searchPhrase is a null-able string as he written. You cannot compare data type (string? variable) with data type (string variable) in LINQ Query. So, it is throwing exception. – Ricky007 Mar 30 '17 at 14:46
1

But hiding exception isn't the best solution. Exceptions are expensive, and they don't look pleasing. – FCin Mar 30 '17 at 15:13
@FCin, You should give this advise to Microsoft so that they will make change in entity framework and then it will allow null-able variable to compare with string. – Ricky007 Mar 30 '17 at 15:17

Problems implementing a search filter using LINQ

2 Answers2