Use Linq to search through list

Question

Is it possible to use a Linq query to search through a List? In my web app I have to potentially process over 14k records based upon a spreadsheet uploaded by the user. With each record processed, I need to compare that record against what we currently have in our database in order to make sure we either aren't adding a duplicate or I know what record I need to be updating/editing.

Instead of hitting the database 14k times or more, I wanted to pull all the records contained on this table into a List, and then perform a search based on a set of conditions.

Here is the Linq query I currently have that hits the database. The business rules are pretty... complicated so I won't bother you with the details but these are the conditions that I need to satisfy for the search. I've tested this query and it returns the expected results.

var previousZips = (from z in db.ZipCodeTerritory
                    where (item.ZipCode.Equals(null) ?
                            z.StateCode.Equals(item.StateCode) &&
                            z.ChannelCode.Equals(item.ChannelCode) &&
                            SqlFunctions.DateDiff("DAY", z.EndDate, item.EndDate) == 0 :
                            z.StateCode.Equals(item.StateCode) &&
                            z.ChannelCode.Equals(item.ChannelCode) &&
                            SqlFunctions.DateDiff("DAY", z.EndDate, item.EndDate) == 0 &&
                            (z.ZipCode.Equals(null) || z.ZipCode.Equals(item.ZipCode)))
                    select z).ToList();

What I would like to do, however, is create a List of all the records on the table like this:

List<ZipCodeTerritory> allRecords = (from z in db.ZipCodeTerritory
                                        select z).ToList()

and then use a query similar to this to pull the record I'm looking for from the list:

List<ZipCodeTerritory> previousZips = allRecords.Where(
                                        z => (item.ZipCode.Equals(null)
                                        ? z.StateCode.Equals(item.StateCode) &&
                                        z.ChannelCode.Equals(item.ChannelCode) &&
                                        SqlFunctions.DateDiff("DAY", z.EndDate,
                                            item.EndDate) == 0
                                        : z.StateCode.Equals(item.StateCode) &&
                                        z.ChannelCode.Equals(item.ChannelCode) &&
                                        SqlFunctions.DateDiff("DAY", z.EndDate,item.EndDate) == 0 &&
                                        (z.ZipCode.Equals(null) || z.ZipCode.Equals(item.ZipCode))
                                    )
                            ).ToList();

The query above (from the List), however, throws the following error:

This function can only be invoked from LINQ to Entities.

Answer 1

Instead of

SqlFunctions.DateDiff("DAY", z.EndDate,item.EndDate) == 0

use

z.EndDate.Subtract(item.EndDate).TotalDays == 0

Answer 2

Answered my own question. The problem here was the SqlFunction. By removing that from the query and re-writing it like this it works

List<ZipCodeTerritory> previousZips = allRecords.Where(
                                        z => (item.ZipCode.Equals(null)
                                        ? z.StateCode.Equals(item.StateCode) &&
                                        z.ChannelCode.Equals(item.ChannelCode) &&
                                        z.EndDate.Date == item.EndDate.Date
                                        : z.StateCode.Equals(item.StateCode) &&
                                        z.ChannelCode.Equals(item.ChannelCode) &&
                                        z.EndDate.Date == item.EndDate.Date &&
                                        (z.ZipCode.Equals(null) || z.ZipCode.Equals(item.ZipCode))
                                    )
                            ).ToList();