LINQ query with multiple conditions in WHERE clause

Question

I am 6 months into my first development role and have started to use more LINQ in our repository layer to query our DB. I could really use some help with two queries I created.

IQueryable<long> clientsWithoutFeature = from cf in db.Features
                where cf.Feature != 9 && cf.Feature == 8
                select cf.Client;

IQueryable<long> clientsWithFeature = from cf in db.Features
                where cf.Feature == 9 && cf.Feature == 8
                select cf.Client;

Each client can have multiple Feature's, each one being a separate record/row.

The first query is supposed to return all clients that have a Feature of 8 but not a Feature of 9. However, it is returning all clients with a Feature of 8 whether or not the client also has a Feature of 9.

The second query is supposed to return all clients that have a Feature of 8 and also have a Feature of 9. However, it is not returning any clients.

Can someone please tell me what is wrong with my queries?

Answer 1

Your sql is performing as you have written it. You need to restructure your query a little bit to express what you actually intended.

I tend to use a subquery approach like so:

IQueryable<long> clientsWithoutFeature = from cf in db.Features
                where cf.Feature == 8 && !db.Features.Any(x => x.id == cf.id && x.Feature == 9)
                select cf.Client;

IQueryable<long> clientsWithFeature = from cf in db.Features
                where cf.Feature == 8 && db.Features.Any(x => x.id == cf.id && x.Feature == 9)
                select cf.Client;

Im not really sure what your primary key column is. so i just guessed it was idY