CODEWITHSUNDEEP
sql-serverlinq-to-entities
BattlFrog
BattlFrog

Reputation: 3397

Creating a LINQ query for a search feature

I have a search feature in my app. The way it works is, the user pulls up a record, the kits the search button which passes the first name, last name and date of birth from the record, to a LINQ query. The LINQ query is then supposed to serve up possible matches. Right now it serves up too many results. I want the date of birth match to be exact, then the first and last name matches can be somewhat loose. In a perfect world, the results would have:

  1. Exact match to the DOB
  2. Fuzzy matches to the first name
  3. Close, but not strict, matches to the last name.

Example, If I am searching for James May, born 11/27/1974, I would like results such as:

Jim May 11/27/1974
Jack May 11/27/1974 James Mayford 11/27/1974

I know the best thing would be a stored procedure, but I tried that and everytime I added the stored Proc to my project, it corrupted the EDMX file, so I am stuck doing it with LINQ.

Here is what I have so far:

        var query = from p in _Elig_DB.PersonAttributes.ToList()
                    where   p.getDateOfBirth == DOB &&
                                    p.FirstName.Contains(FName) || p.FirstName.StartsWith(FName) &&
                                    p.LastName.Contains(LName) || p.LastName.StartsWith(LName)

                    select p;

This gives results like this:

Ileana   May         10/30/1967
Kristina May         4/23/1971
margaret Mayberry    7/26/1942
Karla    Mayorga     5/14/1986
Stan     Mayer       3/8/1952
Lori     Maynard     7/22/1965

So the last name is good, but the date of birth should not vary like that. Thank you for reading.

Upvotes: 1

Views: 172

Answers (2)

hotpie
hotpie

Reputation: 118

sorry I cannot add comments yet but would like to add that your query is not using the where filter when getting the data from the database.

At the moment the following sql or equivalent will execute on your database.

select * _Elig_DB.PersonAttributes

All rows from the table will be newed up into entity objects. Only then the where part of you statement will be executed against the entities and disregard unwanted results.

I'm assuming this is unintentional and that you would rather want to optimise the initial query.

The reason why this is happening is because you have a .ToList() statement on the from part of your linq statement. The tolist statement forces the query to execute immediately and build a list of objects.

If you change your statement to(without tolist):

var query = from p in _Elig_DB.PersonAttributes
            where   p.getDateOfBirth == DOB &&
                            (p.FirstName.Contains(FName) || p.FirstName.StartsWith(FName) &&
                            p.LastName.Contains(LName) || p.LastName.StartsWith(LName))

            select p;

At this point your query has not fetched any data. (Notice explicit var query type is Queryable) Now you can execute your complete query only fetching the database rows you need.

var results = query.ToList();

Upvotes: 0

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Just fix your parentheses.

    var query = from p in _Elig_DB.PersonAttributes.ToList()
                where   p.getDateOfBirth == DOB &&
                                (p.FirstName.Contains(FName) || p.FirstName.StartsWith(FName) &&
                                p.LastName.Contains(LName) || p.LastName.StartsWith(LName))

                select p;

Upvotes: 1

Related Questions