CODEWITHSUNDEEP
c#sql.netlinq
NealR
NealR

Reputation: 10669

Linq query from list containing check for null condition

I am trying to use a Linq query to search for a match in a giant list (around 100k items). The search criteria needs to be the fields StateCode, ChannelCode, EndDate, and ZipCode.

However, the check for ZipCode is a little tricky. I need to pull back results given either of these two conditions:

This is the query as I have it right now... which doesn't work. It constantly pulls back a list with a count of 0. However, if I remove the section that searches for the ZipCode match, then I will get results. 

List<ZipCodeTerritory> previousZips = allRecords.Where(
        z => (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();

I have also tried replacing z.ZipCode.Equals(null) with string.IsNullOrWhiteSpace(z.ZipCode) and this still didn't work.

EDIT: I have also attempted the condition with | and || as the or operator. Neither work...

Just in case, here is the query written out into a SQL statement. This works as expected if I run it in SQL Server Management Studio. 

SELECT * FROM ZipCodeTerritory 
WHERE StateCode = 'OR ' and ChannelCode = 'G' and EndDate = '12/31/9999' and (ZipCode IS NULL OR ZipCode = '00001')

SECOND EDIT

This is how I'm grabbing the record from the database. Again, since I didn't want to have to make multiple calls I wanted to grab all the record from the ZipCodeTerritory table and then search through that list. 

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

Upvotes: 0

Views: 1802

Answers (3)

NealR
NealR

Reputation: 10669

Since the ZipCode field in the database was 9 digits long, Linq placed four spaces at the end of each 5 digit zip code. So a zip code of '00001' would be a value of '00001 '. By placing a .Trim() like so I'm now able to get the expected result. 

(string.IsNullOrWhiteSpace(z.ZipCode) || z.ZipCode.Trim().Equals(item.ZipCode)

Upvotes: 0

Magnus
Magnus

Reputation: 46909

It should be;

(z.ZipCode == null || z.ZipCode.Equals(item.ZipCode))

Upvotes: 1

DLeh
DLeh

Reputation: 24385

Did you mean to have only one pipe symbol here? Two pipes ( || ) is logical or, single is bitwise or

    (z.ZipCode.Equals(null) | z.ZipCode.Equals(item.ZipCode))

Upvotes: 0

Related Questions