Adding LINQ Where && condition in a foreach loop

Question

To simplify my problem let's say I have the folowing database model:

// database model, the list of not supported postcodes
var postcodeExclusionArray = new List<PostcodeExclusion>() {
    new PostcodeExclusion(){
        CustomerId = 2,
        Postocde = "A11"
    },
    new PostcodeExclusion(){
        CustomerId = 2,
        Postocde = "A12"
    },
    new PostcodeExclusion(){
        CustomerId = 2,
        Postocde = "A12 4FR"
    },
    new PostcodeExclusion(){
        CustomerId = 2,
        Postocde = "AR"
    },
    new PostcodeExclusion(){
        CustomerId = 2,
        Postocde = "B11"
    },
    new PostcodeExclusion(){
        CustomerId = 2,
        Postocde = "B12"
    },
        new PostcodeExclusion(){
        CustomerId = 2,
        Postocde = "C11"
    },
    new PostcodeExclusion(){
        CustomerId = 2,
        Postocde = "C12"
    },
    // many more records...
};

It describes unsupported UK postcodes. It's only a concept of a data source as normally we use EF Core 3.1. Please bear in mind the format of each record can vary in gathering the whole area e.g. EH or a particular property only e.g. AB25HZ.

Now I want to check if the provided UK postcode is supported by my system. My idea was to do it in the query using Where statement only but I'm not sure if it's possible at all.

The idea for the algorithm is to get the combinations of the provided postcode in a way such as

providedpostCode = "A41666";

the combinations:

A
A4
A41
A416
A4166
A41666

Then build db query such as

bool isSupported = postcodeExclusionArray.Where(x => x.CustomerId == 2)
                      .Where(x=> x.Postocde == "A" ||
                      x.Postocde == "A" ||
                      x.Postocde == "A4" ||
                      x.Postocde == "A41" ||
                      x.Postocde == "A416" ||
                      x.Postocde == "A41666").First() == null;

Is there a way to achieve this using LINQ (not raw SQL) and if not what is the most optimal solution?

I tried to play with the foreach loop but I didn't manage to find any solution. If it's not possible then probably the easiest solution would be to generate the raw SQL query, however, the minus of this approach would be that unit testing wouldn't be possible and this could only be tested by the integration test. The other alternative would be to just query for all the postcodes from the db by customerId and do a simple iteration. Any ideas? Cheers

build a `List` of all combinations in memory, then do `.Where(x => x.CustomerId == 2 && myList.Contains(x.Postocde).FirstOrDefault()` — Jonesopolis, May 24 '21 at 17:30
Does the exclusion array list strings that aren't acceptable when they start a prospective post code or when they are in a prospective post code at any position? — Loring, May 24 '21 at 17:32
@Loring the first option so: the exclusion array list strings that aren't acceptable when they start a prospective post code — GoldenAge, May 24 '21 at 17:33

Tim Schmelter · Accepted Answer · 2021-05-25T12:56:28.267

2

Enumerable.Contains is supported with collections:

string providedpostCode = "A41666";
string[] providedPostCodes = Enumerable.Range(0, providedpostCode.Length)
    .Select(ix => providedpostCode.Substring(0, ix + 1))
    .ToArray();

bool isSupported = !postcodeExclusionArray
    .Any(x => x.CustomerId == 2 && providedPostCodes.Contains(x.Postocde));

Not sure about the logic, if you need Any or !Any.

edited May 25 '21 at 12:56

answered May 24 '21 at 17:30

Tim Schmelter

450,073
74
686
939

Wouldn't it be easier to use: providedpostCode.StartsWith(x.Postocde) instead of providedPostCodes.Contains(x.Postocde)? That way providedPostCodes could be removed. Also, the logic for isSupported seems to be flipped - shouldn't the variable be notSupported or a ! be prepended to the right hand side? – Loring May 24 '21 at 17:37
@Loring: regarding your last question, i mentioned in my answer that it's not clear to me what means suppported for OP, but it's easy to add `!` if necessary. `StartsWith` seems to be also a good idea, wasnt sure that Linq-To-Entities supports it. However, note that it will be less efficient than the contains, especially with proper index on `Postocde`. – Tim Schmelter May 24 '21 at 17:47
@Tim Schmelter are you sure it will be fully translated into SQL query by EF or it will be invoked on the server-side? – GoldenAge May 24 '21 at 17:47
1

@GoldenAge: yes of course. If LINQ can't translate it to SQL you will get an exception not a query that gets executed in memory. If you add `AsEnumerable` somewhere in the query you will execute it in memory. But above query is [supported](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/supported-and-unsupported-linq-methods-linq-to-entities) and the `Contains` is translated to a [`IN(...)`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15). – Tim Schmelter May 24 '21 at 17:49
If the "PA41" is not supported postcode(it describes the whole unsuported area of postcodes) and you want to check "A41OP5" then your code( adding the `!` to the right side of ur code that @Loring suggested) will return `true` which is not correct. – GoldenAge May 25 '21 at 12:52
@GoldenAge: sorry but i don't understand _"adding the to do with ! to the right side of ur code"_. Just put the `!` in front of the `postcodeExclusionArray` in my code to reverse the logic. I have edited my answer now to show it. – Tim Schmelter May 25 '21 at 12:55
Ah sry m8, it was me; I made a silly typo in my code. Your code works just fine. Thanks – GoldenAge May 25 '21 at 13:12

score 0 · Answer 2 · answered May 24 '21 at 18:06

Looks like you have to just to do StartsWith with first letter:

var postCode = providedpostCode[0];
var isSupported = postcodeExclusionArray
    .Where(x => x.CustomerId == 2)
    .Where(x => x.Postocde.StartsWith(postCode));

Anyway, i think it is just bad explanation of the problem.

Don't want to repeat myself a lot, just reuse extensions from this may answer: https://stackoverflow.com/a/67666993/10646316

And write the following code (reused previous answer code):

var providedpostCode = "A41666";
var providedPostCodes = Enumerable.Range(0, providedpostCode.Length - 1)
    .Select(ix => providedpostCode.Substring(0, ix + 1))
    .ToArray();

var isSupported = postcodeExclusionArray
    .Where(x => x.CustomerId == 2)
    .FilterByItems(providedPostCodes, (x, c) => x.Postocde == c, true);

Adding LINQ Where && condition in a foreach loop

2 Answers2